Introduction to Recursive CTEs

Day 7 of Common Table Expression Month (June) at SteveStedman.com, today we will be taking a look at the introduction to recursive CTEs.

These queries will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.

The recursive feature of CTEs is perhaps one of the most powerful things you can do with a Common Table Expression

What is Recursion

Recursion is a concept in programming where a function calls itself. Many of the typical C programming interview questions around data structures like trees, linked lists, and other structures often times use recursion. For instance in the following T-SQL example, the scalar function Fibonacci calculates the Fibonacci sequence using recursion, by calling itself.  This is accomplished by calling the function name inside of the body of the function.


CREATE FUNCTION dbo.Fibonacci (@Num integer, @prev integer, @next integer)
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @returnValue as VARCHAR (4000) = cast(@prev as varchar(4000));
IF (@Num > 0)
BEGIN
IF (LEN(@returnValue) > 0)
BEGIN
SET @returnValue = @returnValue + ',';
END
SET @returnValue = @returnValue + dbo.Fibonacci(@Num - 1, @next, @next + @prev) ;
END

RETURN @returnValue;
END
GO

To call the function you simply include in a select statement, with the first parameter being the number of Fibonacci numbers to calculate, and the second and third parameters are always 0 and 1. The second and third parameters are set to 0 and 1 to prime the recursive function, and are used internally to pass the recursive call the current and previous values.

select dbo.Fibonacci(10, 0, 1);

Which produces the following output using SSMS:
FIbonacci_Scalar

Recursion With a CTE

Doing recursion as shown above with a function could be considered the brute force way of doing it. The common table expression way of doing recursion is much more elegant, and you can use it even if you don’t have the database permissions needed to create stored procs or functions.

Here is an example of a recursive CTE that queries a heirarchy of store departments:


use [cte_demo];
-- Recursive CTE
;WITH DepartmentCTE(id, Department, Parent, Level) AS
( SELECT id, department, parent, 0 as Level
 FROM Departments
 WHERE parent is NULL
 UNION ALL -- and now for the recursive part
 SELECT d.id, d.department, d.parent,
 DepartmentCTE.Level + 1 as Level
 FROM Departments d
 INNER JOIN DepartmentCTE
 ON DepartmentCTE.id = d.parent)
SELECT *
 FROM DepartmentCTE
 ORDER BY Parent;

The first time I looked at a recursive CTE, I was just confused by the syntax. Lets walk through the different parts and it may make more sense, to start with I will add some white space to the query to split it up a bit:

RecursiveCTE1

Anchor Query

The anchor query is the part of the CTE that starts the recursion. This is the part of the query that is run first that the recursive part will build from.

RecursiveCTE2

Recursive Query

The UNION ALL keyword is used to separate the anchor query from the recursive part.

RecursiveCTE3

After the UNION ALL comes the Recursive Query

RecursiveCTE4

What makes the recursive part of the CTE query recursive is the way that it references itself

Executing the Recursive CTE

Once the recursive CTE has been written, it can be executed just like any other CTE, SELECT everything FROM the CTE name, in this case we are also using ORDER BY to sort the results.

RecursiveCTE5

With the CTE complete, lets take a look at the results.

RecursiveCTE6

Now lets break the results up into what was generated by the anchor and what was generated by the recursive query.

RecursiveCTE7

Whats Next

Now that you understand recursive CTE’s we can get into some really interesting recursive options over the rest of CTE month at SteveStedman.com. Stick around something new most every day.

Related Links:

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions - Joes 2 Pros® - A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

T-SQL: A Simple Example Using a Cursor

In SQL Server the cursor is a tool that is used to iterate over a result set, or to loop through each row of a result set one row at a time. It may not be the best way to work with a set of data, but if you need to loop row by agonizing row (RBAR) in a T-SQL script then a cursor is one way of doing it.

Note: If you are new to SQL Server and come from an Oracle background, you should know that cursors on SQL Server are different from those on Oracle.

Before creating the cursor, we will just start with a simple query that will end up being used in the cursor.


USE AdventureWorks2008;
GO

