Adam Machanic : T-SQL

What Happened Today? DATE and Date Ranges Over DATETIME

Adam Machanic — Tue, 20 Oct 2009 18:21:00 GMT

A few days ago Aaron posted yet another fantastic entry in his Bad Habits series, this one discussing mishandling of date ranges in queries . This is a topic near and dear to me, having had to clean up a lot of poorly thought out code in the past few...(read more)

Grouped String Concatenation: ... The Winner Is ...

Adam Machanic — Sun, 31 May 2009 20:07:00 GMT

After weeks of putting it off, I finally found the time and spent the last day and a half judging the Grouped String Concatenation Challenge. I would like to congratulate the winner, Peter Larsson, who submitted a great query and walks away with a shiny new MSDN Premium subscription.

For those who are interested, following is a breakdown of the judging process, along with some commentary:

Submission Process

To begin with, e-mails. As I mentioned in the first post, I ignored all e-mails that didn't follow the directions. Luckily this was only a few submissions. I felt it rather odd that people would spend a not insignificant amount of time working up a solution only to not bother to read the guidelines thoroughly. But that's human nature, I suppose.

Round 1

Once I collected all of the queries that followed the e-mail rules (all of which are included in the attached ZIP file), I began testing against an expanded version of AdventureWorks (the script for that is also included). I decided to eliminate any queries that did not produce the correct output data based on my sample set, or which took longer than 30 seconds to complete. The majority of queries did complete in a reasonable amount of time, and many were eliminated because the output simply wasn't correct. The biggest issue was ordering of the elements in the comma-delimited sets. I also deducted points from one person's entry because of invalid column names, but I decided to let the entry ride to the next round.

An important side note is that I created this competition with the sole intention of discovering new and different ways to do grouped string concatenation, and my hope was that someone would come up with a clever, fast solution. Unfortunately, that didn't happen, and every submission that used any technique except FOR XML PATH was eliminated in the first round of testing. I received some extremely creative solutions from a couple of people and I would like to mention them here:

Alejandro Mesa's submissions made use of various XQuery techniques, and are very interesting to look at, although fairly slow
Dean Cochrane's submission used an interesting idea of doing a MAX(CASE ...) pivot for the lists. Alas, the product names lists were not correct, so the submission didn't make it to the stress testing phase
Scott Coleman tried a similar technique, actually using the PIVOT keyword. Unfortunately, this ran for over 200 seconds, so it was eliminated

Also interesting to note is that a few people tried recursive CTE solutions. These were all cancelled at the 300 second mark. Recursive CTEs, as mentioned before on here on SQLblog, simply do not scale in their current implementation.

Round 2

After tabulating the Round 1 results I was left with 18 queries, and some obvious contenders. I ran each query through a SQLQueryStress session with 10 threads running 5 iterations each. In this phase the queries were separated into fairly distinct groups: Those that ran for around 5 minutes, those that ran for around 7-8 minutes, and those that ran longer. These groups were based, not surprisingly, on how much attention was paid by the query writers to the little details. For example, Peter Larsson's winning query cut down on logical reads dramatically by doing some of the grouping in a derived table, rather than in the outermost query as some of the other submissions did.

Lesson learned: When doing aggregations, especially when joining a lot of tables, think about what you're really aggregating, and do the aggregation as early as possible. For example, if you need to aggregate sales per customer and get customer names, do the aggregation of the sales numbers first, then join out to get the customer names. Otherwise the query processor is forced to do more work than it has to do, and your query won't be as fast. Peter and a few other contestants understood this distinction and wrote queries that were much faster as a result.

Round 3

Round 2 eliminated 4 queries, leaving me with 14 to judge based on query style. In order to judge consistently, I came up with 10 factors. A query was allotted 500 points to start, and failure to meet each factor resulted in a 50 point penalty. These factors were:

Consistent Indentation

Does the query use the same rules for indentation in all parts? This is huge for readability and helps people understand where each section of the query starts and ends.

Consistent Capitalization

Does the query use the same rules for capitalization throughout? For example, keywords should be either all capitalized, or all lowercase.

Capitalize Keywords

I like to see keywords capitalized.

Use AS for Alias Names

AS is optional, and I've left it out in many queries I've written. But the more of other peoples' code I read, the more I realize that it really does help on the readability front. Use it. Always.

Follow Capitalization of Base Tables/Columns

If the base table is called OrderHeader, I want to see it used as OrderHeader when referenced in your query, rather than orderheader. A trainer I know found this out the hard way, when he reinstalled SQL Server on his laptop shortly before a training session, and used a case-sensitive collation rather than his previously-installed case-insensitive collation. He had been careless in adhering to capitalization for his training materials, and discovered the issue in front of the class. Oops.

Long Horizontal Lists

I don't like horizontal scrolling, and I find long lists difficult to read.

Consistent Vertical Lists

Put either a comma after each element or before each element, not both. Indent your lists the same way throughout. If you indent some items below the SELECT, don't put other items on the same line as the SELECT (or GROUP BY, or ORDER BY, etc)

Alignment of Delimiters

I follow a .NET-inspired style where I put delimiters on their own lines, and line them up vertically. This gives my code what I feel is an airy, easy-to-read feel. When reading others' code I look for some kind of alignment. Failure to align delimiters makes it very difficult to understand, again, where one section begins and another ends. By the way, common delimiters for this challenge included both parens and CASE...END.

Comments

Does the query have comments? Are the comments useful in understanding the logic?

Aesthetics

This is perhaps the most subjective. My general feeling on how I enjoyed reading the code.

All in all, the queries were pretty good. I would like to call out Rick Halliday, who had the highest score in this round with some very well formatted and highly readable code.

Round 4

After judging Round 3 I tallied all of the scores and was left with a tie for top 3:

Rick Halliday
Leonid Koyfman
Peter Larsson's query #4

All three of these queries were well thought out, but only one could win, so I took another pass through each. Rick's query, though extremely well written and readable, was eliminetad first due to the fact that it performed worse than the other two. This left Leonid and Peter. It was a tough choice, but I had to give the prize to Peter for taking the time to really think through the problem and figure out exactly how best to do the aggregations. Leonid was a very, very close second, and I really wish I had a consolation prize for him.

The End

And that's that. Thank you to everyone who participated in the challenge. I hope it was as much a learning experience for you as it was for me. Congratulations again to Peter. All of the materials are attached in the ZIP file; please let me know if you have any questions, comments, etc.

The Grouped String Concatenation Challenge is Closed

Adam Machanic — Mon, 16 Mar 2009 00:22:00 GMT

Just over two weeks ago I posted the Grouped String Concatenation Challenge. A more difficult challenge than the last one I posted, partially in hopes that not as many people would submit solutions and it would be easier for me to judge. But alas, my readers are obviously really into this stuff, and I received submissions from 39 people, some of whom sent me up to four different solutions. I didn't expect such an amazing response, and now it's up to me to sort through all of these e-mails and declare a winner--no easy task.

The submissions I've looked at so far have been of very high quality, and most of them use FOR XML PATH('') in some way. A few people experimented with other techniques--which is exactly what I was hoping for--and I'll be especially interested in seeing how their solutions stack up. I also spent a lot of time this last two weeks working on alternative solutions, and most of my attempts didn't scale well. I'm hoping that some reader managed to come up with a trick I haven't thought of yet.

The judging will be done as follows:

I'll run each submission and test it for correctness against my own benchmark query (the results of which were attached to the original post). If any of the rows don't match exactly, that submission will be disqualified.
Next I will run each query twice, and will eliminate any query that doesn't run in less than four seconds on my notebook (the fastest ones I've already seen run in two).
The remaining queries will be run through SQLQueryStress sessions on a version of AdventureWorks modified to have twice as many customers and eight times as many orders. Queries in this stage will be scored on a bell curve where the top 15% will receive a score of 10, the upper-middle 35% a score of 6, the lower-middle 35% a score of 2, and the rest a score of 0. The 0s will be eliminated.
The remaining queries will then be evaluated for readability. This is purely subjective and since I'm the judge my idea of what constitutes readability wins. So I hope you've been reading my blog for a while and have absorbed some of my best practices in this area! Queries will be scored based on the same curve as before.
At this stage the queries with the highest combined scores will be weighted based on innovation and the ability to apply their techniques to a general pattern, in order to find the number one query.

Thanks again to everyone who submitted! I'm looking forward to judging the submissions. Watch this space for a comprehensive breakdown of the results, to be posted sometime in the next couple of weeks.

T-SQL Challenge: Grouped String Concatenation

Adam Machanic — Fri, 27 Feb 2009 18:22:00 GMT

It's been quite a while since the LIKE vs ? Puzzle, and I feel like it's time for another one. Response was overwhelming last time, and I'm back with a much tougher puzzle and a much bigger prize. So get ready, because I'm going to really make you stretch your brain and your T-SQL skills for this one.

But first, a bit of background. String concatenation is something I've talked about on this blog before, and it is an incredibly popular topic; my post on the subject has gotten more hits than any other single post I've ever done. TechNet blogger Ward Pond also understands the popularity of the topic, having discussed concatenation at least five times on his blog. And as illustrated in this excellent article by Anith Sen, there are a number of methods available to the intrepid SQL Server explorer; the techniques Ward and I show are just the tip of the iceberg.

And while all of that is great, there is a deep and troublesome hidden problem: Nowhere in my post, nor Anith's article, nor Ward's series, will you find a technique that completely solves the concatenation problem. The FOR XML PATH('') method--by far the most popular SQL Server 2005 "trick" I see repeated over and over in these articles and on forums--is a bit limiting. It doesn't help when we need to "group" our string concatenation, i.e. concatenate strings for a number of key values and return multiple rows in the result. And FOR XML PATH('') also leaves us a bit flat if we need to return aggregated data with the concatenated strings or--even more interesting--concatenate multiple different columns in the same output.

Sure, there are ways to solve this problem, but they usually require temp tables, user-defined functions (CLR or otherwise), tables of numbers, cursors, or other adjunct objects. And while some of these solutions are certainly workable they lack the beauty of a single, self-contained solution. In the interest of solving this problem I recently created a challenge for myself: Figure out how to do "grouped" concatenation using nothing more than a single T-SQL statement. No temp tables. No UDFs. No procedural logic.

But rather than do the work all alone and simply post my solution, I've decided to invite you to join me in the quest. Are you up for it?

Here are the rules of the game:

