Data Type and Operator Precedence

DevJef — Sun, 08 Apr 2012 21:08:56 +0000

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!}

Operator – SQL from the Trenches

Data Type and Operator Precedence