SELECT BusinessEntityID, Name
 FROM Sales.Store;

Which looks something like this:

SimpleCursor1

Now to convert it to a cursor, instead of just a select statement.

Step 1: Declare variables to hold the output from the cursor.

</p>
DECLARE @BusinessEntityID as INT;
DECLARE @BusinessName as NVARCHAR(50);

Step 2: Declare the cursor object;


DECLARE @BusinessCursor as CURSOR;

Step 3: Assign the query to the cursor.


SET @BusinessCursor = CURSOR FOR
SELECT BusinessEntityID, Name
 FROM Sales.Store;

Step 4: Open the cursor.


OPEN @BusinessCursor;

Step 5: Fetch the first row.


FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;

Step 5: Loop until there are no more results.  In the loop print out the ID and the name from the result set and fetch the net row.


WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT cast(@BusinessEntityID as VARCHAR (50)) + ' ' + @BusinessName;
 FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;
END

Step 6: Close the cursor.


CLOSE @BusinessCursor;

Step 7: Deallocate the cursor to free up any memory or open result sets.


DEALLOCATE @BusinessCursor;

Now putting it all together:


DECLARE @BusinessEntityID as INT;
DECLARE @BusinessName as NVARCHAR(50);

DECLARE @BusinessCursor as CURSOR;

SET @BusinessCursor = CURSOR FOR
SELECT BusinessEntityID, Name
 FROM Sales.Store;

OPEN @BusinessCursor;
FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;

WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT cast(@BusinessEntityID as VARCHAR (50)) + ' ' + @BusinessName;
 FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;
END

CLOSE @BusinessCursor;
DEALLOCATE @BusinessCursor;

SimpleCursor2

This should give you a quick overview of how to quickly build and use a cursor on Microsoft SQL Server. The example shown was run on SQL Server 2008, and works the same on SQL Server 2005 or 2012.

Enjoy!

Related Links

 


 

Recursive Scalar Function in T-SQL

In my Common Table Expressions presentation the topic of recursion often comes up, but for scalar functions in T-SQL, it might not be as common.
This article has been written to show how a scalar function in SQL Server can call itself, thus being considered recursive.

What is the Fibonacci Sequence

The Fibonacci Sequence is a classic example that is used to demonstrate recursion.

By definition, the first two numbers in the Fibonacci sequence are 0 and 1, and each subsequent number is the sum of the previous two.

For instance, the following are Fibonacci Numbers:
0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, 233, 377, 610, 987, 1597, 2584, 4181, 6765, 10946, 17711, 28657, 46368, 75025, 121393, 196418, 317811, 514229, 832040, 1346269, 2178309, 3524578, 5702887, 9227465, 14930352, 24157817, 39088169, 63245986, 102334155, 165580141, 267914296, 433494437, 701408733 …

Fibonacci Sequence as a Computer Science Challenge

Often times calculating the Fibonacci Sequence is used as a computer science puzzle, or programming interview question to see if you understand recursion.  It is very simple to do in any programming language that supports recursion.

What is Recursion

Recursion is a programming concept where a function, procedure or section of code calls itself. For instance in the following T-SQL example, the scalar function Fibonacci calculates the Fibonacci sequence using recursion, by calling itself.  This is accomplished by calling the function name inside of the body of the function.


CREATE FUNCTION dbo.Fibonacci (@Num integer, @prev integer, @next integer)
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @returnValue as VARCHAR (4000) = cast(@prev as varchar(4000));
IF (@Num > 0)
BEGIN
IF (LEN(@returnValue) > 0)
BEGIN
SET @returnValue = @returnValue + ',';
END
SET @returnValue = @returnValue + dbo.Fibonacci(@Num - 1, @next, @next + @prev) ;
END

RETURN @returnValue;
END
GO

To call the function you simply include in a select statement, with the first parameter being the number of Fibonacci numbers to calculate, and the second and third parameters are always 0 and 1. The second and third parameters are set to 0 and 1 to prime the recursive function, and are used internally to pass the recursive call the current and previous values.

select dbo.Fibonacci(10, 0, 1);

