Recursive CTE’s

As part of my planning for the SQL Saturday Presentation in Vancouver I am creating an hour long presentation on Common Table Expressions.

The easiest way to do a recursive query in SQL server is to use a recursive CTE (Common Table Expression).

What is a Common Table Expression in SQL Server?

  • Similar to the ease of a temporary table without the performance problems of a temp table.
  • Temporary Named Result Set
  • Acts like a temporary view
  • Can be use to
    • Create a recursive query
    • Simplify a query by using a result set multiple times
    • Self JOIN a subquery without running the subquery twice

Here is a transact SQL sample based on the AdventureWorksLt database demonstrating a CTE for a recursive query.

WITH CategoryCTE(CategoryId, Name, Level) AS (
SELECT ProductCategoryId AS CatId,
Name, 0 as Level
FROM saleslt.ProductCategory
WHERE ParentProductCategoryId is NULL
UNION ALL -- and now for the recursive part 
SELECT c.ProductCategoryId AS CatId,
c.Name, ccte.CategoryId + 1 as Level
FROM saleslt.ProductCategory c
INNER JOIN CategoryCTE as ccte
ON ccte.CategoryId = c.ParentProductCategoryID
FROM CategoryCTE
ORDER BY Level, Name;


CTE’s and Recursive CTE’s were first introduced in SQL Server 2005, and are available in 2008 and 2008R2 with the same syntax.



For another example of CTE’s (non-recursive) take a look at the query behind the Backup Set SQL Server Health Report.

Another Session Submitted for SQL Saturday

I just submitted a second abstract for SQL Saturday titled Unleashing Common Table Expressions in SQL Server.

This is for the SQL Saturday Redmond and SQL Saturday Vancouver BC sessions.

I am hoping to get three sessions accepted for Vancouver.

Free – Duplicate Indexes Report – Updated

After creating and deploying the duplicate indexes report earlier today I discovered that although the report did exactly what it says, it didn’t do what I needed.

The earlier  duplicate indexes report only found indexes that were an exact match on the columns.  And that is useful to track down, it didn’t point out indexes that were similar, but had additional columns.

Given the following sample code CustomersA, and CustomersB are duplicate indexes of Customers1 even thought the columns don’t match exactly.  The new report will catch and display this type of matches.

CREATE TABLE [Customers] (
firstname VARCHAR (200),
middlename VARCHAR (200),
lastname VARCHAR (200),
age INT
CREATE CLUSTERED INDEX [CustomersId] ON [Customers] ([id] ASC);
CREATE INDEX [Customers1] ON [Customers] ([lastname], [firstname], [middlename]);
CREATE INDEX [Customers2] ON [Customers] ([lastname], [firstname], [middlename]);
CREATE INDEX [Customers3] ON [Customers] ([lastname], [firstname], [middlename]);
CREATE INDEX [CustomersA] ON [Customers] ([lastname]);
CREATE INDEX [CustomersB] ON [Customers] ([lastname], [firstname]);

As shown below the new report now shows the matching similar indexes.

This is part of my growing collection of free SSRS reports to analyze and assess your SQL Server Health.

Download and Enjoy.



Free – New Duplicate Indexes Report added

After yesterdays Long Running Queries Report, I have decided to add one more SQL Server Health report.

duplicate indexes report has been added to the Server Health reports. With this report you can track down time waste associated with duplicate indexes.

Here is a sample of the new free report.

This is part of my growing collection of free SSRS reports to analyze and assess your SQL Server Health.

Download and Enjoy.

Free – Long Running Queries Report Created

A long running queries report has been added to the Server Health reports. With this report you can track down time consuming queries that may be causing excessive load on your server.

Here is a sample of the new free report.

Download and Enjoy.

Abstracts submitted to SQL Saturday

I just submitted abstracts to SQL Saturday  #114 in Vancouver and #108 in Redmond.

The Redmond SQL Saturday appears to be the more popular of the two.  Who knows if my session will get accepted, but I am giving it a shot.


Using SSRS reports to analyze SQL Server health

Assessing the health of your SQL Server with Several SQL Server Report Server reports designed for DBA’s and database developers to diagnose, and locate common pitfalls of SQL Server.  This includes reports for Duplicate Indexes, Index Fragmentation, Big Indexes, Backup Status – backup log, Big One Time Use Queries, Queries Needing Parameters and more.