MSSQL Common Table Expression (CTE)

What is Common Table Expression?

• Can be thought as a temporally result set that is defined within the SQL execution scope.
• Usage: -

1. Create Recursive Query
2. Substitute for a view
3. Enable Grouping by column that is derived from a scalar sub select.
4. Referencing the resulting table multiple times.
5. Available since SQL Server 2005

Syntax: -

WITH [(coloumn name [, column name...n])]
AS
(
/* anchor member definition */
UNION ALL | UNION | EXCEPT | INTERCECT
/* recursion member definition*/
)



Statement to use with CTE include INSERT, UPDATE, DELETE, CREATE VIEW.

CTE can be used without recursion member and that will serve very well as the temporally result set for further joining.

Example: -

/*************** BEGIN OF QUERY **********************/
USE NRule
GO

WITH DocumentReport
AS
(
SELECT a.id, a.[Description], a.Name AS [DocumentName], c.Name AS [AttributeName], d.value
FROM Documents AS a
INNER JOIN DocumentMetaAttributePathIndex AS b
ON a.id = b.documentId
INNER JOIN MetaAttributes AS c
ON b.attributeId = c.id
INNER JOIN AttributeValues AS d
ON b.valueId = d.id
)
SELECT *
FROM DocumentReport ORDER BY id
GO
/*************** END OF QUERY **********************/

CTE would be best use to query of outcome that is expanding dynamically. Typical example is to trace the tree or graph data structure represented under adjancent list table design pattern within relational model of a database.

Example: -

/*************** BEGIN OF QUERY **********************/
USE NRule
GO

DECLARE @documentId bigint
DECLARE @ancestorId bigint
SET @documentId = 1
SET @ancestorId = NULL;
WITH AttributeHierarchyReport(AncestorId, AttributeId, DocumentId, ValueId, LEVEL)
AS
(
-- Anchor member definition
SELECT meta.AncestorId, meta.attributeId, meta.[DocumentId], meta.[ValueId], 0 AS LEVEL
FROM dbo.DocumentMetaAttributePathIndex AS meta
WHERE (@ancestorId is NULL or meta.AncestorId = @ancestorId) AND
(@documentId is NULL or meta.DocumentId = @documentId)
UNION ALL
-- Recursive member definition
SELECT meta.ancestorId, meta.attributeId, meta.[documentId], meta.[valueId], LEVEL + 1
FROM dbo.DocumentMetaAttributePathIndex AS meta
INNER JOIN AttributeHierarchyReport rpt ON meta.ancestorId = rpt.AttributeId
WHERE (@ancestorId is NULL or meta.AncestorId = @ancestorId) AND
(@documentId is NULL or meta.DocumentId = @documentId)
)
SELECT AncestorId, AttributeId, DocumentId, ValueId, Level
FROM AttributeHierarchyReport ORDER BY Level;
GO
/*************** END OF QUERY **********************/

However, watch out for the data redundancy that is appearing in the query result set. Redundant query result set within the recursive CTE proven to be a killer to your database application using it. It grows the result set sometimes exponentially until it kills the application by running out of memory.

Tweak is needed to CTE when redundant row sets happened and if you care about your application, never live with redundant row sets by either further tuning your CTE or improve your Database/Table design to help better/efficient use with your CTE.

Example: -

/*************** BEGIN OF QUERY **********************/
/* this version is using additional column ParentDescendantId to enforce root node validation at anchor member */
USE NRule
GO

DECLARE @documentId bigint
DECLARE @ancestorId bigint
/* @documentId = NULL would mean to get all attributes for all documents regardlessly */
/* alternatively we can use the @documentId to narrow down to a specific document attribute tree for retrieval */
SET @documentId = 1
/* @ancestorId = NULL would mean to get all attributes regardlessly */
/* alternatively we can use the ancestorId to narrow down the attribute tree for retrieval */
SET @ancestorId = NULL;
WITH AttributeHierarchyReport(AncestorId, DescendantId, DocumentId, ValueId, ParentDescendantId, LEVEL)
AS
(
-- Anchor member definition
SELECT meta.AncestorId, meta.attributeId, meta.[DocumentId], meta.[ValueId], parent.attributeId, 1 AS LEVEL
FROM dbo.DocumentMetaAttributePathIndex AS meta
LEFT JOIN dbo.DocumentMetaAttributePathIndex AS parent ON meta.AncestorId = parent.attributeId
WHERE (@ancestorId is NULL or meta.AncestorId = @ancestorId) AND /* Ensure the ancestor id is matched */
    (@documentId is NULL or meta.DocumentId = @documentId) AND /* Ensure the document id is matched */
    (parent.attributeId IS NULL) /* Ensure there is no ancestor, Root node only */
UNION ALL
-- Recursive member definition
SELECT meta.ancestorId, meta.attributeId, meta.[documentId], meta.[valueId], parent.DescendantId, LEVEL + 1
FROM dbo.DocumentMetaAttributePathIndex AS meta
INNER JOIN AttributeHierarchyReport parent ON meta.ancestorId = parent.DescendantId /* Ensure all is child is recursively joined */
)
SELECT AncestorId, DescendantId, DocumentId, ValueId, ParentDescendantId, Level
FROM AttributeHierarchyReport;
GO
/*************** END OF QUERY **********************/