Which produces the following output using SSMS:
FIbonacci_Scalar

For more details on the CTE version of Fibonaci take a look at my earlier post.

Enjoy!

T-SQL 2012 DATEFROMPARTS function

SQL Server 2012 adds a new function called DateFromParts.  This new function simplifies the creating of a DATE type in TSQL over the older ways of doing it. The information here has been extracted from my SQL Saturday presentation on Whats New in TSQL 2012.

Pre-SQL2012

First lets take a look at how you would do the equivalent to DateFromParts before SQL Server 2012:

DECLARE @TheDate AS DATE;
DECLARE @month AS INTEGER;
DECLARE @day AS INTEGER;
DECLARE @year AS INTEGER;

set @month = 4;
set @day = 12
set @year = 2013;
set @TheDate = cast(convert(datetime,convert(varchar(10),@year) + '-' +
 convert(varchar(10),@month) + '-' +
 convert(varchar(10),@day), 101) as date);

select @TheDate;

datefromparts1

You could certainly make it work prior to SQL Server 2012, but in SQL 2012 the new DATEFROMPARTS function is available to simplify your query.

SQL 2012 DateFromParts

Here is where it gets easier. Many of the new functions and other TSQL features in SQL Server 2012 appear to just simplify what you could do before. The way that DateFromParts works is like this:

DECLARE @TheDate AS DATE;
DECLARE @month AS INTEGER;
DECLARE @day AS INTEGER;
DECLARE @year AS INTEGER;

set @month = 4;
set @day = 12
set @year = 2013;
set @TheDate = DateFromParts(@year, @Month, @Day);

select @TheDate;

datefromparts2

Both the old way and the DateFromParts function produce the same results. The DateFromParts function does simplify the amount of typing, and casting and concatenation you need to do to create a date type. Keep in mind that the order of the parameters passed into DateFromParts is always Year, Month, Day independent of the specific country specific date formats you may be using.

A Function to Make DateFromParts Work Prior to SQL Server 2012

If you are not yet on SQL Server 2012 and you would like to start using the DateFromParts function, you could create your own user defined function for now, then just remove it when you get to SQL Server 2012.

If we try the function on SQL Server 2008 as shown below an error will be thrown.


DECLARE @TheDate AS DATE;
DECLARE @month AS INTEGER;
DECLARE @day AS INTEGER;
DECLARE @year AS INTEGER;

set @month = 4;
set @day = 12
set @year = 2013;

set @TheDate = DateFromParts(@year, @Month, @Day);

select @TheDate;

datefromparts2005error

To fix that error, all we need to do is create a new scalar valued function called DateFromParts to do the same thing as SQL Server 2012 does:


CREATE FUNCTION DateFromParts
(
@year AS INTEGER,
@month AS INTEGER,
@day AS INTEGER
)
RETURNS [DATE]
AS
BEGIN
RETURN cast(Convert(datetime,convert(varchar(10),@year)+'-'+
            convert(varchar(10),@month)+'-'+
            convert(varchar(10),@day), 101) as date);
END

Then we can run the original query with the function prefixed with dbo to get a similar result on SQL Server 2008 as we would on SQL Server 2012.


DECLARE @TheDate AS DATE;
DECLARE @month AS INTEGER;
DECLARE @day AS INTEGER;
DECLARE @year AS INTEGER;

set @month = 4;
set @day = 12
set @year = 2013;

set @TheDate = dbo.DateFromParts(@year, @Month, @Day);

select @TheDate;

datefromparts2008datefromparts

So with a simple function you can implement the same DateFromParts function on SQL Server 2008 and 2008R2 that is available on SQL Server 2012. Since the DATE type was not available in SQL 2005, you won’t be able to do this on SQL Server 2005.

Summary

Prior to SQL Server 2012 you could accomplish the same thing as DateFromParts with appropriate casting of variables. SQL Server 2012 introduces the DateFromParts scalar function to simplify things.  If you want to add your own DateFromParts function into SQL Server 2008, or 2008 R2 that can be done easily.

Enjoy!

