Which table do I join first?

From time to time, I get confused about the order and the outcome of joins when presented with multiple options. I have decided to make a cheat sheet for future reference.

You can either follow the examples step by step below or jump straight to the summary at the end.

Let’s prepare the sample tables and data:

use tempdb

create table Security
(
    SecurityID int
    , SecurityName varchar(100)
)

create table SecurityCodeType
(
    SecurityCodeTypeID int
    , SecurityCodeType varchar(100)
)

create table SecurityCode
(
    SecurityID int
    , SecurityCodeTypeID int
    , SecurityCode varchar(100)
)
    
insert Security values (123, 'BHP BILLITON LIMITED')
insert SecurityCodeType values (1, 'ASX')
insert SecurityCode values (123, 1, 'BHP')

Now, looking at the following three queries, they all return the same result.

select a.SecurityName, b.SecurityCode, c.SecurityCodeType
from Security a
left join SecurityCode b on a.SecurityID = b.SecurityID
join SecurityCodeType c  on b.SecurityCodeTypeID = c.SecurityCodeTypeID
                            and c.SecurityCodeType = 'ASX'

image

select a.SecurityName, b.SecurityCode, c.SecurityCodeType
from Security a
left join SecurityCode b        on a.SecurityID = b.SecurityID
left join SecurityCodeType c    on b.SecurityCodeTypeID = c.SecurityCodeTypeID
                                    and c.SecurityCodeType = 'ASX'

image

select a.SecurityName, b.SecurityCode, b.SecurityCodeType
from Security a
left join 
(
    select b.SecurityID, b.SecurityCode, c.SecurityCodeType
    from SecurityCode b    
    join SecurityCodeType c    on b.SecurityCodeTypeID = c.SecurityCodeTypeID
                                and c.SecurityCodeType = 'ASX'                                    
) b on a.SecurityID = b.SecurityID

image

This is only true if the matching data is found in all three tables. In other words, the LEFT OUTER JOINs don’t serve much purpose here.

Now if we manipulate the queries so the LEFT OUTER JOINs start to have effects, the results will become:

select a.SecurityName, b.SecurityCode, c.SecurityCodeType
from Security a
left join SecurityCode b    on a.SecurityID = b.SecurityID
join SecurityCodeType c        on b.SecurityCodeTypeID = c.SecurityCodeTypeID
                                and c.SecurityCodeType = 'SEDOL'

image

select a.SecurityName, b.SecurityCode, c.SecurityCodeType
from Security a
left join SecurityCode b        on a.SecurityID = b.SecurityID
left join SecurityCodeType c    on b.SecurityCodeTypeID = c.SecurityCodeTypeID
                                    and c.SecurityCodeType = 'SEDOL'

image

select a.SecurityName, b.SecurityCode, b.SecurityCodeType
from Security a
left join 
(
    select b.SecurityID, b.SecurityCode, c.SecurityCodeType
    from SecurityCode b    
    join SecurityCodeType c    on b.SecurityCodeTypeID = c.SecurityCodeTypeID
                                and c.SecurityCodeType = 'SEDOL'                                    
) b on a.SecurityID = b.SecurityID

image

If the differences in the above queries are not obvious to you, see if it is any better if we read them in English:

1. Return all securities which has a SEDOL code.

2. Return all securities AND their codes if available AND indicate the SEDOL codes.

3. Return all securities AND the codes only if they are SEDOL codes.

Now I hope you understand the strategic placement of the LEFT OUTER JOIN is a critical decision to make that it may totally change the information you intended to extract from the database.

I have summarised the above observations in a table below for quick future reference.

Pattern Mandatory Optional
from Table1
left join Table2
join Table3
Table1, Table3. Table2.
from Table1
left join Table2
left join Table3
Table1. Table2, Table3.
from Table1
left join
(
    from Table2
    join Table3
)
Table1. Table2 + Table3.

It took forever to delete one record

Have you ever tried to delete a record from a table but it seemed to have taken forever? Furthermore, you checked everything and just couldn’t find any performance problems with the server.