You are to create a single T-SQL statement that concatenates values from the AdventureWorks (note: not AdventureWorks2008) Sales.SalesOrderHeader, Sales.SalesOrderDetail, Production.Product, and Person.Contact tables.
The output should have the following columns, in the following order, and no other columns:

CustomerID: The customer's CustomerID (this is the unique key in the output)
FirstName: The customer's first name
LastName: The customer's last name
OrderCount: Number of orders placed by the customer
TotalDollarAmount: Total dollar amount of all orders placed by the customer (based on the SalesOrderHeader.SubTotal column)
TotalProductQuantity: Total number of items purchased by the customer in all orders (based on SalesOrderDetail.OrderQty)
OrderNumbers: Comma-delimited list containing the order numbers (SalesOrderHeader.SalesOrderNumber) for each of the orders placed by the customer

The numbers within the list should be alphabetized. The list should have neither leading nor trailing commas, and each element in the list should be separated by a single comma with no spaces or other white space beforeor after the comma

ProductNames: Comma-delimited list containing the unique names of all products ordered by the customer in all orders

The names within the list should be alphabetized. The list should have neither leading nor trailing commas, and each element in the list should be separated by a single comma with no spaces or other white space beforeor after the comma

No tables--permanent, temporary, or variable--are to be created. No dynamic SQL is to be used. No user-defined functions, views, or stored procedures are allowed. No variables may be declared. To put it simply, no permanent or temporary objects of any kind, at any scope, are to be explicitly created. No procedural statements of any kind--cursors or control-of-flow--are allowed. This must be a standalone statement in the AdventureWorks database; nothing more and nothing less.
Aside from the previous stipulation, any SQL Server 2005 or 2008 feature is fair game. Documented or not, if it ships with the product and can be used in a standalone T-SQL statement, you can use it. If you do use a version-specific feature, please let me know (especially if it's a SQL Server 2005 feature that's gone in 2008). Bonus points may be given for solutions that work on either version, but I'll make that decision after reviewing the submissions.
Entries will be judged first and foremost on correctness, then on a combination of performance, readability, and ability to apply your technique as a general pattern.

Just to be absolutely clear: If your submission violates the rules, outputs the wrong data, or does not precisely follow the output guidelines listed above, it will be ignored. Last time I spent a lot of energy going back and forth with people helping them get there, and I just don't have the bandwidth to do that again. So double-check your submission before you send it to me.
Make your submission readable or you will lose credit even if performance is amazing. I don't appreciate looking at a mess, and it's good for your career to learn how to write code that others can maintain. Hint: Learn to indent your code properly; lack of indentation is the biggest mistake I see people make with regard to readability.
Take your time. You have two weeks to work on this. I've already come up with three different solutions that have vastly different performance characteristics. Perhaps your first shot isn't the best choice?

The entry deadline is March 16, 2009, midnight GMT. No exceptions.
Submissions should be e-mailed to me, using a .SQL file attachment.

Do not paste your solution into the body of your e-mail
The subject of your e-mail should be "Grouped String Challenge Submission".
E-mail your submissions to [my first name] [at] [this site].
Again, be careful and don't violate these guidelines or your submission will be ignored.

... What's that? You want a prize? Fine, fine ...

The prize, for the best submission, is a full MSDN subscription, valued at around $10,000. How's that for inspiration?

... and that's that! One final note: Please do not post your solution in the comments here or on another blog, before the deadline has been reached! Last time several people did that and it was incredibly annoying both for me and those contestants trying to think through the problem. You won't be doing yourself any favors by trying to mess up the competition.

Once the deadline is reached I will test all of the submissions, tabulate the results, and post back here in early April. I promise, I won't let the thing stagnate for months like I did last time.

Have fun with it, be creative, and feel free to post comments here with any questions you might have. I found this to be a fairly difficult but very interesting exercise and I hope you agree. Enjoy, and I'm looking forward to seeing what you can do!

Who's On First? Solving the Top per Group Problem (Part 1: Technique)

Adam Machanic — Fri, 08 Feb 2008 23:09:00 GMT

Relative comparison is a simple matter of human nature. From early childhood we compare and contrast what we see in the world around us, building a means by which to rate what we experience. And as it turns out, this desire to discover top and bottom, rightmost and leftmost, or best and worst happens to extend quite naturally into business scenarios. Which product is the top seller? How about the one that's simply not moving off the shelves? Which of our customers has placed the most expensive order? What are the most recent orders placed at each of our outlets?

In the world of common business questions, the edge cases are generally of most interest. What's in the middle is unimportant; it's often too difficult for the mind to compare and comprehend when there are hundreds, thousands, or even millions of items, transactions, or facts that are all within a similar range. Instead, we focus on those that stick out in some extraordinary way.

Those of us who work with SQL products on a regular basis are faced with solving this same problem time and again as we work through various business requirements. Over time, I have noticed four basic query patterns that can be used to solve the problem; each are logically equivalent (within certain restrictions -- more on that later), but can have surprisingly different performance characteristics depending on the data being queried. In this first post, I will outline the available patterns/methods. In the following posts, I will show the results of testing each pattern against a variety of scenarios in an attempt to discover where and when each should be used.

The four basic patterns are outlined below. Each of the methods is illustrated using a query to show all customers' names, plus their most recent order date, and the amount of that order. I've included notes that indicate where logic differences can arise among the various methods.

Method 1: Join to full group and use correlated subquery with a MIN/MAX aggregate to filter

In this method we use an inner join to get all required columns, then filter the resultant set using a correlated subquery in the WHERE clause.

SELECT
    c.FirstName,
    c.LastName,
    o.OrderDate,
    o.OrderAmount
FROM Customers c
JOIN Orders o ON o.CustomerId = c.CustomerId
WHERE
    o.OrderDate =
    (
        SELECT MAX(o1.OrderDate)
        FROM Orders o1
        WHERE
            o1.CustomerId = o.CustomerId
    )

Logic notes: With this method ties are automatically included in the output, unless a tiebreaker is specified (which can be tricky given that you only have one column to work with). This method does not allow you to pull back an arbitrary number of rows, such as top 10 per customer; you are limited to the edge and any ties that might exist.

Method 1a: Join to full group and use correlated subquery with TOP(n) and ORDER BY to filter

This method is almost identical to Method 1 (which is why it is classified here as 1a), but the TOP and ORDER BY allow for a bit more flexibility than the aggregates.

SELECT
    c.FirstName,
    c.LastName,
    o.OrderDate,
    o.OrderAmount
FROM Customers c
JOIN Orders o ON o.CustomerId = c.CustomerId
WHERE
    o.OrderDate =
    (
        SELECT TOP(1)
            o1.OrderDate
        FROM Orders o1
        WHERE
            o1.CustomerId = o.CustomerId
        ORDER BY
            o1.OrderDate DESC
    )

Logic notes: With this method you can more easily integrate a tiebreaker than with Method 1; the comparison column can be anything, including a primary key, and you can still order on whatever column makes most sense. In addition, you can take more rows than with Method 1 by using IN instead of = in the WHERE clause, and increasing the argument value to TOP.

Method 2: CROSS APPLY to ordered TOP(n)

In this method, SQL Server 2005's CROSS APPLY operator is used. This operator allows us to essentially create a table-valued correlated subquery -- something that impossible in previous versions of SQL Server. By using TOP in conjunction with ORDER BY we can get as many rows per group as needed.

SELECT
    c.FirstName,
    c.LastName,
    x.OrderDate,
    x.OrderAmount
FROM Customers c
CROSS APPLY
(
    SELECT TOP(1)
        o.OrderDate,
        o.OrderAmount
    FROM Orders o
    WHERE
        o.CustomerId = c.CustomerId
    ORDER BY
        o.OrderDate DESC
) x

Logic notes: This method is almost identical, from a logic point of view, with Method 1a modified to use IN on a primary key column. With both methods WITH TIES can be added to the TOP in order to get ties.

Method 3: Join to derived table that uses a partitioned, ordered windowing function, and filter in the outer query based on the row number

In this method a derived table or CTE is used, in conjunction with a windowing function partitioned based on the required grain of the final query. So for the "most recent order per customer" query, the row number is partitioned based on the customer. This gives us a count starting at 1 for each customer, which can be filtered in the outer query.

SELECT
    c.FirstName,
    c.LastName,
    x.OrderDate,
    x.OrderAmount
FROM Customers c
INNER JOIN
(
    SELECT
        o.OrderDate,
        o.OrderAmount,
        o.CustomerId,
        ROW_NUMBER() OVER
        (
            PARTITION BY o.CustomerId
            ORDER BY o.OrderDate DESC
        ) AS r
    FROM Orders o
) x ON
    x.CustomerId = c.CustomerId
    AND x.r = 1

Logic notes: If ties are important, use DENSE_RANK instead of ROW_NUMBER. ROW_NUMBER is good for arbitrary TOP(n), similar to Method 2. Unlike the previously described methods, in conjunction with DENSE_RANK this method can return an arbitrary TOP(n) rows, all of which can include ties. So if you would like to see the three most recent order dates and each happens to have multiple orders, this method will be able to return them all by simply filtering on x.r = 3. This would not be directly possible with any of the other methods described here.

Method 4: "Carry-along sort"

This is the only "tricky" method, and not one that I recommend using, except as a last resort. I'm including it here only for completeness and comparison because it happens to be a very high performance method in some cases. This method involves converting each of the required inner columns into a string, concatenating them, then applying an aggregate to the string as a whole. By putting the "sort" column first, the other data is "carried along" -- thus the name for the method. The concatenated data is then "unpacked" in the outer query. This can be surprisingly efficient from an I/O standpoint, but the resultant code is a maintenance nightmare and it is quite easy to introduce errors. In addition, this method can only return the top 1 per group -- no ties or multiple return items are supported.

SELECT
    c.FirstName,
    c.LastName,
    CONVERT(DATETIME, SUBSTRING(x.OrderInfo, 1, 8)) AS OrderDate,
    CONVERT(MONEY, SUBSTRING(x.OrderInfo, 9, 15)) AS OrderAmount
FROM Customers c
INNER JOIN
(
    SELECT
        o.CustomerId,
        MAX
        (
            CONVERT(CHAR(8), OrderDate, 112) +
            CONVERT(CHAR(15), SubTotal)
        ) OrderInfo
    FROM Orders o
    GROUP BY
        o.CustomerId
) x ON
    x.CustomerId = c.CustomerId