Beta 3 is Now Available.

One big new feature in Beta 3 of the Database Health Reports, the historic monitoring.

The way that the historic monitoring works is that it continuously monitors your SQL Server database and logs the details around queries that are causing waits.

Here is an example, the following ugly block of TSQL updates a table with a few million rows in it, and that table has about 20 indexes on the primary key of a unique identifier.   Like I said ugly, ugly, ugly, but this query is quickly detected in the historic monitoring in Beta 3 of the Database Health Reports.


declare @counter as integer;
set @counter = 100;
WHILE @counter > 0
BEGIN
UPDATE dbo.NoLockDemo SET SomeData = NEWID()
set @counter = @counter - 1
WAITFOR DELAY '00:00:00.300'
END

Here is what I discovered using the Historic Waits reporting in Beta 3 of the Database Health Reports.

From here you can see that this query was run a lot between 4:00pm and 6:00pm, and that the wait that caused the most trouble was the WAITFOR wait type, but following that was the IO_COMPLETION, LOGBUFFER, PAGEIOLATCH_EX, followed by WRITELOG.

Once you activate the historic waits reporting, it may take a couple hours to a day to collect the data to be able to see trends like we see here.

The Beta 3 of the Database Health Reports gives you a quick graphical way to track down the queries that may be causing the most pain on your SQL Server.

Download Beta 3 of the Database Health Reports and give it a try.

TSQL Pivot Table

Here is a quick sample of how to implement a pivot table in TSQL for SQL Server. The example below creates a database called pivot, you probably already have your own database to work in. Then it creates a table called REVENUE and fills it in with department revenue for just over a 10 year period. Then you see a couple simple select statements followed by a SELECT statement that pivots the data.


USE [Master];
set statistics io off;

IF EXISTS(SELECT name FROM sys.databases WHERE name = 'pivot')
BEGIN
ALTER DATABASE [pivot] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [pivot];
END
CREATE DATABASE [pivot];
GO

USE [pivot];

-- Table to be used for demo
CREATE TABLE REVENUE
(
[DepartmentID] int,
[Revenue] int,
[Year] int
);

insert into REVENUE
values (1,10030,1998),(2,20000,1998),(3,40000,1998),
(1,20000,1999),(2,60000,1999),(3,50000,1999),
(1,40000,2000),(2,40000,2000),(3,60000,2000),
(1,30000,2001),(2,30000,2001),(3,70000,2001),
(1,90000,2002),(2,20000,2002),(3,80000,2002),
(1,10300,2003),(2,1000,2003), (3,90000,2003),
(1,10000,2004),(2,10000,2004),(3,10000,2004),
(1,20000,2005),(2,20000,2005),(3,20000,2005),
(1,40000,2006),(2,30000,2006),(3,30000,2006),
(1,70000,2007),(2,40000,2007),(3,40000,2007),
(1,50000,2008),(2,50000,2008),(3,50000,2008),
(1,20000,2009),(2,60000,2009),(3,60000,2009),
(1,30000,2010),(2,70000,2010),(3,70000,2010),
(1,80000,2011),(2,80000,2011),(3,80000,2011),
(1,10000,2012),(2,90000,2012),(3,90000,2012);
USE [pivot];
-- first lets look at the REVENUE table

SELECT *
FROM Revenue;
SELECT DepartmentId, Year, Revenue
FROM Revenue;

-- Simple Pivot
SELECT Year, [1], [2], [3]
FROM (SELECT Year, DepartmentId, Revenue FROM Revenue) as t
PIVOT
(
sum(Revenue)
FOR DepartmentId in ([1], [2], [3])
) as pivotTable;

Just to recap, here is the actual pivot code, and the output that it produces.

-- Simple Pivot
SELECT Year, [1], [2], [3]
FROM (SELECT Year, DepartmentId, Revenue FROM Revenue) as t
PIVOT
(
sum(Revenue)
FOR DepartmentId in ([1], [2], [3])
) as pivotTable;

I hope you find this useful as an example of working with PIVOT tables in SQL Server using the select statement in TSQL.