Well, check again. Check the foreign key references on the primary key of the table. Now you see it? Yes, SQL Server was checking the foreign key constraints on every table big or small that references your primary key table. It would only delete the record(s) after it made sure that your statement would not introduce data integrity issues.

I am now going to use the AdventureWorks.HumanResources.Employee table to demonstrate how you can work around the delete performance issue.

Check table

Assuming you already have the table name in a query window, now highlight the table and press Alt+F1. It is the same as running the “sp_help” system proc on the table.

If you got the following error instead of proper results, make sure you surround the schema and table names using quotes or square brackets.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

Like this:

exec sp_help 'HumanResources.Employee'
exec sp_help "HumanResources.Employee"

exec sp_help [HumanResources.Employee]

You will get several result sets and pay attention to the one titled “Table is referenced by foreign key”.

image

Disable foreign key constraints

Copy the list of constraints to a new query window.

image

Replace the beginning of the lines with “alter table “.

image

Replace semi colons with ” nocheck constraint “.

image

Your script should look like the one below

image

By executing the script, you are effectively disabling the foreign key constraints on these table.

Delete the record(s)

Now you should be able to delete the desired records with a much faster response time(assuming you’ve got appropriate indexes and everything).

delete AdventureWorks.Sales.SalesPerson
where EmployeeID =  280

Re-Enable the foreign key constraints

We can re-enable the constraints by using the same script we’ve prepared earlier for disabling the foreign key constraints. We just need to replace “nocheck” with “with check check”.

image

In case if you are wondering why CHECK CHECK, here is the official explanation from Books Online:

The WITH CHECK CHECK CONSTRAINT re-enables the constraint, and also validates the existing data against the re-enabled constraint.

So it should be read like WITH CHECK (pause) CHECK CONSTRAINT.

Finally

I hope you find the above tip helpful. However, a word of warning though, this tactical operation is better reserved for the non production environments.

There is no SUBSTRING function in my code!

Today I have encountered something really annoying, it took me at least an hour to work out what went wrong.

Let’s say I had the following query in a stored procedure:

select left(name, charindex(' ', name) - 1) as 'first_name'
from (select 'louie bao' as name) a

which returned:

image

Now, let’s pretend that I had decided to change the name delimiter from a space to a semicolon and I had forgotten to update the CHARINDEX function:

select left(name, charindex(' ', name) - 1) as 'first_name'
from (select 'louie;bao' as name) a

which returned an error:

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

The error message looked pretty straight forward. Look again! That’s right, there was no SUBSTRING function in my code!

When I saw that error message, I searched through my stored procedure for the word “substring” over and over again but to no avail. Just when I was about to give up, I noticed the line number. Why don’t I go to that line and see what was actually causing the error? How did I do that? Easy, I will demonstrate with an example:

use tempdb
go
create proc no_substring
as
 
print 'Blah'
 
select left(name, charindex(' ', name) - 1) as 'first_name'
from (select 'louie;bao' as name) a
 
print 'Blah'

return 0
go

Now run the stored proc:

exec no_substring

and you would expect to get:

Blah
Msg 536, Level 16, State 5, Procedure no_substring, Line 6
Invalid length parameter passed to the SUBSTRING function.

Blah

Note that the code starting at Line 6 generated the error. Let’s go there and find the code:

exec sp_helptext 'no_substring'

image

Now back to the problem at hand, I managed to reduce the code to the query below and was still able to produce the error message:

select left('', (select -1))

and got:

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.

I need to mention that I was running in SQL Server 2005. Just before writing this post, I tried the same code in SQL Server 2008 and got the following error message instead:

Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

A new error message was created in SQL Server 2008 which is much more helpful at identifying the cause of the error.

DBCC DROPCLEANBUFFERS

I have used “dbcc dropcleanbuffers” numerous times before but never really understood what was happening. I used to assume that by running this command, all the reads would then be done from the physical disk.