This post is just the beginning; watch this space in the coming days for a series of performance tests and analysis of these methods, and some results that I personally found to be quite surprising.

Medians, ROW_NUMBERs, and performance

Adam Machanic — Mon, 18 Dec 2006 19:52:00 GMT

A couple of days ago, Aaron Bertrand posted about a method for calculating medians in SQL Server 2005 using the ROW_NUMBER function in conjunction with the COUNT aggregate. This method (credited to Itzik Ben-Gan) is interesting, but I discovered an even better way to attack the problem in Joe Celko's Analytics and OLAP in SQL.

Rather than using a COUNT aggregate in conjunction with the ROW_NUMBER function, Celko's method uses ROW_NUMBER twice: Once with an ascending sort, and again with a descending sort. The output rows can then be matched based on the ascending row number being within +/- 1 of the descending row number. This becomes clearer with a couple of small examples:

A	1	4
B	2	3
C	3	2
D	4	1

A	1	5
B	2	4
C	3	3
D	4	2
E	5	1

In the first table (even number of rows), the median rows are B and C. These can be matched based on [Ascending Column] IN ([Descending Column] + 1, [Descending Column] - 1). In the second table (odd number of rows), the median row is C, which is matched where [Ascending Column] = [Descending Column]. Note that in the second table, the match criteria for the first table does not apply -- so the generic expression to match either case is the combination of the two: [Ascending Column] IN ([Descending Column], [Descending Column] + 1, [Descending Column] - 1).

We can apply this logic within the AdventureWorks database to find the median of the "TotalDue" amount in the Sales.SalesOrderHeader table, for each customer:

SELECT
   CustomerId,
   AVG(TotalDue)
FROM
(
   SELECT
      CustomerId,
      TotalDue,
      ROW_NUMBER() OVER (
         PARTITION BY CustomerId
         ORDER BY TotalDue ASC, SalesOrderId ASC) AS RowAsc,
      ROW_NUMBER() OVER (
         PARTITION BY CustomerId
         ORDER BY TotalDue DESC, SalesOrderId DESC) AS RowDesc
   FROM Sales.SalesOrderHeader SOH
) x
WHERE
   RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY CustomerId
ORDER BY CustomerId;

The equivalent logic using Itzik Ben-Gan's method follows:

SELECT
   CustomerId,
   AVG(TotalDue)
FROM
(
   SELECT
      CustomerId,
      TotalDue,
      ROW_NUMBER() OVER (
         PARTITION BY CustomerId
         ORDER BY TotalDue) AS RowNum,
       COUNT(*) OVER (
          PARTITION BY CustomerId) AS RowCnt
   FROM Sales.SalesOrderHeader
) x
WHERE
   RowNum IN ((RowCnt + 1) / 2, (RowCnt + 2) / 2)
GROUP BY CustomerId
ORDER BY CustomerId;

Taking a look at the estimated execution plans for these two queries, we might believe that Ben-Gan's method is superior: Celko's algorithm requires an expensive intermediate sort operation and has an estimated cost of 4.96, compared to 3.96 for Ben-Gan's.

Remember that these are merely estimates. And as it turns out, this is one of those times that the Query Optimizer's cost estimates are are totally out of line with the reality of what happens when you actually run the queries. Although the performance difference is not especially noticeable on a set of data as small as that in Sales.SalesOrderHeader, check out the STATISTICS IO output. Celko's version does 703 logical reads; Ben-Gan's does an astonishing 140110!

There is a good lesson to be learned from this: Cost-based optimization is far from perfect! Never completely trust what estimates tell you; they've come a long way, but clearly there is still some work to do in this area. The only way to actually determine that one query is better than another is to run it against a realistic set of data and look at how much IO and CPU time is actually used.

In this case, Ben-Gan's query probably should perform better than Celko's. It seems odd that the Query Processor can't collect the row counts at the same time it processes the row numbers. Regardless, as of today this is the best way to solve this problem... Not that I've ever needed a median in any production application I've worked on. But I suppose that's beside the point!

Scalar functions, inlining, and performance: An entertaining title for a boring post

Adam Machanic — Fri, 04 Aug 2006 03:07:00 GMT

Scalar. Function.

Wow.

Could any other combination of words evoke the same feeling of encapsulation, information hiding, and simplification of client code? After years spent developing software in the procedural and OO worlds, it can be difficult--perhaps, even impossible--to migrate over to working with SQL Server and not consider how to architect your data access logic using some of the same techniques you'd use in the application tier.

In short: Why would you ever write the same piece of logic more than once? Answer: You wouldn't (damn it!). And so Microsoft bestowed upon the SQL Server community, in SQL Server 2000, the ability to write scalar user-defined functions. And they could have been such beautiful things...

But alas, reality can be painful, and as developers tried these new tools they were struck with a strange feeling of sadness as their applications buckled under the weight of what otherwise would have been a wonderful idea. As it turned out, putting all but the simplest of logic into these scalar functions was a recipe for disaster. Why? Because they're essentially cursors waiting to happen (but they don't look like cursors, so you may not know... until it's too late.)

The central problem is that when you wrap logic in a multistatement UDF, the query optimizer just can't unwrap it too easily. And so there's really only one way to evaluate a scalar UDF: call it once per row. And that is really nothing more than a cursor.

Seeing this behavior in action is easy enough; consider the following scalar function that some poor sap DBA working for AdventureWorks might be compelled to create:

CREATE FUNCTION GetMaxProductQty_Scalar
(
    @ProductId INT
)
RETURNS INT
AS
BEGIN
    DECLARE @maxQty INT

    SELECT @maxQty = MAX(sod.OrderQty)
    FROM Sales.SalesOrderDetail sod
    WHERE sod.ProductId = @ProductId

    RETURN (@maxQty)
END

Simple enough, right? Let's pretend that AdventureWorks has a bunch of reports, each of which requires maximum quantity sold per product. So the DBA, thinking he can save himself some time and keep everything centralized (and that is a good idea), puts all of the logic into a scalar UDF. Now, when he needs this logic, he can just call the UDF. And if the logic has a bug, or needs to be changed, he can change it in exactly one place. And so life is great... Or is it?

Let's take a look at a sample query:

SELECT
    ProductId,
    dbo.GetMaxProductQty_Scalar(ProductId)
FROM Production.Product
ORDER BY ProductId

This query does nothing more than get the max quantity sold for each product in the Productin.Product table. And a look at the execution plan or the STATISTICS IO output might indicate that there's nothing too interesting going on here: The execution plan shows an index scan (to be expected, with no WHERE clause), followed by a compute scalar operation (the call to the UDF). And STATISTICS IO shows a mere 16 reads.

So why is this query so problematic? Because the real issue is hiding just beneath the surface. The execution plan and STATISTICS IO didn't consider any of the code evaluated within the UDF! To see what's really going on, fire up SQL Server Profiler, turn on the SQL:BatchCompleted event, and make sure you're showing the Reads column. Now run the query again and you'll see that this seemingly-innocent block of T-SQL is, in fact, using 365,247 logical reads. Quite a difference!

Each of those "compute scalar" operations is really a call to the UDF, and each of the calls to the UDF is really a new query. And all of those queries (all 504 of them -- the number of products in the Product table) add up to massive I/O. Clearly not a good idea in a production environment.

But luckily, we're not done here yet (or this would be a very boring post). Because while the performance penalty is a major turnoff, I really do love the encapsulation afforded by scalar UDFs. I want them (or a similar tool) in my toolbox... And so I got to thinking.

The answer to my dilemma, as it turns out, is to not use scalar UDFs at all, but rather to use inline table-valued UDFs and treat them like scalars. This means that queries get slightly more complex than with scalar UDFs, but because the funtions are inlined (treated like macros) they're optimized along with the rest of the query. Which means, no more under-the-cover cursors.

Following is a modified version of the scalar UDF posted above:

CREATE FUNCTION GetMaxProductQty_Inline
(
    @ProductId INT
)
RETURNS TABLE
AS
    RETURN
    (
        SELECT MAX(sod.OrderQty) AS maxqty
        FROM Sales.SalesOrderDetail sod
        WHERE sod.ProductId = @ProductId
    )

This function is no longer actually scalar--in fact, it now returns a table. It just so happens that the table has exactly one column and exactly one row, and uses the same logic as the scalar UDF shown above. So it's still scalar enough for my purposes.

The query shown above, used to retrieve the maximum quantity sold for each product, will not quite work with this UDF as-is. Trying to substitute in the new UDF will result in nothing more than a variant on an "object not found" error. Instead, you need actually treat this function like it returns a table (due to the fact that it does). And that means, in this case, a subquery:

SELECT
    ProductId,
    (
        SELECT MaxQty
        FROM dbo.GetMaxProductQty_Inline(ProductId)
    ) MaxQty
FROM Production.Product
ORDER BY ProductId

So there it is. We're now treating the table-valued UDF more or less just like a scalar UDF. And the difference in I/O results is really quite astounding: 1267 logical reads in this case. Meaning that the scalar UDF solution is around 288 times more I/O intensive!

The question being, of course, was it worth it? The whole thing could have been written as one query, without the need for any UDFs at all. And the final query in this case is quite a bit more complex than the previous version, in addition to the fact that the encapsulation breaks down to some degree by forcing the caller to have some knowledge of how the UDF actually works. But I do feel that this sacrifice is warranted in some cases. Although the "greatest quantity sold" example shown here is simplistic, imagine other situations in which the same code fragments or logic are used over and over, due to lack of a good way of standardizing and centralizing them. I know I've seen that a lot in my work, and some examples I can think of have included complex logic that might very well have been easier to maintain in a UDF.

This technique may not be perfect for every case, and it certainly has its tradeoffs. But it may be a useful trick to keep in the back of your mind for a rainy day in the data center when someone's scalar UDF solution starts breaking down and you need a fix that doesn't require a massive code rewrite.

Stored procedures are not parameterized views

Adam Machanic — Thu, 13 Jul 2006 01:54:00 GMT

Peter van Ooijen over at CodeBetter.com posted in his blog about some observations he had when working with stored procedures in a recent project. What I found to be interesting about his post was his comment that a stored procedure can be, "a view with parameters." I've run into this assertion before, and it's something I think needs some clarification for a lot of developers. I do not feel that there is any real similarity between stored procedures and views -- they are entirely different types of objects in an SQL database, and should not be considered forms of one another in any way.

Following is an edited version of the response I left in Peter's blog; I thought it warranted its own post:

