CTE Description
Advantages
Using Common Table Expressions (CTEs) in SQL Server offers several advantages that improve code readability, maintainability, and functionality. Below are the key benefits:
Improved Readability
Simplified Query Structure: CTEs make queries easier to read by breaking complex logic into smaller, manageable parts.
Structured Query Building: Queries with multiple levels of derived tables are more intuitive when rewritten using CTEs.
Reusability Within the Query
Temporary Naming: CTEs allow you to define temporary result sets that can be reused within the query.
Logical Steps: You can process data step-by-step, with each CTE building on the results of the previous one.
Recursive Queries
CTEs are essential for recursive queries, enabling operations like:
Hierarchical data processing (e.g., organizational charts, folder structures).
Generating sequences or ranges of numbers or dates.
Enhanced Debugging and Maintenance
Easier Debugging: Breaking complex logic into smaller parts makes it easier to test and debug individual components.
Isolation of Logic: Allows you to focus on specific portions of the query without interference from the rest.
Replacing Derived Tables
CTEs can replace derived tables or subqueries, providing:
Better readability.
No need to nest complex subqueries, which can be harder to follow.
Modularity in Query Building
Multiple CTEs: You can define multiple CTEs in a single query, enabling modular and logical flow.
Chaining Transformations: Build data transformations step-by-step, which is helpful in complex aggregations or data reshaping.
Performance Benefits in Some Scenarios
While not inherently faster, CTEs can improve performance by making the query plan easier for the SQL Server optimizer to interpret.
Recursive CTEs can efficiently handle specific hierarchical or iterative processes.
Better Integration with Window Functions
CTEs work seamlessly with window functions like RANK(), ROW_NUMBER(), or SUM() to simplify analytical queries.
Temporary Scope
The data in a CTE exists only for the duration of the query, reducing memory overhead and ensuring temporary computations don’t persist.
Cleaner and More Maintainable Code
In team environments or complex applications, CTEs enhance maintainability and collaboration by organizing code in a readable and modular way.
Queries - Select
Simple CTE for Organizing Data
Example: List employees with their departments.
WITH EmployeeData AS (
SELECT
EmployeeID,
FirstName,
LastName,
DepartmentID
FROM Employees
)
SELECT
EmployeeID,
FirstName,
LastName,
DepartmentName
FROM EmployeeData
JOIN Departments ON EmployeeData.DepartmentID = Departments.DepartmentID;
Recursive CTE for Hierarchical Data
Example: Find a hierarchy of managers and subordinates.
WITH ManagerHierarchy AS (
SELECT
EmployeeID,
ManagerID,
FirstName,
LastName,
0 AS Level
FROM Employees
WHERE ManagerID IS NULL -- Top-level managers
UNION ALL
SELECT
e.EmployeeID,
e.ManagerID,
e.FirstName,
e.LastName,
mh.Level + 1
FROM Employees e
INNER JOIN ManagerHierarchy mh ON e.ManagerID = mh.EmployeeID
)
SELECT
EmployeeID,
FirstName,
LastName,
Level
FROM ManagerHierarchy
ORDER BY Level, FirstName;
CTE for Ranking Data
Example: Rank employees based on salary within each department.
sql
Copy code
WITH RankedEmployees AS (
SELECT
EmployeeID,
FirstName,
LastName,
DepartmentID,
Salary,
RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RankInDepartment
FROM Employees
)
SELECT
DepartmentID,
EmployeeID,
FirstName,
LastName,
Salary,
RankInDepartment
FROM RankedEmployees
WHERE RankInDepartment <= 3; -- Top 3 salaries in each department
CTE for Aggregations
Example: Calculate total sales per employee.
WITH SalesPerEmployee AS (
SELECT
SalesPersonID,
SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY SalesPersonID
)
SELECT
sp.FirstName,
sp.LastName,
se.TotalSales
FROM SalesPerEmployee se
JOIN SalesPersons sp ON se.SalesPersonID = sp.SalesPersonID
ORDER BY se.TotalSales DESC;
CTE for Working with Dates
Example: Generate a range of dates.
WITH DateRange AS (
SELECT CAST('2024-01-01' AS DATE) AS ReportDate
UNION ALL
SELECT DATEADD(DAY, 1, ReportDate)
FROM DateRange
WHERE ReportDate < '2024-01-10'
)
SELECT ReportDate
FROM DateRange
OPTION (MAXRECURSION 0);
Multiple CTEs in a Single Query
Example: Use multiple CTEs to calculate results step-by-step.
WITH SalesData AS (
SELECT
SalesPersonID,
SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY SalesPersonID
),
TopPerformers AS (
SELECT
SalesPersonID,
TotalSales
FROM SalesData
WHERE TotalSales > 10000
)
SELECT
sp.FirstName,
sp.LastName,
tp.TotalSales
FROM TopPerformers tp
JOIN SalesPersons sp ON tp.SalesPersonID = sp.SalesPersonID;
CTE with INSERT
use WideWorldImporters;
GO
WITH C AS(
SELECT SC.CustomerName
,CT.CustomerTransactionID
,TransactionAmount
FROM [Sales].[CustomerTransactions] CT
INNER JOIN [Sales].[Customers] SC ON CT.CustomerID = SC.CustomerID
)
SELECT CustomerName
,CustomerTransactionID into tmpTable2bDeleted
FROM C
ORDER BY CustomerTransactionID;
CTE with Update
use WideWorldImporters;
GO
WITH C AS(
SELECT SC.CustomerName
,CT.CustomerTransactionID
,TransactionAmount
,SC.CustomerID
FROM [Sales].[CustomerTransactions] CT
INNER JOIN [Sales].[Customers] SC ON CT.CustomerID = SC.CustomerID
)
UPDATE C SET CustomerName = CustomerName + '1'
where c.CustomerID = 1060
--------------------------
select CustomerName FROM [Sales].[Customers] where CustomerID = 1060
--Anand Mudaliyar1
CTE with Delete
WITH MissingAddress_CTE
AS (
SELECT *
FROM dbo.DimCustomerBak
WHERE AddressLine2 IS NULL
)
DELETE
FROM MissingAddress_CTE
CTE with Merge
WITH SourceTableCTE AS
(
SELECT * FROM SourceTable
)
MERGE
TargetTable AS target
USING SourceTableCTE AS source
ON (target.PKID = source.PKID)
WHEN MATCHED THEN
UPDATE SET target.ColumnA = source.ColumnA
WHEN NOT MATCHED THEN
INSERT (ColumnA) VALUES (Source.ColumnA);