The first time I saw DROPCLEANBUFFERS, I read it like DROP’n’CLEAN BUFFERS. According to Books Online, it should be DROP “CLEAN BUFFERS”:

Removes all clean buffers from the buffer pool.

So what is a clean buffer? A clean buffer is a data page in memory that is NOT MODIFIED.

Then what is a dirty buffer? According to Books Online:

Dirty pages are data pages that have been entered into the buffer cache and modified, but not yet written to disk.

Now we know DBCC DROPCLEANBUFFERS gets rid of the clean buffers, but which command gets rid of the dirty buffers? The answer is CHECKPOINT. According to Books Online:

To drop clean buffers from the buffer pool, first use CHECKPOINT to produce a cold buffer cache. This forces all dirty pages for the current database to be written to disk and cleans the buffers. After you do this, you can issue DBCC DROPCLEANBUFFERS command to remove all buffers from the buffer pool.

Alright, that’s all the boring stuff out of the way, now let’s get on with the fun stuff. Let me demonstrate with a simple example.

Let’s create a test database and a test table:

create database test
go
use test
go
create table test (data int)
go

We also need to turn on the stats:

set statistics io on

We are almost ready, press Ctrl + T to output results to text, now we are ready to play:

select * from test

Nothing exists yet hence no reads.

image

Execute the script below to see some reads:

insert test values (1)
select * from test

A row is now in memory hence the logical read.

image

Let’s clear the buffers:

dbcc dropcleanbuffers
select * from test

As expected, the page in memory is dirty, dbcc had no effect, hence no physical reads.

image

Let’s flush the dirty page to disk:

checkpoint
select * from test

The page is now clean but still in memory, hence still no physical reads.

image

Try to clear the buffers again:

dbcc dropcleanbuffers
select * from test

This time the buffers are cleared for real hence the physical read.

image

–oo00oo–

For more info, please refer to the following blog post:
SQL Server: What is a COLD, DIRTY or CLEAN Buffer?

How to generate a HTML table using FOR XML PATH

I have got the idea from a blog post I read ages ago and I vaguely remember the author had used raw T-SQL with lots of string manipulation. Just recently, I had a need to return result sets as HTML tables, and due to the similarity between HTML and XML, I thought I would try the FOR XML syntax.

Let’s use “msdb” for example and return a HTML table showing the following properties for the database from the sys.databases view:

  • name (sysname)
  • database_id (int)
  • source_database_id (int)
  • owner_sid (varbinary)
  • service_broker_id (uniqueidentifier)

Here we go, I executed the following query to get an idea of what the data looked like:

select 
    name
    , database_id
    , source_database_id
    , owner_sid
    , service_broker_guid 
from sys.databases
where name = 'msdb'

Then I added the FOR XML clause:

select 
    name
    , database_id
    , source_database_id
    , owner_sid
    , service_broker_guid 
from sys.databases
where name = 'msdb'
for xml path

Opened the result set in XML Editor:

<row>
  <name>msdb</name>
  <database_id>4</database_id>
  <owner_sid>AQ==</owner_sid>
  <service_broker_guid>E3391D0A-74CD-4DBA-9603-48A5C9E00CED</service_broker_guid>
</row>

The script looked like HTML but far from a HTML table and where did source_database_id go? According to Books Online:

Columns that Contain a Null Value By Default
By default, a null value in a column maps to the absence of the attribute, node, or element. …

No worries, I can easily handle that:

select 
    name                    as 'td'
    , database_id           as 'td'
    , isnull(cast(source_database_id as varchar(max)), 'NULL') as 'td'
    , owner_sid             as 'td'
    , service_broker_guid   as 'td'
from sys.databases
where name = 'msdb'
for xml path('tr'), root('table')

Opened the result set in XML Editor:

<table>
  <tr>
    <td>msdb4NULLAQ==E3391D0A-74CD-4DBA-9603-48A5C9E00CED</td>
  </tr>
</table>

Some things worked, but some didn’t. You didn’t think it was that easy right? The <table> and <tr> tags are pretty self explanatory, so I won’t spend too much time on them. More work was obviously required for the <td> tags.