Stored procedures are not -- and never can be -- "parameterized views". A view in an SQL database can be treated the same as a table in virtually every context. Consider:

SELECT * 
FROM Tbl 

vs.

SELECT * 
FROM 
View 

One of the great things about working with views and tables is that the person querying the database does not need to know whether the base object being queried is a view or a table. For the sake of writing SQL queries, they are one and the same. Both a view and a table have well-defined columns, with well-defined datatypes.

These assertions cannot be made for a stored procedure as compared with a view. A stored procedure is related to a view only in as much as both are defined using SQL syntax. But beyond there, the two diverge into completely different types of entities. First of all, consider:

SELECT *
FROM StoredProcedure

This will not work, and will only result in an "invalid object name" exception. The reason? Stored procedures expose no explicit output contract. Thanks to conditional branching, dynamic SQL, and SELECT *, a stored procedure can output vastly different results beween invocations, or based on different input parameters. It is quite possible to code a stored procedure that will output no result sets for one set of input parameters, two result sets for another, and four for another. Or, it's possible to change the returned result sets, e.g. by outputting different column names or datatypes. Please note, this is an extremely poor (and very dangerous) coding habit to get into -- but the point is, it is impossible to verify the output of a stored procedure for a given set of input parameters without running it.

Furthermore, a stored procedure "late binds" to the base objects being queried. This adds to the difficulty in verifying the output of a stored procedure, and is why you can create the following stored procedure without getting an exception (until you try to run it, of course):

CREATE PROC XYZ 

AS 
    SELECT 
* 
    FROM ThisTableDoesNotExist 

GO 

These stored procedure behaviors are in stark contrast to the way views work. Views provide a couple of means of verification:

The output columns/data types can be verified, and bound to, before actually querying the view
A view can be "schema bound", meaning that the underlying base tables (or other views) which the view is based on cannot be changed, schema-wise, unless the view is dropped.

For the first point, simply query the INFORMATION_SCHEMA.COLUMNS or sys.Columns views, and column information can be determined for a view without having to query it.

The second point adds to the first in a few ways. Schema binding brings to views a certain sense of "early binding," which as I mentioned is missing in stored procedures. Although no view can be created if one of its base objects does not exist, schema binding takes it one step further and guarantees that the base objects used to create the view must exist, and must not be changed, for as long as the view is present in the database. This means that if a schemabound view is created that outputs a certain set of columns with certain datatypes, it is guaranteed to do so for as long as it exists in the database -- in other words, its contract is bound to the schema, and changes to other objects cannot affect it. This is a powerful guarantee, which stored procedures fail to make.

So now the question is, if a stored procedures isn't a parameterized view then what is? The answer, as of SQL Server 2000 (and continuing in 2005), is the table-valued UDF. A table-valued UDF is parameterized, has an explicit and verifyable output contract*, and can be schema bound. If you are looking to implement a solution that makes use of a form of parameterized views, stored procedures are probably not the right choice. I think that table-valued UDFs are quite underused and deserve a second (or first!) look from many T-SQL developers who may have glossed over them in the past.

* Note: Unlike for a view, the column list for a table-valued UDF cannot be queried from the INFORMATION_SCHEMA.COLUMNS table. The column list is, however, available from sys.Columns.

T-SQL Variables: Multiple Value Assignment

Adam Machanic — Thu, 13 Jul 2006 01:53:00 GMT

Tony Rogerson brings us an interesting blog post about T-SQL variable assignment and SET vs. SELECT. The issue? With SELECT you can assign values to multiple variables simultaneously. But with SET, you can set up your assignment such that you get an exception if more than one row is assigned to the variable. Both are desirable qualities... But unfortunately, as Tony shows us, it's difficult to achieve both multiple assignment and getting the exception thrown, at the same time. Tony shows us a solution involving checking for the number of rows affected after the assignment. Creative and effective, but it still has an issue: Unlike with SET when it throws an exception, with Tony's solution the variables will still have been affected by the assignment.

As I was reading Tony's post, I couldn't help but think that there must be another way. And low and behold, there is -- at least, in SQL Server 2005. Thanks to the power of windowed aggregates we can have our multiple pieces of cake and eat them, all at the same time. Wonderful stuff.

So, here's what you do: Set up a CTE that selects the columns you'd like to assign to your variables, and also get COUNT(*), partitioned by 1 (or some other arbitrary literal). By partitioning by a literal, we will end up with the row count for the entire set. In the outer query, express the assignments from the columns returned by the CTE, but add an additional WHERE clause that compares the value of the COUNT(*) column with a subquery against a table of numbers. In the following example which I've adapted from Tony's blog, I'm using master..spt_values for the numbers, but you are encouraged to use a properly-indexed table of numbers, should you decide to use this technique:

DECLARE
    @reserved INT,
    @rowcnt INT,
    @used INT

SET @reserved = -1
SET @rowcnt = -1
SET @used = -1

;WITH x AS
(
    SELECT
        reserved,
        rowcnt,
        used,
        COUNT(*) OVER(PARTITION BY 1) AS theCount
    FROM sysobjects so
    INNER JOIN sysindexes si ON si.id = so.id
    WHERE
        so.name = 'sysrowsets'
)
SELECT
    @reserved = reserved,
    @rowcnt = rowcnt,
    @used = used
FROM x
WHERE theCount =
    (
        SELECT
            number
        FROM master..spt_values
        WHERE
            TYPE = 'p'
            AND number BETWEEN 1 AND theCount
    )

SELECT @reserved, @rowcnt, @used

As you'll see if you run this on your end, an exception is thrown and the values of the variables are not affected. This works because the subquery used in the WHERE clause will return more than one value if theCount is greater than 1, thereby violating the rule that subqueries must only return one value.

The price you'll pay for this convenience? Extremely complex code for a simple variable assignment, in addition to a slight performance penalty. Is it worth it? Probably not, at least for me. To be honest, I seriously doubt I will ever use this -- I've never been especially concerned with the chance of multiple rows screwing up my variable assignment, and those times that it has happened, I've remedied the situation other ways (e.g., defining a better primary key). That said, I think this was an interesting T-SQL challenge, and if anyone comes up with a more elegant solution than Tony's or mine, I'd love to see it!

Running sums, redux

Adam Machanic — Thu, 13 Jul 2006 01:51:00 GMT

Siddhartha Gautama, the Buddha, taught us to understand that the key to enlightenment is following the Middle Path. And today I learned a valuable lesson in extremes. You can file this one in the "Doh! Wrong again!" category...

A fairly common question on SQL Server forums is, "how can I get the running sum of the data in this column?" Being the fan of set-based queries that I am, I always answer the exact same way. I show the person asking the question how to do a self-join on the grouped column, getting all of the "previous" values to create a running sum. The following example shows how you might do this against the AdventureWorks Production.TransactionHistory table:

SELECT
    TH1.TransactionID,
    TH1.ActualCost,
    SUM(TH2.ActualCost) AS RunningTotal
FROM Production.TransactionHistory TH1
JOIN Production.TransactionHistory TH2 ON TH2.TransactionID <= TH1.TransactionID
GROUP BY TH1.TransactionID, TH1.ActualCost
ORDER BY TH1.TransactionID

Pretty simple query. For each row of the "TH1" alias, every row with a lesser-or-equal TransactionID will be summed. Thereby creating a running total for every row of the table. Note, I've used the IDENTITY column instead of the date column. I'd generally suggest not doing so because, e.g., you might need to insert some post-dated rows at some point and relying on the IDENTITY for a time sequence will thereby not work. But in this case it's a lazy solution because the TransactionDate column isn't indexed, and it's also not unique. I want to test a lot of rows (TransactionHistory has around 113,000), but I don't want to skew the test by forcing a table scan on every iteration!

But I digress. The point is, I've given this answer more than a few times and, well, I'd like to apologize. Just now I went ahead and ran this query on my powerful test server--err, my laptop.

As you might guess, since I'm performance-minded I also happen to be extremely impatient--so I went ahead and killed the query at the five-minute mark. SSMS's result grid showed the first 26,568 rows, so obviously there was a long way to go to hit that 113,000 mark. And with an estimated cost of 38,086 for the query, I can't say I'm surprised.

A few moments of head scratching and the following re-write was issued:

SELECT
    TH1.TransactionID,
    TH1.ActualCost,
    (
        SELECT SUM(TH2.ActualCost)
        FROM Production.TransactionHistory TH2
        WHERE TH2.TransactionID <= TH1.TransactionID
    ) AS RunningTotal
FROM Production.TransactionHistory TH1
ORDER BY TH1.TransactionID

With an estimated cost of only 6,630, I had high hopes for this one. Alas, once again I was forced to cancel the query at the five-minute mark. 27,683 rows. Not much better, I'm afraid. And, as an aside, I'm starting to wonder about these estimated costs. But that's another post for another day.

So where am I going with all of this? Well, there's a reason I haven't given any indication up until this point in the post. You see, it's utterly painful to write this, but...

In this case, a cursor is faster.

Yes, I said it. That evil construct which we as database developers despise, the cursor. Thanks to Paul Nielsen, who revealed this ugly fact to me in a conversation today, I was forced to test this for myself (hoping to prove him wrong, of course). Which is why I started playing around with the solution that I've given so many times on forums. Unfortunately, he is correct.

My next test query, using the first cursor I've written in several years:

DECLARE RunningTotalCursor
CURSOR LOCAL FAST_FORWARD FOR
    SELECT TransactionID, ActualCost
    FROM Production.TransactionHistory
    ORDER BY TransactionID

OPEN RunningTotalCursor

DECLARE @TransactionID INT
DECLARE @ActualCost MONEY

DECLARE @RunningTotal MONEY
SET @RunningTotal = 0

DECLARE @Results TABLE
(
    TransactionID INT NOT NULL PRIMARY KEY,
    ActualCost MONEY,
    RunningTotal MONEY
)

FETCH NEXT FROM RunningTotalCursor
INTO @TransactionID, @ActualCost

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @RunningTotal = @RunningTotal + @ActualCost

    INSERT @Results
    VALUES (@TransactionID, @ActualCost, @RunningTotal)

    FETCH NEXT FROM RunningTotalCursor
    INTO @TransactionID, @ActualCost
END

CLOSE RunningTotalCursor

DEALLOCATE RunningTotalCursor

SELECT *
FROM @Results
ORDER BY TransactionID

