Explaining CTE in SQL Server

Introduction

Common Table Expression (CTE) in SQL offers a more readable form of a 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 can self-reflect, and we can also use CTE multiple times in the same query. Mainly, CTE improves readability and makes it easy to maintain complex queries.

CTE can be used for 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 information used to populate the returning columns begins. The CTE is then followed by a select calling it. Always start 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.

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,
      NULL,
      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,
      NULL,
      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 in SQL?

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

Create a recursive query.
Alternative from a view when the general use of an idea is not required, a case in which you do not have to store the definition in metadata.
Enable grouping by a column derived from a scalar subselect or a function that is either not deterministic or has external access.
Reference the resulting table multiple times in the same statement.

CIt can not be used with CTE.

Clauses like ORDER BY, INTO, OPTION clause with query hints, FOR XML, and BROWSE cannot be used in the CTE query definition.
"SELECT DISTINCT," GROUP BY, PIVOT, HAVING, Scalar aggregation, TOP, LEFT, RIGHT, OUTER JOIN, and Subqueries "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 prohibited. For example, if a CTE query definition contains a subquery, then that subquery cannot have a nested WITH clause to define other CTE.

Summary

CTE provides a more readable and usable approach to derived tables. CTE is not materialized into a work table (temporary table). CTEs are not a replacement for temporary Tables. The scope of the CTE is limited to the first SELECT statement only.