This is the first article in the series of articles on Common Table Expression in Sql Server. Below are the other articles in this series:

Recursive CTE
Multiple CTEs in a Single Query
Nested Common Table Expressions

Introduction to Common Table Expression

Common Table expression (a.k.a CTE) is one of the new feature introduced in Sql Server 2005. CTE provides the structured way for writing the complex queries as a simple readable and maintainable queries. CTEs provides an alternative way of writing sub queries in a more readable and maintainable way.

CTEs are something like temporary named result sets, but they are not exactly as temporary table or table variable. CTEs doesn’t use the TempDB for maintaining the temporary named result set. And also the scope of the CTEs temporary named result set is to the immediate next SELECT, INSERT, UPDATE or DELETE statement only.

CTEs are like views, for views meta data is stored in the database but not for CTES. From this point of view we can call CTEs as a dynamic view or temporary view.

In most of the cases CTEs performance is on par with the existing sub queries, table variable or temp table approaches. The main thing with CTEs is, it apart from providing a mechanism to write simple readable and maintainable queries. It also provides a mechanism to write recursive queries. And the current recursion level supported by the recursive function or stored procedure is 32 level only, where as by default max recursion level which CTE is 100. But CTE provides an option to set a MAXRECURSION level value between 0 to 32,767 . Specifying it’s value as 0 means no limit, you agreed for a risk and server crash by a bugy query if any 🙂

CTE SYNTAX

[ WITH <common_table_expression> [ ,...n ] ]

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

I think we had enough theory, let us jump on to the examples to understand all the above explained theory.

CTE Syntax explained by a very basic example

To demonstrate the CTE feature with extensive list of examples, let us create a demo database SqlHintsCTED with Employees table having the sample data as shown in the below image by the following script:

Script:

--Create demo database
CREATE DATABASE SqlHintsCTE
GO
USE SqlHintsCTE
GO
-- Create an Employees table.
CREATE TABLE dbo.Employees
( Id INT  PRIMARY KEY, Name NVARCHAR(50), ManagerId INT NULL)
GO
-- Populate Employees table with sample data
INSERT INTO dbo.Employees VALUES
 (1, 'Prabhu Biradar',NULL) ,(2, 'Sharan Biradar', 1)
,(3, 'Monty Biradar', 2), (4, 'Shashank Biradar', 2)
,(5, 'Basavaraj Biradar',1),(6, 'Keerthi Biradar',1)
,(7, 'Shree Biradar',5), (8,'Shree Sharan', 7)

Very basic CTE example, here skipped defining the optional CTE column_names

Below is an example of a very basic CTE, in this CTE example the optional CTE column_names are not specified. So, in this case the the column names specified in the CTE query_definition will be returned.

WITH DemoCTE
AS (SELECT Id, Name FROM dbo.Employees)
SELECT * FROM DemoCTE

RESULT:

If CTE is not the first statement in the batch, then the previous statement to it Should be terminated with a semicolon.

Let us understand this with an example. In the below example we have PRINT statement before the CTE, let us see what is the result of executing the following script:

PRINT 'CTE Demo'
WITH CTESemicolon
AS (SELECT Id, Name FROM dbo.Employees WITH(NOLOCK))
SELECT * FROM CTESemicolon WHERE id = 2
GO

RESULT:

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.

To resolve this error let us terminate the PRINT statement with a Semicolon and see what is the result:

PRINT 'CTE Demo';
WITH CTESemicolonPrefix
AS (SELECT Id, Name FROM dbo.Employees WITH(NOLOCK))
SELECT * FROM CTESemicolonPrefix WHERE id = 2
GO

RESULT:

From the above result it is clear that, if CTE is not the first statement in the batch of statements, then the previous statement to it must be terminated with a semicolon.