What's really unfortunate about the cursor approach is that you need to use a temporary table if you want to return a single result set to the client. I figured the additional I/O due to the temp table would balance any improvement gains from the cursor approach, thereby rendering my forum responses correct, and Paul wrong. Well, 14 seconds and 113,443 rows later, SSMS and my laptop declared Paul the undisputed Champion of the Cursor.

This cursor makes a lot of sense in this case. The set-based query works by looping over each row of the table, taking a sum of every previous row. So for the 10th row, 10 previous rows also need to be visited. For the 1000th row, 1000 previous rows need to be visited. And so on. The larger the set gets, the worse performance will be--and that's not going to be a merely linear decrease in performance. Think about this: Using the set-based method to find the running sum over a set of 100 rows, 5050 total rows need to be visited. For a set of 200 rows, the query processor needs to visit 20100 total rows -- a four-fold increase in the amount of work that must be done to satisfy the query (O((N^2)/2), for those who are a bit more algorithmically minded.)

The cursor, on the other hand, needs to visit each row exactly once (O(N)). By maintaining the running count in a variable, there is no need to re-visit previous rows. And as my laptop was so happy to show me, the I/O cost due to the temp table does not overshadow the performance improvement of having to visit so many less rows.

So what have we learned today? In my set-based singlemindedness I failed to realize that the cursor does, indeed, have utility. Everything in moderation.

Next steps? I get the feeling that this can be made even faster by employing a CLR routine. Pull the data into a DataReader and loop over that instead, which will completely eliminate the need for a temporary table. Watch for that experiment coming to this space soon.

And next time you hear someone mention how horrible cursors are, remind that person that there is a time and place for everything (and it's called college).

Swinging From Tree to Tree Using CTEs, Part 2: Adjacency to Nested Intervals

Adam Machanic — Thu, 13 Jul 2006 01:49:00 GMT

In our previous installment, we saw how to convert Adjacency Lists into Nested Sets using a CTE.

In this episode, we will convert the Adjacency List into a Nested Intervals encoding. Specifically, this encoding will make use of the Nested Intervals with Continued Fractions technique that Tropashko presented in a later paper.

The key to this technique lies in using a slightly different form of materialized path than was used in the last post. Rather than materializing the EmployeeIds into a path, the path will be created as an enumerated representation, based on sibling ordering. For example, the first two levels of the AdventureWorks HumanResources.Employee table's tree look like:

EmployeeId 109 (CEO)

EmployeeId 6 (Marketing Manager)
EmployeeId 12 (VP Engineering)
EmployeeId 42 (IS Manager)
EmployeeId 140 (CFO)
EmployeeId 148 (VP Production)
EmployeeId 273 (VP Sales)

Using the materialized path representation from the previous post, the paths to the second-level employees would be:

6: 109.6
12: 109.12
42: 109.42
140: 109.140
148: 109.148
273: 109.273

However, for this post, paths will instead be materialized based on sibling ordering. We don't know anything about how siblings should be ordered in the AdventureWorks employee hierarchy (that's probably a business rule question, if it matters at all). So ordering will be done by EmployeeId:

6: 1.1
12: 1.2
42: 1.3
140: 1.4
148: 1.5
273: 1.6

The significance of this encoding is that for each path a rational number can be generated, using a Euclidian algorithm (described very well on this web site). The algorithm works by iterating over each element on the path, building a rational number as it goes. The beauty of this, as shown by Tropashko in his paper, is that by using these paths we can determine an interval, beween which all children of a given path will fall.

In order to accomplish getting the path, the ROW_NUMBER function will be used, but slightly differently than last time. Instead of creating a second CTE to reference the first, thereby getting the row number for each element of the path, the ROW_NUMBER function will be embedded within the recursive CTE itself, as in the following example:

WITH EmployeeRows AS
(
    SELECT
        EmployeeId,
        ManagerId,
        ROW_NUMBER() OVER (ORDER BY EmployeeId) AS theRow
    FROM HumanResources.Employee
    WHERE ManagerId IS NULL

    UNION ALL

    SELECT
        e.EmployeeId,
        e.ManagerId,
        ROW_NUMBER() OVER (ORDER BY e.EmployeeId) AS theRow
    FROM EmployeeRows x
    JOIN HumanResources.Employee e ON e.ManagerId = x.EmployeeId
)
SELECT *
FROM EmployeeRows
ORDER BY
    ManagerId,
    EmployeeId

Interestingly, this example as-is will return exactly the same results as the following, non-recursive CTE example:

SELECT
    EmployeeId,
    ManagerId,
    ROW_NUMBER() OVER (PARTITION BY ManagerId ORDER BY EmployeeId) AS theRow
FROM HumanResources.Employee
ORDER BY
    ManagerId,
    EmployeeId

So, why do we care? Take a close look at the two examples. In the CTE example, the ROW_NUMBER function does not use PARTITION BY. Yet, results are implicitly partitioned. This gives an interesting view into the inner-workings of CTEs. As it turns out, the recursive part of the CTE is called once per row returned by the anchor or previous recursion. This is not how I originally expected CTEs to behave (I thought the recursive part would be called once per rowset returned by the anchor or previous recursion), but it does help us with this particular task!

The current row number, at any given point in the recursion, represents the enumeration for that node. But because we're using a recursive CTE, we also have access to the parent's enumaration. For instance, to build an enumerated path, the following T-SQL would be used:

WITH EmployeeRows AS
(
    SELECT
        EmployeeId,
        ManagerId,
        CONVERT(VARCHAR(MAX), ROW_NUMBER() OVER (ORDER BY EmployeeId)) AS thePath
    FROM HumanResources.Employee
    WHERE ManagerId IS NULL

    UNION ALL

    SELECT
        e.EmployeeId,
        e.ManagerId,
        x.thePath + '.' + CONVERT(VARCHAR(MAX), ROW_NUMBER() OVER (ORDER BY e.EmployeeId)) AS thePath
    FROM EmployeeRows x
    JOIN HumanResources.Employee e ON e.ManagerId = x.EmployeeId
)
SELECT *
FROM EmployeeRows
ORDER BY
    thePath

Note that this sample can be made a bit more readable (and more functional for later) by embeding the ROW_NUMBER in a derived table:

WITH EmployeeRows AS
(
    SELECT
        y.EmployeeId,
        y.ManagerId,
        CONVERT(VARCHAR(MAX), y.theRow) AS thePath
    FROM
    (
        SELECT
            EmployeeId,
            ManagerId,
            ROW_NUMBER() OVER (ORDER BY EmployeeId) AS theRow
        FROM HumanResources.Employee
        WHERE ManagerId IS NULL
    ) y

    UNION ALL

    SELECT
        y.EmployeeId,
        y.ManagerId,
        y.thePath + '.' + CONVERT(VARCHAR(MAX), y.theRow) AS thePath
    FROM
    (
        SELECT
            e.EmployeeId,
            e.ManagerId,
            x.thePath,
            ROW_NUMBER() OVER (ORDER BY e.EmployeeId) AS theRow
        FROM EmployeeRows x
        JOIN HumanResources.Employee e ON e.ManagerId = x.EmployeeId
    ) y
)
SELECT *
FROM EmployeeRows
ORDER BY
    thePath

From here, it's a simple step to implement the Euclidian algorithm. The algorithm is quite simple:

Set parentNumerator <- 1
Set parentDenominator <- 0
Set theElement <- first enumeration in the path
Set currentNumerator <- theElement
Set currentDenominator <- 1
Set theElement <- next enumeration in the path
Set previousParentNumerator <- parentNumerator
Set previousParentDenominator <- parentDenominator
Set parentNumerator <- currentNumerator
Set parentDenominator <- currentDenominator
Set currentNumerator <- (parentNumerator * theElement) + previousParentNumerator
Set currentDenominator <- (parentDenominator * theElement) + previousParentDenominator
If the current element is not the final node in the path, goto 6.

This seems a bit hairy, but I think that looking at the algorithm and spending a few minutes with our old friends pencil and paper will make it quite clear. Also, look at the web site linked above. Here is how I've implemented the algorithm using a CTE:

WITH EmployeeRows AS
(
    SELECT
        EmployeeId,
        CONVERT(VARCHAR(MAX), theRow) AS thePath,
        CONVERT(BIGINT, 1) AS prevNumer,
        CONVERT(BIGINT, 0) AS prevDenom,
        CONVERT(BIGINT, theRow) AS currNumer,
        CONVERT(BIGINT, 1) AS currDenom
    FROM
    (
        SELECT
            EmployeeId,
            ROW_NUMBER() OVER (ORDER BY EmployeeId) AS theRow
        FROM HumanResources.Employee
        WHERE ManagerId IS NULL
    ) y

    UNION ALL

    SELECT
        y.EmployeeId,
        y.thePath + '.' + CONVERT(VARCHAR(MAX), y.theRow) AS thePath,
        prevNumer = y.currNumer,
        prevDenom = y.currDenom,
        (y.currNumer * y.theRow) + y.prevNumer AS currNumer,
        (y.currDenom * y.theRow) + y.prevDenom AS currDenom
    FROM
    (
        SELECT
            e.EmployeeID,
            x.thePath,
            x.currNumer,
            x.currDenom,
            x.prevNumer,
            x.prevDenom,
            ROW_NUMBER() OVER (ORDER BY e.EmployeeID) AS therow
        FROM EmployeeRows x
        JOIN HumanResources.Employee e ON e.ManagerId = x.EmployeeId
    ) y
)

Note that in this case I didn't require the use of the temporary variables; the previous anchor/recursive parts act as temporary storage enough.

Readers will also hopefully notice that I haven't yet included a SELECT to get the data from the CTE! This is because I'd like to explain briefly what it will do. In his paper, Tropashko explains that for each node, the intervals for the children of that node will fall into an interval between the encoding of that node (currNumer / currDenom) and the numerator of the previous node plus the numerator for the current node, divided by the denominator of the previous node plus the denominator for the current node ((currNumer + prevNumer) / (currDenom + prevDenom)). Quite wordy here. Refer to the paper for a better explanation and proof.

Anyway, the completed query follows:

