LA Code Camp Slides…

Ok, here they are.  If you attended my sessions you know they are more demo than slides so it is questionable how much value you can get from the decks themselves, but people asked and I promised so here they are.  I would like to do screen captures of these and hope to do that soon.  If and when I get that done I will post links on the blog.

Sessions:

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.

LA Code Camp Session Slides Coming Soon

I had a great time at the LA Code Camp last weekend.  I never got a chance to get up and play rock band, but I managed to have fun anyhow.

For those that attended my sessions and wanted the slide decks, I promise to try and post them up here this week.

Thanks, and I hope to see you at the next Code Camp!