Using a Recursive CTE to generate a contiguous sequence of values…

I am working on a chapter for Syngress’ 70-433 test prep guide, and talking about character sets.  In my research I wanted to write a query that would return the values 0 through 255 and the the character associated with each of those numbers.

Yeah if I had a table Like AdventureWorks.Person.Contact where there was a set of 256 contiguous values, I could highjack those values for my source numbers, but that seems much less elegant.

Here is what I came up with:

WITH numbers AS
(
  SELECT 0 AS Number
  UNION ALL
  SELECT Number + 1
  FROM Numbers
  WHERE Number + 1 <= 255
)
SELECT Number, CHAR(Number) AS Character FROM numbers
OPTION (MAXRECURSION 255);

Note the OPTION (MAXRECURSION 255) at the end.  Without that it will error after the first 100 rows (really its after the 100th recursion along this branch, but I digress) by default. But that option will cause it to error if I don’t stop the recursion myself at 255, hence the WHERE Number + 1 <= 255 where clause on the recursive query definition in the CTE.

Anyhow, if you run the query above you will get a result similar to the following

Number  Character
—— ———
…     …
63      ?
64      @
65      A
66      B
67      C
…     …
97      a
98      b
99      c
…     …
253     ý
254     þ
255     ÿ

 

Enjoy!

Using CTEs to Build Running Sums…

I am teaching a class today on optimizing queries and one of the topics in on refactoring cursors in to queries.  The book stated that Cumulative Totals is an example of where a cursor is required.  I beg to differ.  The following example uses two features of SQL Server to build a running sum with set based syntax.

I will explain the following code snippet below, but here it is:

USE AdventureWorks
WITH NumberedProducts (RowNum,ProductID,Name,ListPrice)
AS
(
SELECT
  ROW_NUMBER() OVER (ORDER BY ListPrice, Name) AS RowNum,
  ProductID, Name, ListPrice
FROM Production.Product
WHERE ListPrice > 0
),
CummulativeSums (RowNum, ProductID, Name, ListPrice, RunningSum)
AS
(
  SELECT TOP 1
    RowNum, ProductID, Name, ListPrice, ListPrice AS RunningSum
  FROM NumberedProducts
  ORDER BY ListPrice, Name
  UNION ALL
  SELECT
    This.RowNum, This.ProductID, This.Name, This.ListPrice, This.ListPrice + CS.RunningSum AS RunningSum
  FROM NumberedProducts AS This
  JOIN CummulativeSums AS CS
  ON This.RowNum = CS.RowNum + 1
)
SELECT * FROM CummulativeSums
OPTION (maxrecursion 503)

The two features I used are Common Table Expressions (or CTEs). and the ROW_NUMBER() function.

I used CTEs to facilitate the recursive query needed to iterate through records, collecting values and accumulating totals on a row by row basis.  I am using the Product.Product table in the SQL 2005 AdventureWorks sample database as my data set.  I need a way to first order the products.  A Running total assumes some order to the data.  I used ListPrice, and where ListPrice was the same, then [Name].

For the recursive part to work, I needed a way to correlate a product to it’s previous product.  There is an automatically generated ProductID column, but the ProductIDs are in no way correlated to the ListPrice, [Name] sort order so that didn’t make sense.  Plus there isn’t any quaranatee that they are sequential and that would make joining to the "next" product difficult. 

To solve my problem I used the ROW_NUMBER() function to number the rows in an original set of Product Data that had been ordered by ListPrice, [Name].  I used a CTE to define that set. 

WITH NumberedProducts (RowNum,ProductID,Name,ListPrice)
AS
(
SELECT
  ROW_NUMBER() OVER (ORDER BY ListPrice, Name) AS RowNum,
  ProductID, Name, ListPrice
FROM Production.Product
WHERE ListPrice > 0
),

Next, I used a second CTE to do the recursion.  The anchor query retrieves the top 1 product ordered by ListPrice, [Name].  As this is the first row in the result set the "RunningSum" is the same as the current records ListPrice:

SELECT TOP 1
    RowNum, ProductID, Name, ListPrice, ListPrice AS RunningSum
FROM NumberedProducts
ORDER BY ListPrice, Name

The recursive part joins back to the CTE on the RowNum being one greater that it’s "parent" from the anchor query.  The running sum is built by taking the current row’s ListPrice and adding it to the "RunningSum" value of it’s parent:

SELECT
    This.RowNum, This.ProductID, This.Name, This.ListPrice,
    This.ListPrice + CS.RunningSum AS RunningSum
FROM NumberedProducts AS This
JOIN CummulativeSums AS CS
ON This.RowNum = CS.RowNum + 1

There is one last thing that can cause a problem with this; maxrecursion.  SQL Server defaults to only allowing 100 recursion iterations in a CTE by default.  We can override that however with an option on the final query.

In the AdventureWorks.Product.Product table there are 504 products by default.  That means after the anchor row, we would have to recurse 503 times to get the remaining products.  To demonstrate this I set the maxrecursion option to 503.   You would likely want to set a higher value to allow additional rows to be added in the future, and have it not break your query.

Unfortunately, it doesn’t appear that there is an easy way to set the value for maxrecursion dynamically (like from a variable or subquery).  You would have to resort to dynamic sql execution, but you can just set it to a large value you know will exceed your desired recursion count. 

Anyhow, I am sure other’s have arrived at this solution already, but for me it was a new way of thinking of a common problem.