WITH EmployeeRows AS
(
    SELECT
        EmployeeId,
        CONVERT(VARCHAR(MAX), theRow) AS thePath,
        CONVERT(BIGINT, 1) AS prevNumer,
        CONVERT(BIGINT, 0) AS prevDenom,
        CONVERT(BIGINT, theRow) AS currNumer,
        CONVERT(BIGINT, 1) AS currDenom
    FROM
    (
        SELECT
            EmployeeId,
            ROW_NUMBER() OVER (ORDER BY EmployeeId) AS theRow
        FROM HumanResources.Employee
        WHERE ManagerId IS NULL
    ) y

    UNION ALL

    SELECT
        y.EmployeeId,
        y.thePath + '.' + CONVERT(VARCHAR(MAX), y.theRow) AS thePath,
        prevNumer = y.currNumer,
        prevDenom = y.currDenom,
        (y.currNumer * y.theRow) + y.prevNumer AS currNumer,
        (y.currDenom * y.theRow) + y.prevDenom AS currDenom
    FROM
    (
        SELECT
            e.EmployeeID,
            x.thePath,
            x.currNumer,
            x.currDenom,
            x.prevNumer,
            x.prevDenom,
            ROW_NUMBER() OVER (ORDER BY e.EmployeeID) AS therow
        FROM EmployeeRows x
        JOIN HumanResources.Employee e ON e.ManagerId = x.EmployeeId
    ) y
)
SELECT
    EmployeeId,
    thePath,
    currNumer AS startNumer,
    currDenom AS startDenom,
    currNumer + prevNumer AS endNumer,
    currDenom + prevDenom AS endDenom
FROM EmployeeRows

For each node (EmployeeId), you now have an interval (start and end) within which all children intervals will fall. Note that computation must be done by the interval, not by the current node's encoding. The reason becomes apparent when looking at the encodings for 1.1.1 and 1.2. They are the same; however, their intervals do not overlap. As a matter of fact, encodings will be the same for every next sibling/first child pair. But the intervals remain nested, and if proper queries are written there will be no confusion.

So that's a first step towards using the Nested Intervals Model in SQL Server 2005. Stay tuned for more... And as always, feel free to post questions or comments. I know some of this material can be confusing (at least, it was to me before I wrote this post!)

Swinging From Tree to Tree Using CTEs, Part 1: Adjacency to Nested Sets

Adam Machanic — Thu, 13 Jul 2006 01:47:00 GMT

I'm not sure how many times over the last several years I've seen the same tired article titles... "Climbing Trees in SQL," "Climbing Up the SQL Tree," or maybe, "Naked Coeds Playing in the Trees!" ... Oh wait, I think that last one might be something else.

But anyway, the point is, I'm going to adhere to that standard. But I'm adding a Tarzan-esque theme to this post, because we're not going to just climb a tree. We're going to swing about between trees. Different types of tree representations are appropriate for different scenarios. Which is why, as I pointed out in my recent SearchSQLServer article on recursive Common Table Expressions, we have so many different ways of representing them. Adjacency Lists, Materialized Paths, Nested Sets, and Nested Intervals spring to mind. And there are probably others.

My article shows how to use the CTEs, in conjunction with a dynamically generated materialized path, to manipulate an Adjacency List, getting many of the benefits associated with using the Nested Sets Model. And that's great. But the Nested Sets Model itself might be useful in your endeavors. So in this post, I will show how to extend the CTE discussed in that article.

The end-result CTE in the article, which can be run in the AdventureWorks database, looks something like this (renamed for the sake of this post):

WITH EmployeeLevels AS
(
    SELECT
        EmployeeId,
        CONVERT(VARCHAR(MAX), EmployeeId) AS thePath,
        1 AS Level
    FROM HumanResources.Employee
    WHERE ManagerId IS NULL

    UNION ALL

    SELECT
        e.EmployeeId,
        x.thePath + '.' + CONVERT(VARCHAR(MAX), e.EmployeeId) AS thePath,
        x.Level + 1 AS Level
    FROM EmployeeLevels x
    JOIN HumanResources.Employee e on e.ManagerId = x.EmployeeId
)

thePath is the materialized path, a '.' delimited breadcrumb from the root to each node. We also end up with a level, which represents how many nodes away from the root in the hierarchy each employee sits (very important for those upwardly-mobile junior execs, no doubt!)

I'm going to assume that readers of this post are familiar with the Nested Sets Model, as popularized by Joe Celko. If not, read the link.

After staring at the Nested Sets for a while, I arrived at the following mathematical properties:

Value of Lft for the root node is 1
Value of Rgt for the root node is 2 * (Number of nodes)
Value of Lft for any node is ((Number of nodes visited) * 2) - (Level of current node)
Value of Rgt for any node is (Lft value) + ((Number of subnodes) * 2) + 1

I think the only factor here that requires further explanation is "(Number of nodes visited)". By this, I mean the number of nodes that would have been visited (including the current node) if one were doing a preorder traversal of the tree. Luckily, this number is quite easy to determine. The row number for each row, as determined by ordering by the materialized path, is this number. I encourage readers to validate this with pencil and paper if it doesn't quite make sense reading on the screen. Draw a simple tree and traverse, starting from the lefthand side of the root node.

But how to translate that into T-SQL? Luckily, SQL Server 2005, in addition to CTEs, also includes the very useful ROW_NUMBER() function. So to get the row number, we simply need to add another CTE into the chain (did you know that successive CTEs can use the results of previous CTEs?):

WITH EmployeeLevels AS
(
    SELECT
        EmployeeId,
        CONVERT(VARCHAR(MAX), EmployeeId) AS thePath,
        1 AS Level
    FROM HumanResources.Employee
    WHERE ManagerId IS NULL

    UNION ALL

    SELECT
        e.EmployeeId,
        x.thePath + '.' + CONVERT(VARCHAR(MAX), e.EmployeeId) AS thePath,
        x.Level + 1 AS Level
    FROM EmployeeLevels x
    JOIN HumanResources.Employee e on e.ManagerId = x.EmployeeId
),
EmployeeRows AS
(
    SELECT
        EmployeeLevels.*,
        ROW_NUMBER() OVER (ORDER BY thePath) AS Row
    FROM EmployeeLevels
)

We now have current level and number of nodes visited, which gives us the Lft value for each node. But how to determine the number of subnodes, in order to get the Rgt value? Luckily, the materialized path also gives us that capability...

For any given node, number of subnodes can be determined by counting all nodes whose path value is LIKE the current path value + '.%'.

The resultant query should be fairly obvious at this point:

WITH EmployeeLevels AS
(
    SELECT
        EmployeeId,
        CONVERT(VARCHAR(MAX), EmployeeId) AS thePath,
        1 AS Level
    FROM HumanResources.Employee
    WHERE ManagerId IS NULL

    UNION ALL

    SELECT
        e.EmployeeId,
        x.thePath + '.' + CONVERT(VARCHAR(MAX), e.EmployeeId) AS thePath,
        x.Level + 1 AS Level
    FROM EmployeeLevels x
    JOIN HumanResources.Employee e on e.ManagerId = x.EmployeeId
),
EmployeeRows AS
(
    SELECT
        EmployeeLevels.*,
        ROW_NUMBER() OVER (ORDER BY thePath) AS Row
    FROM EmployeeLevels
)
SELECT
    ER.EmployeeId,
    ER.thePath,
    ER.Level,
    ER.Row,
    (ER.Row * 2) - ER.Level AS Lft,
    ((ER.Row * 2) - ER.Level) +
        (
            SELECT COUNT(*) * 2
            FROM EmployeeRows ER2
            WHERE ER2.thePath LIKE ER.thePath + '.%'
        ) + 1 AS Rgt
FROM EmployeeRows ER
ORDER BY thePath

... And that's it! We have now converted the Adjacency List tree into a Nested Sets tree.

In the next installment, I will show how to determine the Nested Intervals encoding from an Adjacency List tree, also using recursive CTEs.

Questions?

Texas Hold 'em Hint #1

Adam Machanic — Thu, 13 Jul 2006 01:46:00 GMT

The other day I annouced the Texas Hold 'em SQL Challenge. I haven't gotten any feedback on it yet, so I have no idea if anyone is working on it, but I thought I'd get the ball rolling and come up with my own solution...

The first step I've decided to take in solving this problem is to figure out how, given two or more 5-card hands, to figure out a winner. What's necessary is a way of weighting each hand so that better hands will have a higher value. Or at least, that's the tactic I took.

I first checked out this list of poker hands from strongest to weakest. After staring at it for a while, I realized that some patterns can be derived from the list:

