Common Table Expression (CTE) in SQL Server

Introduction

Common Table Expression (CTE) was introduced in SQL Server 2005, which offers a more readable form of the derived table, which can be declared once and referenced multiple times in a query. It does not require any extra effort to declare it. A Common Table Expression is an expression that returns a temporary result set. This result set is similar to a hybrid Derived Table.

CTE is more powerful than the derived table. It is capable of self referencing and we can also use CTE multiple times in the same query. Mostly CTE is used to improved readability and makes it easy to maintain complex queries.

CTE can be used for both selects and DML (Insert, Update and Delete) statements.

Common Structure of CTE

;WITH CTE_name [ ( column_name [,...n] ) ]
AS
(
query_definition
)
select * from CTE_name;

The Common Table Expression is created using the WITH statement followed by the CTE name and List of Columns (specifying a column is optional). After the "AS", the statement used to populate the returning columns begins. The CTE is then followed by a select calling it. Always begin CTE with a semi-colon.

Example

Step 1

The following is a sample of creating two tables, EmployeeMasters and DepartmentMasters, and inserting some default values into them:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DepartmentMasters]') AND type in (N'U'))
DROP TABLE [dbo].[DepartmentMasters]

CREATE TABLE [dbo].[DepartmentMasters](
      [DepartmentId] [int] IDENTITY(1,1) NOT NULL,
      [DepartmentCode] [varchar](50) NULL,
      [DepartmentName] [varchar](50) NULL,
 CONSTRAINT [PK_DepartmentMasters] PRIMARY KEY CLUSTERED
(
      [DepartmentId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EmployeeMasters]') AND type in (N'U'))
DROP TABLE [dbo].[EmployeeMasters]
CREATE TABLE [dbo].[EmployeeMasters](
      [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
      [EmployeeName] [varchar](50) NULL,
      [EmployeeCode] [varchar](50) NULL,
      [DepartmentId] [int] NULL,
 CONSTRAINT [PK_EmployeeMasters] PRIMARY KEY CLUSTERED

      [EmployeeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[EmployeeMasters]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeMaster_DepartmentMaster] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[DepartmentMasters] ([DepartmentId])
GO

ALTER TABLE [dbo].[EmployeeMasters] CHECK CONSTRAINT [FK_EmployeeMaster_DepartmentMaster]
GO

INSERT INTO DepartmentMasters VALUES
('Eaxm', 'Examination'),
('Staff', 'Exam Staff')

INSERT INTO EmployeeMasters VALUES
('Jignesh','D0093',1),
('tejas','D0094',1),
('Rakesh','D0095',1),
('Umesh','D0096',2),
('Punit','D0097',2)

Step 2

Writing CTE Query

The following is a sample use of a CTE Query:

;WITH emp_detail(EmployeeName,EmployeeCode,DepartmentCode)
AS
(
SELECT e.EmployeeCode,e.EmployeeName,d.DepartmentCode FROM EmployeeMasters e JOIN DepartmentMasters d ON e.DepartmentId=d.DepartmentId
)
SELECT * FROM emp_detail;

When to Use CTE

CTE offers the same functionality as a View (ideal for one-off usages). Microsoft offers the following four advantages of CTEs:

1. Create a recursive query.

2. Alternative from a view when the general use of a view is not required, case in which, you do not have to store the definition in metadata.

3. Enable grouping by a column that is derived from a scalar sub select, or a function that is either not deterministic or has external access.

4. Reference the resulting table multiple times in the same statement.

Cannot use with CTE

The clauses like ORDER BY, INTO, OPTION clause with query hints, FOR XML, FOR BROWSE, cannot be used in the CTE query definition.

"SELECT DISTINCT", GROUP BY, PIVOT, HAVING, Scalar aggregation, TOP, LEFT, RIGHT, OUTER JOIN and Sub queries are not allowed in the CTE query definition of a recursive member.

A CTE can be self-referencing and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.

Specifying more than one "WITH" clause in a CTE is not allowed. For example, if a CTE query definition contains a sub query then that sub query cannot contain a nested WITH clause to define other CTE.

Conclusion

CTE is provide a more readable and usable approach to derived tables. CTE is not materialised into a work table (temporary table). CTE are not replacement of the Temporary Table or Temporary Variable Table. The scope of the CTE is limited to the first SELECT statement.

Reference
 
http://msdn.microsoft.com/en-us/library/ms190766.aspx