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! ;)

Row_Number: Unique ID in select statement

Last week I was asked by a colleague, Sander (Blog | @SanderKooij), “What’s the easiest way of adding a unique identifier (or row ID) to a result set?”. That’s an interesting question. There are several ways to do this. For example, you could insert the resultset into a temp table (physical or memory temp table), with an extra column (Identity). But what if you want to do this in a Select statement itself? Here’s a solution.

If you use the scripts I’ve added to this post (check Code Samples), you’ve created a table with country abbreviation codes. This table contains the data as shown below:

If you select the data, and want to add a record ID to your result set, you can use the following script:

SELECT
	DefaultAbbreviation,
	CountryProper,
	CountryNumber,
	ROW_NUMBER() OVER(ORDER BY DefaultAbbreviation ASC) AS RowNumber
FROM CountryAbbreviationCodes

The statement above uses the ROW_NUMBER built-in function to generate a row number for the result set. The ORDER BY clause in the functions is used to tell SQL Server what column(s) to use, to create a row number. In this case, the result set is ordered by the abbreviation column.

But what if you want to select the data with a specific row number? If you try to use the ROW_NUMBER function in the where clause, you get the following error:

“Windowed functions can only appear in the SELECT or ORDER BY clauses.”

So you need another solution. From SQL Server 2005 onwards we can use a Common Table Expression (CTE). With a CTE you can use a select statement as a table. So if you want to return the rows that are numbered 50 through 60, you can use the following query:

WITH OrderedCountries AS
(
	SELECT
		DefaultAbbreviation,
		CountryProper,
		CountryNumber,
		ROW_NUMBER() OVER(ORDER BY DefaultAbbreviation ASC) AS RowNumber
	FROM CountryAbbreviationCodes
)

SELECT
	DefaultAbbreviation,
	CountryProper,
	CountryNumber,
	RowNumber
FROM OrderedCountries
WHERE 1 = 1
AND RowNumber BETWEEN 50 AND 60

The result of this statement is the 10 rows we requested. So with a CTE we can use the ROW_NUMBER function to return specific rows from a table.

With the ROW_NUMBER functions, you can also delete duplicate records from your tables. If interested, post a comment, mail or tweet me, and I might write a blog post about it.


Code samples:
CreateSources.sql
SelectStatement.sql
CTESelectStatement.sql

Query order of execution

If you write a SQL query, it’s important to know when certain pieces of you query are executed. For example, it’s possible that some statement in your query interferes with another part of your query, because of the execution time of a specific piece.

The order of execution is:

1. FROM Clause
2. JOIN / APPY / PIVOT / UNPIVOT Clause
3. WHERE Clause
4. GROUP BY Clause
5. CUBE / ROLLUP Clause
6. HAVING Clause
7. SELECT Clause
8. DISTINCT Clause
9. TOP Clause
10. ORDER BY Clause

So in many cases this won’t be a problem. But in some cases this can pose as a problem. In those cases it’s important to know the order of execution if you want to debug your query.

Sources: Logical Query Processing
Also check out this video if you want to learn more about this subject.

Follow

Get every new post delivered to your Inbox.

Join 53 other followers