A two-pair is two pairs (wow, getting deep here, aren't we!)
A full house is a pair plus a three of a kind
A straight flush is a straight plus a flush
A royal flush is a type of straight flush with the highest cards in the deck

Obvious, yes; but the final point is important -- is there a way of weighting the cards such that we can determine, using only a single number, the weight of a given hand, independent of whether or not it falls into one of these categories? That is, how can we know that a straight flush with a high king is worth less than a straight flush with a high ace? And how can we know that a hand with a pair of 10s and a high jack beats a hand with a pair of 10s and a high 9?

The answer is to use our old friend, binary:

Card	Value
2	2
3	4
4	8
5	16
6	32
7	64
8	128
9	256
10	512
Jack	1024
Queen	2048
King	4096
Ace	8192 (1)

Note that the ace will be weighted as 1, should it happen to be part of a straight with a high 5.

So what does this binary weighting give us? The property that's especially useful in this case is that for every power of 2, it's impossible to reach that number by adding up any unique combination of powers of 2 less than that power. Restated in terms of this project, if a hand has only a high card, say a king, then that hand will be weighted higher than any other high card hand that doesn't have either a king or an ace.

But weighting alone isn't enough. This can be shown using the following two hands:

2 3 Q K A
2 3 Q K K

The weighted values of these hands are 14342 and 10246, respectively (2 + 4 + 2048 + 4096 + 8192 and 2 + 4 + 2048 + 4096 + 4096). However, the second is a better hand. How to solve this issue? Bonuses.

After conducting a completely non-scientific trial-and-error analysis, I arrived at the following bonus structure, which seems to work:

To weight a hand, take the base weight (i.e., the sum of the weights of each card in the hand) and add the following bonuses per condition:

Pair: 2000000000 + (8192 * pair card weight)
Three of a kind: 6000000000 + (131072 * three of a kind card weight)
Straight: 5500000000
Flush: 5800000000
Four of a kind: 9500000000 + (131072 * four of a kind card weight)

Note that "pair card weight," for example, refers to the weight of the card type that makes up the pair. So for a pair of twos, the calculation would be (8192 * 2).

This formula can be expanded for each type of hand. Two pair? Add 2000000000 + (8192 * the weight of the card for the first pair) + (8192 * the weight of the card for the second pair). Full house? Combine the pair with the three of a kind. Same with straight flush. And since the base weights are being added, the hands will naturally be weighted heavier for higher cards.

There may have been a more elegant solution, but this one does the trick for now.

But enough math, on to the SQL! What I wanted was a table containing every possible hand and its weights. But first I needed a table with all of the possible cards:

CREATE TABLE dbo.Cards
(
	CardID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Suit CHAR(1) NOT NULL,
	Card VARCHAR(2) NOT NULL,
	Weight INT NOT NULL
)

INSERT dbo.Cards (Suit, Card, Weight)
SELECT S.Suit, C.Card, C.Weight
FROM
(
	SELECT 'H'
	UNION ALL
	SELECT 'D'
	UNION ALL
	SELECT 'S'
	UNION ALL
	SELECT 'C'
) S (Suit)
CROSS JOIN
(
	SELECT CONVERT(VARCHAR(2), number), POWER(2, number-1)
	FROM master..spt_values
	WHERE type='p'
		AND number BETWEEN 2 AND 10
	UNION ALL
	SELECT 'J', 1024
	UNION ALL
	SELECT 'Q', 2048
	UNION ALL
	SELECT 'K', 4096
	UNION ALL
	SELECT 'A', 8192
) C (Card, Weight)
ORDER BY 
	Suit, 
	Weight
GO

Yes, I know that (Suit, Card) could have formed a natural primary key, but the table of hands is going ta have 2598960 rows -- which you can prove using the formulas from this website:

52 cards, 5 cards per hand:

            n!
n_C_k = ----------
        k!(n - k)!

      52!
= -----------
  5!(52 - 5)!

      52!
= -----------
    5!(47)!

  52 * 51 * 50 * 49 * 48
= ----------------------
     5 * 4 * 3 * 2 * 1

   311875200
= -----------
      120

= 2598960

...And to believe that my high school algebra teacher had the gall to flunk me!

Anyway, with 2.59 million rows, I'd rather just use a single-column surrogate. It makes like a lot easier. So how do we generate all of these rows?

First, we need every possible hand:

SELECT
	C1.CardId AS Card1,
	C2.CardId AS Card2,
	C3.CardId AS Card3,
	C4.CardId AS Card4,
	C5.CardId AS Card5
FROM dbo.Cards C1
JOIN dbo.Cards C2 ON 
	C2.CardId > C1.CardId
JOIN dbo.Cards C3 ON 
	C3.CardId > C2.CardId
JOIN dbo.Cards C4 ON
	C4.CardId > C3.CardID
JOIN dbo.Cards C5 ON
	C5.CardId > C4.CardId

Easy enough. And notice how nice and simple that surrogate makes life... Laziness is a virtue!

Next step, apply the weighting logic. This is quite a bit more complex, and I'm not going to spend a lot of time going into detail since it's currently quite late here and I need to work in the morning. But remember the following rules: If you have two or more of the same index in a hand (pair, three of a kind, four of a kind), that hand can't be a flush. And if you have only two of a given card, that hand can't have a three of a kind of that card. And if you have only three of a card, that hand can't have a four of a kind for that card!

Given these rules, I realized that you can test for all of these conditions and add up the results, without doing any kind of backtracking. This made the job quite a bit simpler. I did the following: First, calculate the base weight and figure out whether we have a straight, for any given row. Add up the results. Then "unpivot" (using a cross-join to five rows of numbers) and use aggregates to figure out what pairs, threes of a kind, fours of a kind, and flushes exist in each hand... Re-pivot, and stuff everything into a table. Oh, and I implemented some rudimentary batching so that 2.5 million rows wouldn't be inserted in a single transaction.

The complete SQL is here:

CREATE TABLE dbo.PokerHands
(
	Card1 INT NOT NULL,
	Card2 INT NOT NULL,
	Card3 INT NOT NULL,
	Card4 INT NOT NULL,
	Card5 INT NOT NULL,
	Weight BIGINT NOT NULL,
	CONSTRAINT PK_PokerHands PRIMARY KEY CLUSTERED
		(Card1, Card2, Card3, Card4, Card5)
)
GO

DECLARE @CardId INT
SET @CardId = 1

WHILE @CardId <= 52
BEGIN
	INSERT dbo.PokerHands
		(Card1, Card2, Card3, Card4, Card5, Weight)
	SELECT 
		y.Card1,
		y.Card2,
		y.Card3,
		y.Card4,
		y.Card5,
		-- 2
		CASE 
			SUM(
				CASE y.CardWeight
					WHEN 2 THEN 1
					ELSE 0
				END
			)
			WHEN 4 THEN 9500000000 + (131072 * 2)
			WHEN 3 THEN 6000000000 + (131072 * 2)
			WHEN 2 THEN 2000000000 + (8192 * 2)
			ELSE 0
		END +
		-- 3
		CASE 
			SUM(
				CASE y.CardWeight
					WHEN 4 THEN 1
					ELSE 0
				END
			)
			WHEN 4 THEN 9500000000 + (131072 * 4)
			WHEN 3 THEN 6000000000 + (131072 * 4)
			WHEN 2 THEN 2000000000 + (8192 * 4)
			ELSE 0
		END +
		-- 4
		CASE 
			SUM(
				CASE y.CardWeight
					WHEN 8 THEN 1
					ELSE 0
				END
			)
			WHEN 4 THEN 9500000000 + (131072 * 8)
			WHEN 3 THEN 6000000000 + (131072 * 8)
			WHEN 2 THEN 2000000000 + (8192 * 8)
			ELSE 0
		END +
		-- 5
		CASE 
			SUM(
				CASE y.CardWeight
					WHEN 16 THEN 1
					ELSE 0
				END
			)
			WHEN 4 THEN 9500000000 + (131072 * 16)
			WHEN 3 THEN 6000000000 + (131072 * 16)
			WHEN 2 THEN 2000000000 + (8192 * 16)
			ELSE 0
		END +
		-- 6
		CASE 
			SUM(
				CASE y.CardWeight
					WHEN 32 THEN 1
					ELSE 0
				END
			)
			WHEN 4 THEN 9500000000 + (131072 * 32)
			WHEN 3 THEN 6000000000 + (131072 * 32)
			WHEN 2 THEN 2000000000 + (8192 * 32)
			ELSE 0
		END +
		-- 7
		CASE 
			SUM(
				CASE y.CardWeight
					WHEN 64 THEN 1
					ELSE 0
				END
			)
			WHEN 4 THEN 9500000000 + (131072 * 64)
			WHEN 3 THEN 6000000000 + (131072 * 64)
			WHEN 2 THEN 2000000000 + (8192 * 64)
			ELSE 0
		END +
		-- 8
		CASE 
			SUM(
				CASE y.CardWeight
					WHEN 2 THEN 1
					ELSE 0
				END
			)
			WHEN 4 THEN 9500000000 + (131072 * 128)
			WHEN 3 THEN 6000000000 + (131072 * 128)
			WHEN 2 THEN 2000000000 + (8192 * 128)
			ELSE 0
		END +
		-- 9
		CASE 
			SUM(
				CASE y.CardWeight
					WHEN 256 THEN 1
					ELSE 0
				END
			)
			WHEN 4 THEN 9500000000 + (131072 * 256)
			WHEN 3 THEN 6000000000 + (131072 * 256)
			WHEN 2 THEN 2000000000 + (8192 * 256)
			ELSE 0
		END +
		-- 10
		CASE 
			SUM(
				CASE y.CardWeight
					WHEN 512 THEN 1
					ELSE 0
				END
			)
			WHEN 4 THEN 9500000000 + (131072 * 512)
			WHEN 3 THEN 6000000000 + (131072 * 512)
			WHEN 2 THEN 2000000000 + (8192 * 512)
			ELSE 0
		END +
		-- J
		CASE 
			SUM(
				CASE y.CardWeight
					WHEN 1024 THEN 1
					ELSE 0
				END
			)
			WHEN 4 THEN 9500000000 + (131072 * 1024)
			WHEN 3 THEN 6000000000 + (131072 * 1024)
			WHEN 2 THEN 2000000000 + (8192 * 1024)
			ELSE 0
		END +
		-- Q
		CASE 
			SUM(
				CASE y.CardWeight
					WHEN 2048 THEN 1
					ELSE 0
				END
			)
			WHEN 4 THEN 9500000000 + (131072 * 2048)
			WHEN 3 THEN 6000000000 + (131072 * 2048)
			WHEN 2 THEN 2000000000 + (8192 * 2048)
			ELSE 0
		END +
		-- K
		CASE 
			SUM(
				CASE y.CardWeight
					WHEN 4096 THEN 1
					ELSE 0
				END
			)
			WHEN 4 THEN 9500000000 + (131072 * 4096)
			WHEN 3 THEN 6000000000 + (131072 * 4096)
			WHEN 2 THEN 2000000000 + (8192 * 4096)
			ELSE 0
		END +
		-- A
		CASE 
			SUM(
				CASE y.CardWeight
					WHEN 8192 THEN 1
					ELSE 0
				END
			)
			WHEN 4 THEN 9500000000 + (131072 * 8192)
			WHEN 3 THEN 6000000000 + (131072 * 8192)
			WHEN 2 THEN 2000000000 + (8192 * 8192)
			ELSE 0
		END +
		--Flush
		CASE COUNT(DISTINCT y.CardSuit)
			WHEN 1 THEN 5800000000
			ELSE 0
		END +
		--Base Weight + Straight
		MIN(y.TotalWeight) AS Weight
	FROM
	(
		SELECT TOP 100 PERCENT
			C1.CardId AS Card1,
			C2.CardId AS Card2,
			C3.CardId AS Card3,
			C4.CardId AS Card4,
			C5.CardId AS Card5,
			--Straight
			CASE WHEN 
				(C2.Weight = 2 * C1.Weight
				AND C3.Weight = 2 * C2.Weight
				AND C4.Weight = 2 * C3.Weight
				AND (
					(C5.Weight = 2 * C4.Weight)
					OR 
					(C2.Card = '2' AND C5.Card = 'A')))
				THEN 
					CASE 
						WHEN (C2.Card = '2' AND C5.Card = 'A')
							THEN 5500000000 - 8191 --Make A = weight 1
						ELSE 5500000000
					END
				ELSE 0
			END +
			--Base weight
			C1.Weight + C2.Weight + C3.Weight + C4.Weight + C5.Weight
				AS TotalWeight,
			CASE x.number
				WHEN 1 THEN C1.Weight
				WHEN 2 THEN C2.Weight
				WHEN 3 THEN C3.Weight
				WHEN 4 THEN C4.Weight
				WHEN 5 THEN C5.Weight
			END AS CardWeight,
			CASE x.number
				WHEN 1 THEN C1.Suit
				WHEN 2 THEN C2.Suit
				WHEN 3 THEN C3.Suit
				WHEN 4 THEN C4.Suit
				WHEN 5 THEN C5.Suit
			END AS CardSuit
		FROM dbo.Cards C1
		JOIN dbo.Cards C2 ON 
			C2.CardId > C1.CardId
		JOIN dbo.Cards C3 ON 
			C3.CardId > C2.CardId
		JOIN dbo.Cards C4 ON
			C4.CardId > C3.CardID
		JOIN dbo.Cards C5 ON
			C5.CardId > C4.CardId
		CROSS JOIN
		(
			SELECT number
			FROM master..spt_values
			WHERE type='p'
				AND number BETWEEN 1 and 5
		) x
		WHERE C1.CardId = @CardId
		ORDER BY
			C1.CardId,
			C2.CardId,
			C3.CardId,
			C4.CardId,
			C5.CardId
	) y
	GROUP BY
		y.Card1,
		y.Card2,
		y.Card3,
		y.Card4,
		y.Card5

	SET @CardId = @CardId + 1
END

... And that's about it! Assuming I made no logic mistakes -- which of course is a very safe assumption because it's me and I never do that -- this is a relatively good start to the Challenge! So take it from here... Or at least until I post the next piece of the puzzle.

In retrospect, I think this would be a good use for a CLR UDT. If I have some time, I'll post that version in the coming days. Enjoy!

Looping over routines using sp_foreachroutine

Adam Machanic — Thu, 13 Jul 2006 01:45:00 GMT

Of all of the undocumented stored procedures shipped with SQL Server, there are two in particular that I constantly use: sp_MSforeachtable and sp_MSforeachdb. These procedures internally loop over each non-Microsoft shipped (i.e. user-defined) table in the current database, or each database on the current server, respectively. During this loop, the procedures perform whatever action(s) are specified by the user (in the parameters). For instance, what if you want to re-index every table in the database? Sure, you could write your own cursor, but why bother? Use the following T-SQL instead:

EXEC sp_MSforeachtable 'DBCC DBREINDEX(''?'')'

Convenient, isn't it? But I won't get into any more detail on these. Gregory Larsen does a good job of that in the article linked above.

What I'd like to show instead is a very simple modification I've made to sp_MSforeachtable. It's great to loop over tables and databases, but sometimes we want to loop over routines (a collective term for procedures, functions, triggers, and views) instead. Perhaps you want to grant pemissions to a user. Or perhaps you want to roll out some TSQLMacro updates to every routine in the database instead of just one, as is supported by the current version of the framework... And now you know how it will be done in the next version.

Presenting sp_foreachroutine:

CREATE PROCEDURE dbo.sp_foreachroutine
	@command1 nvarchar(2000), 
	@replacechar nchar(1) = N'?', 
	@command2 nvarchar(2000) = null,
	@command3 nvarchar(2000) = null, 
	@whereand nvarchar(2000) = null,
	@precommand nvarchar(2000) = null, 
	@postcommand nvarchar(2000) = null,
	@routinetype nvarchar(20) = null
AS
BEGIN
	/* This proc returns one or more rows for each procedure (optionally, matching @where), 
		with each procedure defaulting to its own result set */
	/* @precommand and @postcommand may be used to force a single result set via a temp table. */

	/* Preprocessor won't replace within quotes so have to use str(). */
	declare @mscat nvarchar(12)
	select @mscat = ltrim(str(convert(int, 0x0002)))

	if (@precommand is not null)
		exec(@precommand)

	/* Create the select */

	declare @sql nvarchar(4000)
	set @sql =
		N'declare hCForEach cursor global for ' 
		 + N' select ''['' + REPLACE(user_name(uid), N'']'', N'']]'') + '']'' + ''.'' + ' 
			+ N' ''['' + REPLACE(object_name(id), N'']'', N'']]'') + '']'' ' 
		 + N' from dbo.sysobjects o '
	         + N' where OBJECTPROPERTY(o.id, N''IsMSShipped'') = 0 '
		 + 	CASE @routinetype
				WHEN 'procedure' THEN ' and OBJECTPROPERTY(o.id, N''IsProcedure'') = 1 '
				WHEN 'function' THEN ' and (OBJECTPROPERTY(o.id, N''IsScalarFunction'') = 1 '
					+ ' or OBJECTPROPERTY(o.id, N''IsTableFunction'') = 1) '
				WHEN 'view' THEN ' and OBJECTPROPERTY(o.id, N''IsView'') = 1 '
				WHEN 'trigger' THEN ' and OBJECTPROPERTY(o.id, N''IsTrigger'') = 1 '
				ELSE ' and ( ' 
					+ ' OBJECTPROPERTY(o.id, N''IsProcedure'') = 1 '
					+ ' or OBJECTPROPERTY(o.id, N''IsScalarFunction'') = 1 '
					+ ' or OBJECTPROPERTY(o.id, N''IsTableFunction'') = 1 '
					+ ' or OBJECTPROPERTY(o.id, N''IsView'') = 1 '
					+ ' or OBJECTPROPERTY(o.id, N''IsTrigger'') = 1 '
					+ ' ) '
			END
	         + COALESCE(@whereand, '')

	exec(@sql)
	declare @retval int
	select @retval = @@error
	if (@retval = 0)
		exec @retval = sp_MSforeach_worker @command1, @replacechar, @command2, @command3

	if (@retval = 0 and @postcommand is not null)
		exec(@postcommand)

	return @retval
END
GO

Regular readers of this blog will note that the formatting isn't consistent with my usual standards. But since this was a port from an MS-written proc, I decided to keep things fairly consistent with what was already there. I've also added an additional parameter that wasn't present in sp_MSforeachtable: @routinetype, which lets the user select a specific type of routine to loop over. So, for instance, if you only want views, pass in 'view'. Same for functions ('function'), triggers ('trigger') and procedures ('procedure'). Pass in any other value -- or leave it NULL -- and you'll get all routines in the database.

This procedure keeps the sp_ prefix on purpose; it's meant to be created in the master database, and makes use of the MS-shipped sp_MSforeach_worker stored procedure, which lets it do its work.

Using it is simple. ? is the default substitution character (this can be changed using the @replacechar parameter). So to print a list of all routines in the current database, use:

EXEC sp_foreachroutine 'print ''?'''

For just functions, use the optional @routinetype parameter:

EXEC sp_foreachroutine 'print ''?''', @routinetype = 'function'

Enjoy!

SQL Server 2005 T-SQL: Aggregates and the OVER clause

Adam Machanic — Thu, 13 Jul 2006 01:44:00 GMT

A new feature added to SQL Server 2005 for the sake of the windowing functions is the OVER clause. Using this clause, you can specify ordering or partitioning for the windowing functions. For instance, to enumerate the names of all of the products in the AdventureWorks database that have a list price, along with their list prices and the rank of those prices compared to all of the other prices, the following query can now be used:

SELECT
	P.Name,
	P.ListPrice,
	DENSE_RANK() OVER (ORDER BY P.ListPrice DESC) AS PriceRank
FROM Production.Product P
WHERE 
	ListPrice > 0
ORDER BY 
	P.Name ASC



Name			List Price	PriceRank
-------------------------------------------------
All-Purpose Bike Stand	159.0000	44
AWC Logo Cap		8.9900		98
Bike Wash - Dissolver	7.9500		99
Cable Lock		25.0000		88
Chain			20.2400		93
Classic Vest, L		63.5000		66
Classic Vest, M		63.5000		66
Classic Vest, S		63.5000		66
Fender Set - Mountain	21.9800		91
Front Brakes		106.5000	55
Front Derailleur	91.4900		59
...

So what does this tell us? All-Purpose Bike Stand is the 44th most expensive item sold by AdventureWorks. AWC Logo Cap is the 98th most expensive item. And the Vests are tied for 66th most expensive. Which is why DENSE_RANK was used for this example! But really, this example is only here to demonstrate one use of the OVER clause. And this post isn't about windowing functions or rankings at all. That's another post for another day.

What this post is about is normal, non-windowing aggregate functions. Like SUM(). It turns out that the OVER clause can be used for them, too!

Pretend that you're an employee of AdventureWorks and your manager comes to you with a request: Write a query to return all of the products, their prices, their subcategories, and the average price for all products in the subcategory that any given product belongs to... Why? Perhaps the manager wants to re-categorize products based on whether they fall, percentage-wise, close to the same average price. Or maybe it just makes a good contrived example for showing this feature! Regardless...

Here's how you can solve this in SQL Server 2000:

SELECT
	P.Name AS ProductName,
	P.ListPrice,
	PS.Name AS ProductSubCategoryName,
	x.AveragePrice
FROM Production.Product P
JOIN Production.ProductSubCategory PS ON P.ProductSubCategoryID = PS.ProductSubCategoryID
JOIN
(
	SELECT 
		P2.ProductSubCategoryID,
		AVG(P2.ListPrice) AS AveragePrice
	FROM Production.Product P2
	WHERE 
		P2.ProductSubCategoryID IS NOT NULL
	GROUP BY 
		P2.ProductSubCategoryID
) x ON x.ProductSubCategoryID = P.ProductSubCategoryId
ORDER BY 
	P.Name

I don't know about you (since I have no clue who you are), but I personally have a difficult time reading this. If I came back to this query in six months, it would take me a few minutes to figue out what was going on. And doesn't it feel like there should be a more efficient way of expressing it?

...Well, now there is...

SELECT
	P.Name AS ProductName,
	P.ListPrice,
	PS.Name AS ProductSubCategoryName,
	AVG(P.ListPrice) OVER (PARTITION BY P.ProductSubCategoryID)
FROM Production.Product P
JOIN Production.ProductSubCategory PS ON P.ProductSubCategoryID = PS.ProductSubCategoryID
ORDER BY 
	P.Name

So what's going on here? Under the covers, SQL Server builds a subquery for the average, based on the partitioning column of the OVER clause -- which in this case is ProductSubCategoryID. It's a little bit less efficient in this case than the derived table approach, but a lot cleaner from a readability standpoint. Personally, I think it's a really cool feature, although I don't honestly see myself using it too often.

More ways to express yourself using SQL Server 2005. Madonna would be proud.