Editing MSSQL Cookbook (section)

==Common Table Expressions==

*MSDN: [http://msdn.microsoft.com/en-us/library/ms175972.aspx WITH common_table_expression (T-SQL)]
*MSDN: [http://msdn.microsoft.com/en-us/library/ms190766.aspx Using Common Table Expressions]<br />The comments on this page contain some useful information.

Before SQL Server 2005 I would create a temp table in order to organize a recordset to return a single page of results. CTE's are more efficient and easier to understand when editing the T-SQL.


Before:

<syntaxhighlight lang="sql">
DECLARE @tmpTable TABLE (rowNum IDENTITY(1,1), recordID INT)

INSERT INTO @tmpTable
SELECT recordID
FROM myTable WITH(NOLOCK)
WHERE [mySearchConditions]
ORDER BY [mySortExpression]
</syntaxhighlight>


After:

<syntaxhighlight lang="sql">
DECLARE @firstRow int, @lastRow int

/* page controls */
SET @firstRow = ((@page-1)*@pageLen)+1
SET @lastRow = @page*@pageLen

WITH sortedResultsCTE (recordID, title, description) AS
(
    SELECT recordID, title, description,
    ROW_NUMBER() OVER(ORDER BY [mySortExpression]) AS rowNumber
    FROM myTable WITH(NOLOCK)
    WHERE [mySearchConditions]
)

SELECT recordID, title, description
FROM sortedResultsCTE
WHERE rowNumber BETWEEN @firstRow AND @lastRow
</syntaxhighlight>


The [http://msdn.microsoft.com/en-us/library/ms186734.aspx <code>ROW_NUMBER()</code> function] returns just that, the sequential value of the row in the resultset.

One great feature of CTE's is that you can use the limited recordsets in subsequent statements, you can refer to the subsets multiple times within the statement, you can use subsets to create other subsets and/or join to other tables.

<syntaxhighlight lang="sql">
WITH FirstSetCTE (recordID) AS
(
    SELECT recordID FROM ThisTableHere WITH(NOLOCK) WHERE SomeCondition = 'Some Value'
)
,
SecondSetCTE (recordID) AS
(
    SELECT tot.recordID
    FROM ThisOtherTable tot WITH(NOLOCK)
    INNER JOIN FirstSet fs ON tot.recordID = fs.recordID
    WHERE SomeOtherCondition = 'Some Other Value'
)

SELECT t1.recordID, t1.name
FROM SomeTable t1 WITH(NOLOCK)
INNER JOIN SecondSetCTE s2 ON t1.recordID = s2.recordID
</syntaxhighlight>