According to Books Online:

Several Columns Share the Same Path Prefix
If several subsequent columns share the same path prefix, they are grouped together under the same name. …

One Column Has a Different Name
If a column with a different name appears in between, it will break the grouping, …

In light of these mapping conditions, I could see a way around the issue I was having with the <td> tags:

  1. Insert a column with a different name in between <td> columns.
  2. Return NULL for the extra columns.

I changed the code slightly:

select 
    name                       as 'td', null
    , database_id              as 'td', null
    , isnull(cast(source_database_id as varchar(max)), 'NULL') as 'td', null
    , owner_sid                as 'td', null
    , service_broker_guid      as 'td', null
from sys.databases
where name = 'msdb'
for xml path('tr'), root('table')

Opened the result set in XML Editor:

<table>
  <tr>
    <td>msdb</td>
    <td>4</td>
    <td>NULL</td>
    <td>AQ==</td>
    <td>E3391D0A-74CD-4DBA-9603-48A5C9E00CED</td>
  </tr>
</table>

Alright! The data was returned in the HTML table format. I saved the script as table.htm and opened it in Internet Explorer:

image

Adding a border and a header row would make the table more user friendly, hence I rolled up my sleeves and played around with the query a bit:

select
    1 as [email protected]'
    ,(
        select
            name                     as 'td', null
            , database_id            as 'td', null
            , isnull(cast(source_database_id as varchar(max)), 'NULL') as 'td', null
            , owner_sid              as 'td', null
            , service_broker_guid    as 'td', null
        from sys.databases
        where name = 'msdb'
        for xml path('tr')
    )
for xml path('table')

Opened the result set in XML Editor:

<table border="1">&lt;tr&gt;&lt;td&gt;msdb&lt;/td&gt;&lt;td&gt;4&lt;/td&gt;&lt;td&gt;NULL&lt;/td&gt;&lt;td&gt;AQ==&lt;/td&gt;&lt;td&gt;E3391D0A-74CD-4DBA-9603-48A5C9E00CED&lt;/td&gt;&lt;/tr&gt;</table>

The border attribute was added successfully but the angle brackets were gone.

According to Books Online:

TYPE Directive in FOR XML Queries

In SQL Server 2000, the result of a FOR XML query is always directly returned to the client in textual form. With support for the xml data type in SQL Server 2005, you can optionally request that the result of a FOR XML query be returned as xml data type by specifying the TYPE directive. This allows you to process the result of a FOR XML query on the server. For example, you can specify an XQuery against it, assign the result to an xml type variable, or write Nested FOR XML queries.

The very last sentence appealed to me since I had used a nested FOR XML query. I added the TYPE directive without hesitation:

select
    1 as [email protected]'
    ,(
        select
            name                     as 'td', null
            , database_id            as 'td', null
            , isnull(cast(source_database_id as varchar(max)), 'NULL') as 'td', null
            , owner_sid              as 'td', null
            , service_broker_guid    as 'td', null
        from sys.databases
        where name = 'msdb'
        for xml path('tr'), type
    )
for xml path('table')

Opened the result set in XML Editor:

<table border="1">
  <tr>
    <td>msdb</td>
    <td>4</td>
    <td>NULL</td>
    <td>AQ==</td>
    <td>E3391D0A-74CD-4DBA-9603-48A5C9E00CED</td>
  </tr>
</table>

Nice! Now the border issue was resolved, it’s time to add the header row:

select
    1 as [email protected]'
    ,(
        select
            'name'                  as 'th', null
            , 'database_id'         as 'th', null
            , 'source_database_id'  as 'th', null
            , 'owner_sid'           as 'th', null
            , 'service_broker_guid' as 'th', null
        for xml path('tr'), type
    )        
    ,(
        select
            name   as 'td', null
            , database_id            as 'td', null
            , isnull(cast(source_database_id as varchar(max)), 'NULL') as 'td', null
            , owner_sid              as 'td', null
            , service_broker_guid    as 'td', null
        from sys.databases
        where name = 'msdb'
        for xml path('tr'), type
    )
