What Is SQL Common Table Expression (CTE)

Common Table Expression in SQL Server offers a more readable form of the derived table, which can be declared once and referenced multiple times in a query.

Introduction
 
Common Table Expression (CTE) was introduced in SQL Server 2005, which offers a more readable form of the derived table. A Common Table Expression is an expression that returns a temporary result set. This result set is similar to a hybrid Derived Table. The resultset can be declared once and referenced multiple times in a query. It does not require any extra effort to declare it. 
 
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 
  1. ;WITH CTE_name [ ( column_name [,...n] ) ]  
  2. AS  
  3. (  
  4. query_definition  
  5. )  
  6. 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. Create a query 
 
The following is a sample of creating two tables, EmployeeMasters and DepartmentMasters, and inserting some default values into them: 
  1. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DepartmentMasters]'AND type in (N'U'))  
  2. DROP TABLE [dbo].[DepartmentMasters]  
  3. CREATE TABLE [dbo].[DepartmentMasters](  
  4. [DepartmentId] [int] IDENTITY(1,1) NOT NULL,  
  5. [DepartmentCode] [varchar](50) NULL,  
  6. [DepartmentName] [varchar](50) NULL,  
  7. CONSTRAINT [PK_DepartmentMasters] PRIMARY KEY CLUSTERED  
  8. (  
  9. [DepartmentId] ASC  
  10. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  11. ON [PRIMARY]  
  12. GO  
  13. SET ANSI_PADDING OFF  
  14. GO  
  15. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EmployeeMasters]'AND type in (N'U'))  
  16. DROP TABLE [dbo].[EmployeeMasters]  
  17. CREATE TABLE [dbo].[EmployeeMasters](  
  18. [EmployeeId] [int] IDENTITY(1,1) NOT NULL,  
  19. [EmployeeName] [varchar](50) NULL,  
  20. [EmployeeCode] [varchar](50) NULL,  
  21. [DepartmentId] [intNULL,  
  22. CONSTRAINT [PK_EmployeeMasters] PRIMARY KEY CLUSTERED  
  23. [EmployeeId] ASC  
  24. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  25. ON [PRIMARY]  
  26. GO  
  27. SET ANSI_PADDING OFF  
  28. GO  
  29. ALTER TABLE [dbo].[EmployeeMasters] WITH CHECK ADD CONSTRAINT [FK_EmployeeMaster_DepartmentMaster] FOREIGN KEY([DepartmentId])  
  30. REFERENCES [dbo].[DepartmentMasters] ([DepartmentId])  
  31. GO  
  32. ALTER TABLE [dbo].[EmployeeMasters] CHECK CONSTRAINT [FK_EmployeeMaster_DepartmentMaster]  
  33. GO  
  34. INSERT INTO DepartmentMasters VALUES  
  35. ('Eaxm''Examination'),  
  36. ('Staff''Exam Staff')  
  37. INSERT INTO EmployeeMasters VALUES  
  38. ('Jignesh','D0093',1),  
  39. ('tejas','D0094',1),  
  40. ('Rakesh','D0095',1),  
  41. ('Umesh','D0096',2),  
  42. ('Punit','D0097',2)  
Step 2. Writing CTE Query
 
The following is a sample use of a CTE Query: 
  1. ;WITH emp_detail(EmployeeName,EmployeeCode,DepartmentCode)  
  2. AS  
  3. (  
  4. SELECT e.EmployeeCode,e.EmployeeName,d.DepartmentCode FROM EmployeeMasters e JOIN DepartmentMasters d ON e.DepartmentId=d.DepartmentId  
  5. )  
  6. SELECT * FROM emp_detail;  
When to Use CTE
 
CTE offers the same functionality as a View (ideal for one-off usages). CTE offers the following four advantages: 
  • Create a recursive query. 
  • 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.
  • 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.
  • 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 provides a more readable and usable approach to derived tables. CTE is not materialised into a work table (temporary table). CTEs are not a replacement of Temporary Tables. The scope of the CTE is limited to the first SELECT statement only.
 
Reference
 
http://msdn.microsoft.com/en-us/library/ms190766.aspx