The SQL Server CTE (Common Table Expression) feature in SQL Server is a potent tool that expands the possibilities of SQL queries. We’ll examine the background, benefits, and drawbacks of SQL Server CTE, as well as its syntax, various varieties, frequent problems, and useful examples to help you grasp it all in this in-depth guide.
Table of Contents
Introduction
To make complex queries easier to understand and enhance code readability, SQL Server introduced Common Table Expressions (CTEs). Serving as a transient result set, SQL Server CTEs provide a succinct means of generating named queries that can be reused inside the context of a SELECT, INSERT, UPDATE, or DELETE statement.
A Glimpse into History
The introduction of CTEs in SQL Server 2005 represented a major advancement in code organization and query optimization. Since then, SQL Server CTEs have established themselves as a mainstay in SQL development, offering a more sophisticated and effective means of managing recursive queries and streamlining intricate tasks.
Advantages of SQL Server CTE
Advantages of SQL Server CTE are given below:
Readability and Sustainability
By dividing complex queries into smaller, easier-to-read sections, CTEs make them easier to read.
Because modifications to the CTE definition can be made without impacting the query as a whole, they help to make queries more maintainable.
Reusability of Code
SQL Server CTEs allow for multiple references within a query, which encourages code reuse.
This increases the code’s modularity and helps to prevent redundancy.
Recursive Inquiries
Recursive queries, which are supported by CTEs, enable the retrieval of hierarchical data, including organizational structures and tree-like data.
Functions of the Window
When paired with window functions, CTEs offer strong analytical capabilities for data partitioning, aggregation, and ranking.
Enhancement
Performance can occasionally be enhanced by using CTEs in queries that are better optimized by SQL Server’s query optimizer.
Disadvantages of SQL Server CTE
Advantages of SQL Server CTE are given below:
Performance Impact
CTEs can improve code readability, but performance may not always be at its best when they are used. Subqueries and other methods may be more effective in some situations.
Restricted Range
Only the SELECT, INSERT, UPDATE, or DELETE statement in which they are defined can see CTEs due to their restricted scope. They are declared in a query and cannot be referenced outside of it.
SQL Server CTEs nested
SQL Server CTEs cannot be directly nested in SQL Server. Multiple CTEs must be defined independently before being referenced in the main query if you need to use them.
Realization
Alternatively, the query optimizer could decide to materialize the CTE and save the output in a temporary table. In certain situations, this can improve performance, but it can also result in higher storage requirements.
Complexity for Simple Queries
Using CTEs may add needless complexity to simple queries. In these situations, a simple query devoid of CTEs might be easier to read.
Syntax of SQL Server CTE
Within a WITH clause, a Common Table Expression (CTE) is defined in SQL Server. The following is the basic syntax:
; WITH CTEName (column 1, column 2, column 3,..., column N) AS (
-- Definition of CTE query
SELECT column 1, column 2, column 3,..., column N,
FROM <TableName>
WHERE <Condition>
)
-- Actual Query based on the above CTE
SELECT *
FROM CTEName
WHERE <Condition>;The syntax is broken down as follows:
IN COLUMN 1, COLUMN 2, COLUMN 2,…, COLUMN N), WITH CTEName
The CTE declaration begins at this point. The name you give the CTE is CTEName, and to help with readability, (column 1, column 2, column 3,…, column N) lists the optional column names for the CTE.
AS (
An opening parenthesis indicates the start of the CTE’s query definition and is followed by the AS keyword.
Here is the definition of a CTE query: The actual SELECT statement or any other legitimate SQL query that defines the CTE should be written here. Joins, filters, and other operations are examples of it.
Types of CTE in SQL Server
Common Table Expressions (CTEs) in SQL Server come in two primary varieties: recursive and non-recursive.
CTE that is not recursive
A non-recursive CTE is a basic type of CTE in which the CTE’s SELECT statement doesn’t refer to itself. It doesn’t use recursion or self-referencing and is only used for simple queries.
When working with recursive queries on datasets that demand a different recursion level than the default, this option can be helpful.
Syntax:
WITH CTEName (column-1, column-2, ..., column-N) AS (
SELECT column-1, column-2, ....,column-N
FROM <TableName>
WHERE <Condition>
)
SELECT * FROM CTEName WHERE <Condition>;Example:
CREATE TABLE student (StudentID Int IDENTITY(1,1),
StudentFirstName Varchar(15),
StudentLastName Varchar(15),IsActive bit DEFAULT 1,
AdmissionDate DateTime DEFAULT GETDATE())
go
INSERT INTO student(StudentFirstName,StudentLastName)
SELECT 'Sumit','Khana'
UNION
SELECT 'John','Player'
UNION
SELECT 'Smith','Bob'
UNION
SELECT 'John','Smith'
UNION
SELECT 'Steffy','Graph'
UNION
SELECT 'Michal','Kuth'
go
;WITH CTE_Students AS (
SELECT StudentID, StudentFirstName, StudentLastName
FROM Student
WHERE IsActive = 1
)
SELECT * FROM CTE_Students WHERE StudentFirstName='John';
CTE recursive
When a query has to refer to its own result set, it uses a recursive CTE. When managing hierarchical or tree-like data structures, where a record could be related to other records in the same table, this is especially helpful.
Syntax:
WITH RecursiveCTE (column-1, column-2, column-3,...) AS (
SELECT column-1,column-2, column-3, ...
FROM <TableName>
WHERE <condition>
UNION ALL
-- Recursive member (refers to the CTE itself)
SELECT column-1, column-2, column-3,...
FROM RecursiveCTE
WHERE <RecursiveCondition>
)
SELECT * FROM RecursiveCTE;Example:
Use TestDB
go
CREATE TABLE IncidentCategories (
IncidentCategoryID INT PRIMARY KEY,
IncidentCategoryName NVARCHAR(250),
ParentIncidentCategoryID INT
);
INSERT INTO IncidentCategories (IncidentCategoryID, IncidentCategoryName, ParentIncidentCategoryID)
VALUES
(1, 'A SQL job Failed To Complete Successfully.', NULL),
(2, 'A SQL job Failed Due To Blocking.', 1),
(3, 'A SQL job Failed Because User has Cancelled the Job.', 1),
(4, 'The Table Was Blocked.', 2),
(5, 'Database Was Blocked.', 2),
(6, 'System has cancelled the job due to deadlock.', 3),
(7, 'User has Cancelled the job.', 3),
(8, 'Availability Group is not ready for automatic failover.', NULL),
(9, 'Database is in recovery pending.', 8),
(10, 'Secondary Replica is not ready.', 8),
(11, 'CPU Utilization Percentage is too high.', Null),
(12, 'Database is consuming more than 90% on the server.', 11),
(13, 'Currently Anti-Virus is running & consuming more than 90% on the server.', 11),
(14, 'Observed CPU pressure on the server.', 11);
WITH RecursiveIncidentCategoryCTE (IncidentCategoryID, IncidentCategoryName, ParentIncidentCategoryID, HierarchyLevel) AS (
-- Query for Parent / Top Level details
SELECT
IncidentCategoryID,
IncidentCategoryName,
ParentIncidentCategoryID,
1 AS HierarchyLevel
FROM
IncidentCategories
WHERE
ParentIncidentCategoryID IS NULL
UNION ALL
-- Query for Child/Lower Level details
SELECT
c.IncidentCategoryID,
c.IncidentCategoryName,
c.ParentIncidentCategoryID,
rc.HierarchyLevel + 1
FROM
IncidentCategories c
JOIN
RecursiveIncidentCategoryCTE rc ON c.ParentIncidentCategoryID = rc.IncidentCategoryID
)
-- Query to fetch details from actual CTE
SELECT
IncidentCategoryID,
IncidentCategoryName,
ParentIncidentCategoryID,
HierarchyLevel
FROM
RecursiveIncidentCategoryCTE;
DROP TABLE IncidentCategories
go
As a CTE, the in-line table-valued function (ITVF)
A user-defined function that yields a table is called an in-line table-valued function. Like a table or view, it can be used inside a FROM clause. An ITVF serves as a data source for a CTE when it is utilized within one.
This method of using an ITVF gives you a systematic and reusable solution to encapsulate logic in a function that you can utilize in CTEs or other sections of your SQL queries.
Syntax:
CREATE FUNCTION <FunctionName> (@Parameter-1 DataType, @Parameter-2 DataType, ...)
RETURNS TABLE
AS
RETURN
(
SELECT column-1, column-2, ... FROM <TableName>
WHERE <condition>
);
-- Using the ITVF within a CTE
WITH CTEName AS (
SELECT *
FROM <FunctionName(@Parameter1, @Parameter2, ...)>
)
SELECT * FROM CTEName;Example:
CREATE FUNCTION GetPassOutStudents()
RETURNS TABLE
AS
RETURN
(
SELECT StudentID, StudentFirstName, StudentLastName
FROM Students
WHERE IsActive = 1
);
-- Using the ITVF within a CTE
WITH PassOutStudentsCTE AS (
SELECT *
FROM GetPassOutStudents()
)
SELECT *
FROM PassOutStudentsCTE;Materialized CTE
It is important to note that SQL Server may decide to materialize the result of a CTE, storing it in a temporary table, even though it is not a distinct type in terms of syntax. We refer to this as a “Materialised CTE.” In some cases, materialization can enhance performance, particularly when the result set is utilized more than once in a single query. But it can also result in more storage being used.
It’s critical to know that the query optimizer determines whether to materialize a CTE based on several variables, including the query’s complexity. Usually, developers have no explicit control over this behavior.
WITH MaterializedCTEName AS (
SELECT column-1, column-2, column-3, ....,column-N
FROM <TableName>
WHERE <condition>
)
SELECT * FROM MaterializedCTEName;For optimization reasons, the query optimizer could decide to materialize the MaterializedCTE.
Recursive CTE with Several Anchor Participants
A recursive CTE may have more than one anchor member. The non-recursive term in the SQL Server CTE is called an anchor member. When you wish to begin the recursive portion of the CTE from various initial conditions, this can be helpful.
Syntax:
WITH RecursiveCTE (column-1, column-2, column-3, ....,column-N) AS (
SELECT column-1, column-2, column-3, ....,column-N
FROM <TableName>
WHERE <condition-1>
UNION ALL
SELECT column-1, column-2, column-3, ....,column-N
FROM <TableName>
WHERE <condition-2>
UNION ALL
-- Recursive member
SELECT column-1, column-2, column-3, ....,column-N
FROM <RecursiveCTE>
WHERE <Eecursive_Condition>
)
SELECT * FROM RecursiveCTE;This enables you to set up several beginning places for the CTE’s recursive portion.
Several CTEs in One Query
A SQL query may contain more than one CTE, each with a different name. These CTEs can be used independently in the primary query or created collectively within a single WITH clause. They can also be used as references to one another.
Syntax:
WITH CTEName-1 AS (
-- Definition of CTE-1),
CTE-2 AS (
-- Definition of CTE-2 which can refer CTE1
)
SELECT * FROM CTE2;This method divides a complicated query into smaller, easier-to-read sections, improving modularity and readability.
Example
-- Create a Employees master table
CREATE TABLE mEmployees (
EmpID INT PRIMARY KEY,
EmpFirstName NVARCHAR(50),
EmpLastName NVARCHAR(50),
DOJ DateTime Default GETDATE(),
ManagerID INT,
);
-- Insert Sample data in Employees master table
INSERT INTO mEmployees (EmpID, EmpFirstName, EmpLastName, ManagerID)
VALUES
(1, 'Broce', 'Johnson', NULL),
(2, 'Sudhir', 'Kumar', 1),
(3, 'Tom', 'Alter', 2),
(4, 'John', 'Shena', 2),
(5, 'Jacky', 'Chan', 1),
(6, 'Atul', 'Patel', 5),
(7, 'Scott', 'Miller', 5),
(8, 'Cincy', 'Cooper', 2);
-- Query with multiple CTEs
;WITH GetEmpManagerDetailsCTE AS (
SELECT EmpID AS ManagerID,EmpFirstName AS ManagerFirstName,
EmpLastName AS ManagerLastName FROM mEmployees
WHERE EmpID IN (SELECT DISTINCT ManagerID FROM mEmployees WHERE ManagerID IS NOT NULL)
),
EmployeeHierarchyCTE AS (SELECT e.EmpID, e.EmpFirstName,
e.EmpLastName,e.ManagerID,m.ManagerFirstName,m.ManagerLastName
FROM mEmployees e
LEFT JOIN
GetEmpManagerDetailsCTE m ON e.ManagerID = m.ManagerID
)
-- Final Query to select the required data
SELECT EmpID, EmpFirstName, EmpLastName, ManagerID,
ManagerFirstName, ManagerLastName
FROM EmployeeHierarchyCTE;
Drop Table mEmployees;
SQL Server CTE with INSERT, DELETE, or UPDATE statement
Though they are frequently utilized in SELECT statements, CTEs can also be applied in DELETE, INSERT, UPDATE, and UPDATE commands. This offers a mechanism to organize inquiries for changing data and permits more sophisticated operations.
Syntax:
WITH UpdateCTE AS (
--CTE Definition for data updation
)
UPDATE <TargetTableName>
SET column-1 = UpdateCTE.NewValue
FROM UpdateCTE
WHERE TargetTableName.id = UpdateCTE.id;In this instance, the source of the modified data is the CTE.
Example
-- Create a Employees master table
CREATE TABLE mEmployees (
EmpID INT PRIMARY KEY,
EmpFirstName NVARCHAR(50),
EmpLastName NVARCHAR(50),
Dept VARCHAR(2),Salary DECIMAL(18, 2)
);
-- Insert Sample data in Employees master table
INSERT INTO mEmployees (EmpID, EmpFirstName, EmpLastName,Dept, Salary)
VALUES
(1, 'Broce', 'Johnson', 'IT', 35000),
(2, 'Sudhir', 'Kumar', 'HR',28000),
(3, 'Tom', 'Alter', 'FN',30000),
(4, 'John', 'Shena', 'AD',50000),
(5, 'Jacky', 'Chan', 'FN',19000),
(6, 'Atul', 'Patel', 'HR',16000),
(7, 'Scott', 'Miller', 'IT',34000),
(8, 'Cincy', 'Cooper', 'HR',37000),
(9, 'Michel', 'Miller', 'IT',34000),
(10, 'Cindy', 'Bush', 'HR',37000);
-- Query with multiple CTEs
;WITH EmpSalaryCalculationCTE AS (
SELECT EmpID, EmpFirstName, EmpLastName, Dept, Salary,
(CASE
WHEN Dept IN ('IT','FN') THEN Salary * 1.25
ELSE Salary
END) AS NewSalary
FROM mEmployees
)
-- UPDATE Salary with new salary
UPDATE mEmployees SET Salary = sac.NewSalary
FROM EmpSalaryCalculationCTE sac
WHERE mEmployees.EmpID = sac.EmpID;
-- Main query to select required data
SELECT EmpID, EmpFirstName, EmpLastName, Dept, Salary
FROM mEmployees;
Drop Table mEmployees;
Recursive SQL Server CTE with the option for MAXRECURSION
To avoid infinite loops, recursive CTEs have a default maximum recursion level. The maximum number of permitted recursion levels can be changed, if necessary, by using the MAXRECURSION option.
Syntax:
WITH RecursiveCTE (column-1, column-2, column-3,..., column-N) AS (
-- Definition of Recursive CTE)
SELECT * FROM RecursiveCTE
OPTION (MAXRECURSION 100);Typical Problems with SQL Server CTE
Even though SQL Server’s typical Table Expressions (CTEs) are strong and helpful, developers may run across a few typical problems. The following are some common issues linked to CTEs:
Limitations of Recursion
To avoid infinite loops, recursive CTEs have a default maximum recursion level (e.g., 100). You must change the MAXRECURSION parameter if your data calls for greater recursion levels. But without careful thought, raising the recursion level might cause problems with performance or even stack overflow situations.
Inaccurate CTE Citations
Errors may arise from inappropriate CTE names or improper CTE column references in the main query. Make sure the query specifies column names and CTE names accurately.
Misuse of CTEs for Task Effectiveness
CTEs can make queries easier to read, but they may not necessarily result in faster performance. Other strategies, like temporary tables or subqueries, might be more effective in some circumstances. Examining the execution plan and selecting the best approach for the given query and dataset is crucial.
Overly Complicated
When CTEs are overused for straightforward queries, more complexity might be added, making the code more difficult to read. It’s critical to strike a balance between the task’s complexity and the usage of CTEs.
Impact of Materialization
Although the materialization of CTEs can sometimes result in better performance, it can also need more storage. When a CTE is assigned to materialize by the query optimizer, developers need to be mindful of the possible impact on resources.
Nested SQL Server CTEs
CTEs cannot be directly nested within one another in SQL Server. If more than one CTE is required, define them independently and provide references to them in the main query.
FAQs
Q: What does a SQL Server CTE mean?
Ans: A named temporary result set used inside the parameters of an SQL statement is called a Common Table Expression, or CTE.
Q: When did SQL Server first introduce CTE?
Ans: SQL Server 2005 brought CTEs into the fold.
Q: What does a CTE’s syntax look like?
Ans: Use the CTE name and query definition after the WITH clause.
Q: Is it possible to use CTEs for recursive queries?
Ans: Recursive CTEs are made expressly to deal with recursive queries.
Q: Can CTEs be reused?
Ans: Yes, by generating named, reusable query blocks, CTEs encourage code reuse.
Q: What possible drawbacks might there be to using CTEs?
Ans: It’s important to optimize queries because improper use of CTEs can negatively affect performance.
Q: How can infinite loops in recursive CTEs be avoided?
Ans: Provide appropriate termination conditions and carefully design recursive CTEs to prevent infinite loops.
Q: Is it possible to use more than one CTE in a single query?
Ans: A query containing several SQL Server CTEs can handle various aspects of the data.
Q: Do CTEs only work with SELECT statements?
Ans: SELECT, INSERT, UPDATE, and DELETE statements can all be used with CTEs.
Q: Do you have CTEs that are nested?
Ans: It is possible to nested CTEs, enabling more intricate and structured queries.
Power BI vs SSRS: A Complete Comparison
Q: What is the primary goal of CTE application?
Ans: Enhancing the readability and maintainability of complex queries is the major goal of SQL Server CTEs. They enable you to divide a query into named, modular subqueries, which improves the organization and readability of the code.
Q: Is it possible to combine CTEs with other SQL features?
Ans: Yes, you can conduct more intricate and potent queries by combining CTEs with other SQL capabilities like window functions, joins, and aggregate functions.
Q: Do SQL Server CTEs appear on their own volition?
Ans: The SQL Server query optimizer determines whether to materialize a CTE or store its result in a temporary table. It is dependent upon variables like query complexity and performance issues. Developers do not have direct control over this process.
Q: Could I use more than one CTE in the same query?
Ans: Indeed, you may define more than one SQL Server CTE in the same query by placing commas between each one after the WITH keyword. These CTEs may appear in the primary query as standalone terms or as references to one another.
Q: Are transactions supported by SQL Server CTEs?
Ans: Transactions are not intrinsically supported by SQL Server CTEs. Nevertheless, when paired with other SQL statements in a transaction, they can be utilized inside a bigger transaction block.
Q: Is it possible to use CTEs in DELETE or UPDATE statements?
Ans: It is possible to use CTEs in INSERT, DELETE, and UPDATE commands. They can be used as a data source or as guidelines for adding, removing, or altering records.
Q: What restrictions apply to recursive CTEs?
Ans: To avoid infinite loops, recursive SQL Server CTEs contain restrictions on the default maximum recursion level. Developers must exercise caution while using the MAXRECURSION setting and be aware of these limitations.
Q: How can I troubleshoot CTE problems?
Ans: By closely examining the query syntax, making sure that proper scoping is done, and examining the execution plan, problems with CTEs can be troubleshooted. It’s critical to review your work for faults and comprehend the particular requirements of your query and data.
Q: Can a stored procedure take advantage of a CTE?
Ans: It is possible to employ CTEs in stored procedures. They offer a mechanism to organize and divide up intricate inquiries inside the framework of a stored procedure.
Q: Do any other options for structuring complex queries outside SQL Server CTEs?
Ans: Subqueries, derived tables, and temporary tables are examples of alternatives. The decision is based on the developer’s preferred code organization scheme as well as the particular requirements of the query.
Conclusion
Gaining proficiency with SQL Server CTE gives you access to improved query capabilities and more efficient code organization, enabling you to confidently take on challenging database tasks. Try using CTEs in your SQL projects and see how they improve the clarity and efficiency of your database queries.
Check below articles also
A Powerful Merge Statement in SQL Server
Understand Deadlocks in SQL Server
Unleash Database Insights with Extended Events in SQL Server
SQL Server Pivot: Top 5 Concepts
Unleash Database Insights with Extended Events in SQL Server: A Deep Div
A Powerful Merge Statement in SQL Server
Deadlocks in SQL Server : Understanding and Resolving Database Concurrency Issues