for xml path('table')

Opened the result set in XML Editor:

<table border="1">
  <tr>
    <th>name</th>
    <th>database_id</th>
    <th>source_database_id</th>
    <th>owner_sid</th>
    <th>service_broker_guid</th>
  </tr>
  <tr>
    <td>msdb</td>
    <td>4</td>
    <td>NULL</td>
    <td>AQ==</td>
    <td>E3391D0A-74CD-4DBA-9603-48A5C9E00CED</td>
  </tr>
</table>

I tried the HTML in Internet Explorer again:

image

Mission accomplished? According to Bush it was, but not for me, I had one more battle to fight. Notice that owner_sid (varbinary) is represented in base64-encoded format? Well, can we display it in a text format? Yes, we can!

After rummaging through google search results, I found two undocumented system functions on the net:

  • sys.fn_sqlvarbasetostr
  • sys.fn_varbintohexsubstring

The first one accepts a sql_variant and returns a nvarchar(max), in other words, it converts “any” data type to string. It calls the second function if the parameter is of binary/varbinary type.

Run sp_helptext ‘sys.fn_sqlvarbasetostr’ and see for yourself:

image

I couldn’t wait to try the new toy:

select
    1 as [email protected]'
    ,(
        select
            'name'                  as 'th', null
            , 'database_id'         as 'th', null
            , 'source_database_id'  as 'th', null
            , 'owner_sid'           as 'th', null
            , 'service_broker_guid' as 'th', null
        for xml path('tr'), type
    )        
    ,(
        select
            name                                as 'td', null
            , database_id                       as 'td', null
            , isnull(cast(source_database_id as varchar(max)), 'NULL') as 'td', null
            , sys.fn_sqlvarbasetostr(owner_sid) as 'td', null
            , service_broker_guid               as 'td', null
        from sys.databases
        where name = 'msdb'
        for xml path('tr'), type
    )
for xml path('table')

Opened the result set in XML Editor:

<table border="1">
  <tr>
    <th>name</th>
    <th>database_id</th>
    <th>source_database_id</th>
    <th>owner_sid</th>
    <th>service_broker_guid</th>
  </tr>
  <tr>
    <td>msdb</td>
    <td>4</td>
    <td>NULL</td>
    <td>0x01</td>
    <td>E3391D0A-74CD-4DBA-9603-48A5C9E00CED</td>
  </tr>
</table>

Saved the script and viewed in Internet Explorer:

image

Mission accomplished.

–oo00oo–

For more info, please refer to the following blog posts:
Format query output into an HTML table – the easy way
Creating an HTML table from SQL
How to make an HTML table version 2
T-SQL Convert Hex String to Varbinary

How to pad numbers with leading zeros

I often come across a date manipulation requirement to have a leading zero for the single digit day/month number. Well, it is pretty easy to come up with a solution, but the other day I stumbled across a little gem which I would like to call the “think outside the square” approach.

Previously I would write something like:

select 
    case 
        when day('2009-07-01') < 10 then '0' 
        else '' 
    end 
    + cast(day('2009-07-01') as varchar)

Nowadays, I simply apply the “think outside the square” approach to this problem:

select right('0' + cast(day('2009-09-01') as varchar), 2)
select right('0' + cast(day('2009-10-19') as varchar), 2)

IsNumeric != IsNumber

According to Books Online:
ISNUMERIC determines whether an expression is a valid numeric type. However, ISNUMERIC also returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($).
 
So what other characters/symbols are also treated as the numeric type? I’ve built two tables to help answer the question.
 
