Data Type and Operator Precedence

In SQL Server you will encounter a lot of cases, in which an operator combines two expressions of different data types. The rules that specify which value is converted to another data type, can be found on MSDN. But the precedence of data types are different for the different versions of SQL Server. Therefore I created the schema below, so you can compare the different versions:

  SQL Server 2005 SQL Server 2008 / 2008 R2 SQL Server 2012
1 user-defined data types (highest) user-defined data types (highest) user-defined data types (highest)
2 sql_variant sql_variant sql_variant
3 xml xml xml
4 datetime datetimeoffset datetimeoffset
5 smalldatetime datetime2 datetime2
6 float datetime datetime
7 real smalldatetime smalldatetime
8 decimal date date
9 money time time
10 smallmoney float float
11 bigint real real
12 int decimal decimal
13 smallint money money
14 tinyint smallmoney smallmoney
15 bit bigint bigint
16 ntext int int
17 text smallint smallint
18 image tinyint tinyint
19 timestamp bit bit
20 uniqueidentifier ntext ntext
21 nvarchar
(including nvarchar(max))
text text
22 nchar image image
23 varchar
(including varchar(max))
timestamp timestamp
24 char uniqueidentifier uniqueidentifier
25 varbinary
(including varbinary(max))
nvarchar
(including nvarchar(max))
nvarchar
(including nvarchar(max))
26 binary
(lowest)
nchar nchar
27 varchar
(including varchar(max))
varchar
(including varchar(max))
28 char char
29 varbinary
(including varbinary(max))
varbinary
(including varbinary(max))
30 binary
(lowest)
binary
(lowest)

The same counts for Operators. There are differences in the precedence between SQL Server versions. I took the data from different versions of SQL Server, and created the schema below:

  SQL Server 2005 SQL Server 2008 / 2008 R2 SQL Server 2012
1 ~ (Bitwise NOT) ~ (Bitwise NOT) ~ (Bitwise NOT)
2 * (Multiply), / (Division),
% (Modulo)
* (Multiply), / (Division),
% (Modulo)
* (Multiply), / (Division),
% (Modulo)
3 + (Positive), – (Negative), + (Add), (+ Concatenate), – (Subtract), &
(Bitwise AND)
+ (Positive), – (Negative), + (Add), (+ Concatenate), – (Subtract), &
(Bitwise AND), ^ (Bitwise Exclusive OR), | (Bitwise OR)
+ (Positive), – (Negative), + (Add), (+ Concatenate), – (Subtract), &
(Bitwise AND), ^ (Bitwise Exclusive OR), | (Bitwise OR)
4 =, >, =, <=, ,
!=, !>, !< (Comparison operators)
=, >, =, <=, ,
!=, !>, !< (Comparison operators)
=, >, =, <=, ,
!=, !>, !< (Comparison operators)
5 ^ (Bitwise Exlusive OR),
| (Bitwise OR)
Text Text
6 NOT NOT NOT
7 AND AND AND
8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME ALL, ANY, BETWEEN, IN, LIKE, OR, SOME ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
9 = (Assignment) = (Assignment) = (Assignment)


This post was inspired by a SQL Server session by Bob Beauchemin (Blog | @bobbeauch), at the last version of TechDays NL. Bob, thank you for that!😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: