In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.

In today's post I will cover Common Table Expressions(CTEs). Common Table Expressions were introduced in SQL Server 2005 and you can think of them as a derived table in another form of a table expression.

The syntax looks like this

[ WITH  [ ,...n ] ]

::=
    expression_name [ ( column_name [ ,...n ] ) ]
    AS
    ( CTE_query_definition )

Here are the arguments:

expression_name
Is a valid identifier for the common table expression. expression_name must be different from the name of any other common table expression defined in the same WITH clause, but expression_name can be the same as the name of a base table or view. Any reference to expression_name in the query uses the common table expression and not the base object.


column_name
Specifies a column name in the common table expression. Duplicate names within a single CTE definition are not allowed. The number of column names specified must match the number of columns in the result set of the CTE_query_definition. The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.


CTE_query_definition
Specifies a SELECT statement whose result set populates the common table expression. The SELECT statement for CTE_query_definition must meet the same requirements as for creating a view, except a CTE cannot define another CTE.

If more than one CTE_query_definition is defined, the query definitions must be joined by one of these set operators: UNION ALL, UNION, EXCEPT, or INTERSECT.

With Common Table Expressions you can use two form of table aliasing; external and internal. If the columns are aliased in the query definition itself then this is an internal alias

If you supply the column names in the parentheses right after the Common Table Expression name then this is external aliasing

If you use both external and internal then the external names will show up in the result

Output

bla	bla2
-------------
1	2

Every Common Table Expression has to start with WITH, the statement before it has to be terminated with a semicolon. What you can do is start your Common Table Expression with semicolon followed by WITH (;WITH) this way if someone adds some code before your Common Table Expression it won't mess up the proc or sql script

Here is an example of one Common Table Expression where the previous statement wasn't terminated with a semicolon

And here is the error
Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Adding the semicolon before WITH fixes that

Some more examples

Paging with Common Table Expressions

I will cover the windowing functions tomorrow but here is just a small example of how you can use a Common Table Expression to do paging

Here are my results

row name
11 assembly_types
12 asymmetric_keys
13 availability_group_listener_ip_addresses
14 availability_group_listeners
15 availability_groups
16 availability_read_only_routing_lists
17 availability_replicas
18 backup_devices
19 certificates
20 change_tracking_databases

Okay that is all fine, what if I want to also add a total count? You can reference a Common Table Expression more than once in a query so that is no problem, here is what the query looks like

Here is another example that selects more than once from the same Common Table Expression

However if you reference the Common Table Expression in more than one query you will get an error, run this

Msg 208, Level 16, State 1, Line 6
Invalid object name 'cte'.

As you can see, you got an invalid object name error.

If you put a UNION in between the two queries, it will also work fine since it is considered one query

You can also do multiple levels of recursion with Common Table Expression. Here is an example from books on line. First create this table in the AdventureWorks2008R2 database

Now this is the query that will give you the first two levels of the employee hierarchy

ManagerID   EmployeeID Title                                    EmployeeLevel
----------- ---------- -------------------------------------------------- ---
NULL        1          Chief Executive Officer                            0
1           273        Vice President of Sales                            1
273         16         Marketing Manager                                  2
273         274        North American Sales Manager                       2
273         285        Pacific Sales Manager                              2

You can also limit the number of recursions by applying a hint, for example OPTION (MAXRECURSION 5), that will limit it to 5 levels.

Besides just selecting from Common Table Expression you can also manipulate data through them, the underlying tables will then have the changes. See my post Use common table expressions to simplify your updates in SQL Server to learn more about that