ASCII Symbol Description IsNumeric
9   tab 1
10   newline 1
13   carriage return 1
32   space 0
33 ! exclamation mark 0
34 double quote 0
35 # hash 0
36 $ dollar 1
37 % percent 0
38 & ampersand 0
39 single quote 0
40 ( open bracket 0
41 ) close bracket 0
42 * multiply/star 0
43 + plus 1
44 , comma 1
45 minus/dash 1
46 . full stop 1
47 / divide/forward slash 0
48 0 0 1
49 1 1 1
50 2 2 1
51 3 3 1
52 4 4 1
53 5 5 1
54 6 6 1
55 7 7 1
56 8 8 1
57 9 9 1
58 : colon 0
59 ; semicolon 0
60 < less than 0
61 = equal 0
62 > greater than 0
63 ? question mark 0
64 @ at 0
65 A A 0
66 B B 0
67 C C 0
68 D D 0
69 E E 0
70 F F 0
71 G G 0
72 H H 0
73 I I 0
74 J J 0
75 K K 0
76 L L 0
77 M M 0
78 N N 0
79 O O 0
80 P P 0
81 Q Q 0
82 R R 0
83 S S 0
84 T T 0
85 U U 0
86 V V 0
87 W W 0
88 X X 0
89 Y Y 0
90 Z Z 0
91 [ open square bracket 0
92 \ backslash 1
93 ] close square bracket 0
94 ^ power 0
95 _ underscore 0
96 ` apostrophe 0
97 a a 0
98 b b 0
99 c c 0
100 d d 0
101 e e 0
102 f f 0
103 g g 0
104 h h 0
105 i i 0
106 j j 0
107 k k 0
108 l l 0
109 m m 0
110 n n 0
111 o o 0
112 p p 0
113 q q 0
114 r r 0
115 s s 0
116 t t 0
117 u u 0
118 v v 0
119 w w 0
120 x x 0
121 y y 0
122 z z 0
123 { open brace 0
124 | vertical bar/pipe 0
125 } close brace 0
126 ~ tilde 0
ASCII Symbol Expression1 IsNumeric Expression2 IsNumeric Expression3 IsNumeric
9 (tab) 1(tab) 0 (tab)2 1 1(tab)2 0
10 (newline) 1(newline) 0 (newline)2 1 1(newline)2 0
13 (carriage return) 1(carriage return) 0 (carriage return)2 1 1(carriage return)2 0
32 (space) 1(space) 1 (space)2 1 1(space)2 0
33 ! 1! 0 !2 0 1!2 0
34 1″ 0 “2 0 1″2 0
35 # 1# 0 #2 0 1#2 0
36 $ 1$ 0 $2 1 1$2 0
37 % 1% 0 %2 0 1%2 0
38 & 1& 0 &2 0 1&2 0
39 1′ 0 ‘2 0 1’2 0
40 ( 1( 0 (2 0 1(2 0
41 ) 1) 0 )2 0 1)2 0
42 * 1* 0 *2 0 1*2 0
43 + 1+ 0 +2 1 1+2 0
44 , 1, 1 ,2 1 1,2 1
45 1- 0 -2 1 1-2 0
46 . 1. 1 .2 1 1.2 1
47 / 1/ 0 /2 0 1/2 0
48 0 10 1 02 1 102 1
49 1 11 1 12 1 112 1
50 2 12 1 22 1 122 1
51 3 13 1 32 1 132 1
52 4 14 1 42 1 142 1
53 5 15 1 52 1 152 1
54 6 16 1 62 1 162 1
55 7 17 1 72 1 172 1
56 8 18 1 82 1 182 1
57 9 19 1 92 1 192 1
58 : 1: 0 :2 0 1:2 0
59 ; 1; 0 ;2 0 1;2 0
60 < 1< 0 <2 0 1<2 0
61 = 1= 0 =2 0 1=2 0
62 > 1> 0 >2 0 1>2 0
63 ? 1? 0 ?2 0 1?2 0
64 @ 1@ 0 @2 0 [email protected] 0
65 A 1A 0 A2 0 1A2 0
66 B 1B 0 B2 0 1B2 0
67 C 1C 0 C2 0 1C2 0
68 D 1D 0 D2 0 1D2 1
69 E 1E 0 E2 0 1E2 1
70 F 1F 0 F2 0 1F2 0
71 G 1G 0 G2 0 1G2 0
72 H 1H 0 H2 0 1H2 0
73 I 1I 0 I2 0 1I2 0
74 J 1J 0 J2 0 1J2 0
75 K 1K 0 K2 0 1K2 0
76 L 1L 0 L2 0 1L2 0
77 M 1M 0 M2 0 1M2 0
78 N 1N 0 N2 0 1N2 0
79 O 1O 0 O2 0 1O2 0
80 P 1P 0 P2 0 1P2 0
81 Q 1Q 0 Q2 0 1Q2 0
82 R 1R 0 R2 0 1R2 0
83 S 1S 0 S2 0 1S2 0
84 T 1T 0 T2 0 1T2 0
85 U 1U 0 U2 0 1U2 0
86 V 1V 0 V2 0 1V2 0
87 W 1W 0 W2 0 1W2 0
88 X 1X 0 X2 0 1X2 0
89 Y 1Y 0 Y2 0 1Y2 0
90 Z 1Z 0 Z2 0 1Z2 0
91 [ 1[ 0 [2 0 1[2 0
92 \ 1\ 0 \2 1 1\2 0
93 ] 1] 0 ]2 0 1]2 0
94 ^ 1^ 0 ^2 0 1^2 0
95 _ 1_ 0 _2 0 1_2 0
96 ` 1` 0 `2 0 1`2 0
97 a 1a 0 a2 0 1a2 0
98 b 1b 0 b2 0 1b2 0
99 c 1c 0 c2 0 1c2 0
100 d 1d 0 d2 0 1d2 1
101 e 1e 0 e2 0 1e2 1
102 f 1f 0 f2 0 1f2 0
103 g 1g 0 g2 0 1g2 0
104 h 1h 0 h2 0 1h2 0
105 i 1i 0 i2 0 1i2 0
106 j 1j 0 j2 0 1j2 0
107 k 1k 0 k2 0 1k2 0
108 l 1l 0 l2 0 1l2 0
109 m 1m 0 m2 0 1m2 0
110 n 1n 0 n2 0 1n2 0
111 o 1o 0 o2 0 1o2 0
112 p 1p 0 p2 0 1p2 0
113 q 1q 0 q2 0 1q2 0
114 r 1r 0 r2 0 1r2 0
115 s 1s 0 s2 0 1s2 0
116 t 1t 0 t2 0 1t2 0
117 u 1u 0 u2 0 1u2 0
118 v 1v 0 v2 0 1v2 0
119 w 1w 0 w2 0 1w2 0
120 x 1x 0 x2 0 1×2 0
121 y 1y 0 y2 0 1y2 0
122 z 1z 0 z2 0 1z2 0
123 { 1{ 0 {2 0 1{2 0
124 | 1| 0 |2 0 1|2 0
125 } 1} 0 }2 0 1}2 0
126 ~ 1~ 0 ~2 0 1~2 0
 
If an expression is numeric, then it can be converted to either int, float or money. Looking at the tables above, most of the numeric expressions are pretty straight forward. There is an odd one out that troubled me for a while.
 
What is “\2”? What sort of numeric representation is this? To answer that, I tried various conversions:

select cast(‘\2’ as int)
go
select cast(‘\2’ as float)
go
select cast(‘\2’ as money)
go

and got:


Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '\2' to data type int.
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

(1 row(s) affected)

So it represents a currency. I understand that $ is the symbol for dollar, but what is \? It turned out to be either the Japanese Yen or Korean Won.
 
–oo00oo–
 
For more info, please refer to the following blog post:

dbcc checkident

One day I wanted to set the identity column of a newly created table to a higher number, so I issued the following SQL command:

dbcc checkident('table_name', reseed, 3000)

Then after inserting a few rows, I noticed the numbers in the identity column was still incrementing from 1 (original seed value).

I realised what’s wrong after looking up the function in Books Online:

You cannot use DBCC CHECKIDENT to perform the following tasks:

  • Change the original seed value that was specified for an identity column when the table or view was created.