Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

The Baker's Dozen: 13 Productivity Tips for Transact-SQL 2005 : Page 2

SQL Server 2005 offers T-SQL language features that can improve your productivity.


advertisement
Tip 2: CTEs and Recursive Queries (1 of 2)
Scenario: You have a hierarchy of product items, groups, and brands, all in a single table. Each row has a parent key that points to the row's parent. For any single row, you want to know all the row's parent rows, or all the row's child rows.

Recursive queries are powerful—so powerful that you may not always see all their power at once. Next time you think you need to write code to loop through data, consider a CTE and a recursive query instead.
SQL Server 2000 developers commonly requested the ability to more easily query against hierarchical data. Many databases store hierarchical data, such as databases for menu options, bill of materials, and geographic hierarchies. E-commerce applications in particular will often store a company's product hierarchy in a single table, where each row contains a pointer key to the row's parent. The table might look something like the contents of Table 1.

Table 1: The table shows a sample product hierarchy for an E-commerce application.

PrimaryKey

ParentKey



Description

1

NULL

Brand A

2

NULL

Brand B

3

NULL

Brand C

 

 

 

4

1

Brand A, Group 1

5

1

Brand A, Group 2

6

2

Brand B, Group 3

 

 

 

7

4

Brand A, Group 1, Item a

8

6

Brand B, Group 3, Item b


Imagine writing even a simple query to determine all the parent or child records from the table, especially when there is no limit to the number of levels. Fortunately, the common table expression and recursive query capabilities in SQL Server 2005 make this very easy. The key is to use a set of language constructs that allow you to first retrieve your main or "core" selection(s), and then a second set of queries or processes that takes the result of your core selections and repeatedly traverses (either up or down) a hierarchy to find subsequent matches.

Listing 1 populates a simple product hierarchy, and shows how to use recursive query capabilities to determine all the parent and child rows for a specific row.

A basic recursive query consists of three parts:

First, you must define a common table expression (CTE). Note the semicolon at the beginning of the statement:

; WITH ProductCTE AS

Second, you construct your main or core (or "anchor") query:

(SELECT ID, Name, ParentID FROM @tProducts WHERE Name = @cSearch

So far, easy stuff. The major work occurs in the third step. You want to add a UNION ALL statement and construct a query that references the CTE. Notice that the query does a JOIN on the ProductCTE based on a match between each ID and the ParentID. SQL Server 2005 will continue to repeat this query (hence the name "recursive" until it exhausts all searches):

-- Recursive query SELECT Prod.ID, Prod.Name, Prod.ParentID FROM @tProducts Prod INNER JOIN ProductCTE ON ProductCTE.ID = Prod.parentID )

Now that you've seen a basic example of recursive queries, I'll move on to a second example that shows their power.

Tip 3: CTEs and Recursive Queries (1 of 2)
Scenario: You need to take a table of vendor orders and produce a flat result set for a weekly report or chart for the first quarter of 2004. The result set must contain one row for each vendor and week-ending date, with the sum of orders for that week (even if zero).

If you've ever written queries for weekly reports or weekly charts, you know that creating the full result set can be the trickiest part. Some reporting and charting systems require a matrix or even some form of a Cartesian product as the data source.

In the example above, you need to create a result set with one record per week per vendor account, even if the account did not have any orders for the week. You may be tempted to write a loop (either in T-SQL or in the application layer) to produce a table with the accounts and dates, and then join that against the orders themselves while searching for any orders between each week. However, you can also construct a recursive query to perform all the work in a stored procedure.

Before you write such a recursive query, you need to construct a function that will convert any date to an end of the week date. Orders may take place any day of the week, but you need to convert any date to a week-ending date (normally Saturday, as most businesses treat a business week as Sunday through Saturday. The following code is a SQL user-defined function (UDF) to convert any day to a Saturday date—so if you pass '6-13-2006' as a parameter, you'll get '6-16-2006' as the return value from the UDF:

ALTER FUNCTION [dbo].[GetEndOfWeek] (@dDate DateTime) -- Converts date to the Saturday date for the week -- So 6-12-07 becomes 6-16-07 RETURNS DateTime AS BEGIN DECLARE @dRetDate DateTime SET @dRetDate = @dDate + ( 7-DATEPART(WeekDay, @dDate)) RETURN @dRetDate END

Next, you need to construct a matrix of dates and vendors. The report calls for a weekly result set by vendor for the first quarter of 2004. Since you may not know the first Saturday in 2004 (nor the last Saturday), you can simply declare two variables for the '1-1-2004' and '3-31-2004' strings, and use the UDF to convert them to the actual Saturday dates:

DECLARE @StartDate DATETIME, @EndDate DATETIME SET @StartDate = dbo.GetEndOfWeek('1-1-2004') -- First Saturday SET @EndDate = dbo.GetEndOfWeek('3-31-2004') -- Last Saturday

After that, you'll next create two CTEs, one for a range of dates and a second for a list of vendors with at least one order. You can actually combine CTE declarations into a single large statement and delimit them with commas. The first CTE, DateCTE, will contain a row for each Saturday date in the date range. Again here is where you may be accustomed to writing some kind of loop to create a list. But now you can use recursive queries. To do so, you declare your CTE and set your main query, which simply does a SELECT of the single starting date:

; WITH DateCTE (WeekEnding) AS (SELECT @StartDate AS WeekEnding

At this point, the DateCTE has only one row in it, containing the '1-3-2004' value. The recursive query will repeatedly query DateCTE for values greater than 7 days beyond each entry, which are less than the ending date. So the recursive query will eventually build a row in DateCTE for '1-10-2004', '1-17-2004', etc., all the way to the ending date:

UNION ALL SELECT WeekEnding + 7 AS WeekEnding FROM DateCTE WHERE WeekEnding < @EndDate ),

Now that you've built the DateCTE, you can build a simple CTE for each vendor with at least one order:

VendorList (VendorID) AS (SELECT VendorID FROM Purchasing.PurchaseOrderHeader GROUP BY VendorID)

Finally, you can query against the two CTEs and the PO table to build the final result. Notice that the final query calls the GetEndOfWeek UDF to convert each OrderDate to a Saturday date, to match it up against the corresponding end of week date in DateCTE. Listing 2 contains the complete source code for the CTEs and the final query, but here's the relevant portion:

SELECT Matrix.VendorID,WeekEnding, SUM(COALESCE(Orders.TotalDue,0)) AS WeeklySales FROM (SELECT VendorID, WeekEnding FROM DateCTE, VendorList) AS Matrix LEFT JOIN Purchasing.PurchaseOrderHeader Orders ON dbo.GetEndOfWeek(Orders.OrderDate) = Matrix.WeekEnding AND Orders.VendorID = Matrix.VendorID GROUP BY Matrix.VendorID,Matrix.WeekEnding

Recursive queries are powerful—so powerful that you may not always see all their power at once. Next time you think you need to write code to loop through data, consider a CTE and a recursive query instead.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap