Home › SQL Server

SQL Server Common Table Expressions (CTEs)


A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A basic example of a Common Table Expression (CTE)

DECLARE @t TABLE (fname VARCHAR(15), lname VARCHAR(15))
INSERT INTO @t (fname,lname) SELECT 'jacob','sebastian'
INSERT INTO @t (fname,lname) SELECT 'bob','torres'
INSERT INTO @t (fname,lname) SELECT 'smith','jones'

-- Old approach 
SELECT * 
FROM (
    SELECT fname + ' ' + lname AS name
    FROM @t
) a
WHERE name = 'jacob sebastian'

-- Using a CTE
;WITH cte AS (
    SELECT fname + ' ' + lname AS name
    FROM @t
)
SELECT * FROM cte
WHERE name = 'jacob sebastian'

An example using multiple Common Table Expressions  (CTE)

DECLARE @t TABLE (fname VARCHAR(15), lname VARCHAR(15))
INSERT INTO @t (fname,lname) SELECT 'jacob','sebastian'
INSERT INTO @t (fname,lname) SELECT 'bob','torres'
INSERT INTO @t (fname,lname) SELECT 'smith','jones'

;WITH cte AS (
    SELECT 
        fname, 
        lname,
        ROW_NUMBER() OVER(ORDER BY fname) AS Sr
    FROM @t
), cte2 AS (
    SELECT 
        fname,
        lname,
        Sr
    FROM cte 
    WHERE Sr < 3
)
SELECT * FROM cte2
/*
fname           lname           Sr
--------------- --------------- --------------------
bob             torres          1
jacob           sebastian       2
*/

Share |
 Cant find the page you are looking for?
 Help us to improve by adding the content that you are looking for.
 Leave a feedback
 We look forward to hear your comments and feedback.
  • 1436 reads