This post is part of the QUERY series.

Common table expressions or CTE for short are like temporal tables that are kept in RAM in order to access them very fast. But to not saturate the RAM they are so short lived that they can be only used in the next sentence that generated the CTE.

The sintax of a CTE is simple, they are declared without data types because they get the type from the table that is used in the definition. In the next example we can see a CTE in combination with ROW_NUMBER, both used to get a page of records from a table ordered and filtered.

ALTER PROCEDURE [dbo].[DI_EXP_ORG_QUERY]
@pPK_Org int, @pSortColumn NVARCHAR(50) = null,
@pPageSize INT = 10,
@pPageNum INT = 1
AS
BEGIN
SET NOCOUNT ON;
--CTE declaration
WITH PagingCTE
(--Columns are only defined by name
Row_ID,
PK_Org,
Nom,
Descripcio)
AS
(--The query is enclosed in parenthesis
SELECT
ROW_NUMBER()--Here is used the Row_Number function, and the ORDER BY
OVER(ORDER BY
CASE WHEN @pSortColumn = 'PK_OrgASC' THEN DI_EXP_ORG.PK_Org END ASC,
CASE WHEN @pSortColumn = 'PK_OrgDESC' THEN DI_EXP_ORG.PK_Org END DESC,
CASE WHEN @pSortColumn = 'NomASC' THEN DI_EXP_ORG.Nom END ASC,
CASE WHEN @pSortColumn = 'NomDESC' THEN DI_EXP_ORG.Nom END DESC,
CASE WHEN @pSortColumn = 'DescripcioASC' THEN DI_EXP_ORG.Descripcio END ASC,
CASE WHEN @pSortColumn = 'DescripcioDESC' THEN DI_EXP_ORG.Descripcio END DESC
)
AS [Row_ID],
DI_EXP_ORG.PK_Org,
DI_EXP_ORG.Nom,
DI_EXP_ORG.Descripcio
 FROM DI_EXP_ORG WHERE ((@pPK_Org IS NULL) OR (DI_EXP_ORG.PK_Org=@pPK_Org))
)--Here ends the CTE, ready to use in the following QUERY
SELECT
(SELECT COUNT(*) FROM PagingCTE) AS NumRegs,
PK_Org,
Nom,
Descripcio FROM PagingCTE
--In the CTE WHERE part records are filtered.
--In this WHERE a page is selected, calculated from parameters
WHERE Row_ID >= (@pPageSize * @pPageNum) - (@pPageSize -1) AND Row_ID <= @pPageSize * @pPageNum
 ORDER BY Row_ID
END

As you can see the QUERY is pretty straight forward, there are no JOIN, the number of records is low and the filter parameters are just a few. So we can use CTE - ROW_NUMBER to get an excelent performance.