Kevin S. Goff: Stages of Data

4/13/07: Processing data using ADO.NET in VS2005

I average about 15-20 speaking engagements a year. Regardless of the topic, my first PowerPoint slide is a picture of Seargent Joe Friday and Officer Bill Gannon (from the old TV show Dragnet), with the caption "Just the Facts". It's a segue to my philosophy on presentations….plenty of code….and just the facts.

I've covered data handling in stored procedures and ADO.NET in many articles and in my book. I also talk to many people about data-handing. And that's today's topic. I've uploaded a data-handling sample onto my server, ADOProcessingSample.zip.

It's a contrived example from a very common "real-world" situation: reading customer invoices and producing a result set for an aging receivables report. Normally, I'd use the new PIVOT capability in SQL Server 2005 to read invoices into an analytical view for an aging report – but sometimes people can't use (or don't want to use) stored procedures. Maybe all they can do is query raw invoices back into a datatable, and then read through them and put them into the corresponding aging bracket, based on the date of the invoice and the "as of" date.

The sample is a simple .NET project, built using VS2005. The sample demonstrates the following:

• Adding data to strongly-typed rows and looping through the rows
• Performing date math
• Filtering
• Equivalent of a SELECT DISTINCT to get a unique list of accounts from a table
• Computing Subtotals

It also demonstrates other tasks, such as determining which aging bucket to use based on a date range in a table (as opposed to hard-coding).

Now, there's actually a second (and possibly more efficient) way to produce the result set for the report, by looping instead through the smaller bracket range and then filtering on invoices that match the date range. I've intentionally started with the "brute force" approach, to demonstrate some of the ADO.NET constructs that would be necessary in other situations. My next post will provide a second solution, and will use a much much larger dataset.

More reading:

• My book: Pro VS2005 Reporting using SQL Server and Crystal Reports
• Code Magazine article #1 (from 2006) on ADO.NET and data handling
• Code Magazine article #2 (from late 2005) on ADO.NET and data handling

KG

4/5/07: True or False?

True or False: "Solving problems that are hierarchical and nested in nature are best helped with a record oriented approach rather than a SET ORIENTED approach"

This assertion comes from the Visual FoxPro environment, where developers can navigate through result sets (VFP cursors…not to be confused with SQL Server Cursors) at the record level, very quickly and easily. No question, VFP is a great database tool. So great in fact, that some have used it for so long, and to solve so many problems, that it's easy to conclude that VFP's offerings are the only optimal hammer in the toolbox for certain operations.

SQL Server 2005 conforms more closely to the ANSI-99 SQL spec – and some of the features of the ANSI-99 standard making querying more flexible than before. One such feature is Common Table Expressions and recursive querying of hierarchical data. I'm going to devote some blog entries over the next few weeks to recursive querying, as I believe developers regularly face this challenge.

I'm going to start with a very simple example, to demonstrate the mechanics of recursive querying in SQL 2005. First, here's a simple set of hierarchical data – a product hierarchy with Brands, Groups, and Items. Each row contains a unique integer identifier, a description, and a reference to the row's parent:

DECLARE @tProducts TABLE (ID int, Name char(50), ParentID int)

INSERT INTO @tProducts VALUES (1, 'Brand 1', null)
INSERT INTO @tProducts VALUES (2, 'Brand 2', null)
INSERT INTO @tProducts VALUES (3, 'Brand 3', null)

INSERT INTO @tProducts VALUES (6, 'Brand 1, Group 1', 1)
INSERT INTO @tProducts VALUES (7, 'Brand 1, Group 2', 1)
INSERT INTO @tProducts VALUES (8, 'Brand 1, Group 3', 1)

INSERT INTO @tProducts VALUES ( 9, 'Brand 2, Group 1', 2)
INSERT INTO @tProducts VALUES (10, 'Brand 2, Group 2', 2)

INSERT INTO @tProducts VALUES ( 11, 'Brand 3, Group 3', 3)

INSERT INTO @tProducts VALUES (12, 'Brand 1, Group 1, Item 1', 6)
INSERT INTO @tProducts VALUES (13, 'Brand 1, Group 1, Item 2', 6)

INSERT INTO @tProducts
    VALUES (14, 'Brand 1, Group 1, Item 1, SKU 1', 12)

Here's the challenge: for any specific item in this table, I want to know all the child records, going all the way down the hierarchy. Think about how you'd handle this query in SQL 2000 using SQL statements. While it was doable, querying hierarchical data was never an easy task. Fortunately, SQL 2005 makes this much simpler. Here's the entire query, which sets a search string, and then retrieves all the child records for that search string:

DECLARE @cSearch char(50)
SET @cSearch = 'Brand 1, Group 1'

; WITH ProductCTE AS
    -- Anchor query
    (SELECT ID, Name, ParentID
            FROM @tProducts
            WHERE Name = @cSearch
        UNION ALL
            -- Recursive query
            SELECT Prod.ID, Prod.Name, Prod.ParentID
                FROM @tProducts Prod
                    INNER JOIN ProductCTE
                    ON ProductCTE.ID = Prod.parentID )

SELECT * FROM ProductCTE

I'll confess, when I first looked at CTE and recursive query syntax, I felt pretty stupid that I couldn't grasp it. So, I took an online example, completely changed the sample data, built my own example, and then I didn't feel quite so stupid. ;)

Here's the deal/trick with recursive queries - in a "normal" SQL SELECT statement, you're querying from a table that isn't changing during the lifetime of the query (OK, data could be inserted from another session, but that's a different topic). You're querying directly FROM one table, and into a totally separate result set.

In a recursive query, some of the data you need to query is actually a result of a prior phase of the query. So results become the source for future results. Think about this with respect to our simple example, where we want all the children of a particular row – we can't pull the lowest level (SKU) without pulling the level above that (Item), and we can't do that without pulling the level above that (Group) and so on.

So let's take a look at the anatomy of the query, which has three parts. The first part is defining the Common Table Expression…

; WITH ProductCTE AS

As I said in my last blog post, a CTE is like a temporary derived table or view, with a very short lifespan. What we're going to do in the next two steps is query into ProductCTE for the search string, and then continue to query all the way down the hierarchy by comparing IDs to ParentIDs.

The second part is called the main or anchor query:

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

The anchor query executes first, so we know that ProductCTE will contain the ID, Name, and ParentID for the single row that matches the search condition.

The final part is the actual recursive query, which is connected to the anchor query with a UNION ALL:

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

Note that the anchor query is performing a join between the original list of products and the ProductCTE. So each "hit" between an ID and a ParentID will go into ProductCTE, and the query will continue until no more matches are found.

OK, so that handles querying DOWNWARD….suppose we wanted to find all the parent rows instead? Just switch the column names on the INNER JOIN in the recursive query:

                    ON ProductCTE.ParentID = Prod.ID )

If you're wondering about any limit to the number of recursions, the default is 100, which can be configured: check out MAXRECURSION in the online help if you ever have a hierarchy with more than 100 levels. (I'm almost afraid to see a hierarchy with that many levels!)

See, that wasn't so bad? There are many examples of hierarchical data out there, such as Bill of Material data, organization chart data, etc. Anyone who has ever worked with e-commerce applications is likely VERY familiar with the challenges of hierarchical data. SQL Server 2005 makes life much easier to deal with these challenges.

More on this later…KG

4/4/07: Another use of T-SQL 2005…

When it comes to retrieving data, I'm a firm believer in using SQL/T-SQL and stored procedures as much as possible.

I've been debating someone on an online forum about the use of SQL/T-SQL syntax to handle retrieval requirements that don't appear to be SET-oriented. The person I'm debating proposed the following challenge as an example where the SQL language is allegedly not very efficient:

The example is a client with multiple invoices, where you need to determine the specific invoice that exceeds a specific cumulative balance. Here was an example of a post-query "data munging" approach in the database tool Visual Foxpro:

SEEK nDebtor
nDebt = 0
SCAN WHILE Debtor = nDebtor AND nDebt < 1000
    nDebt = nDebt + DebtAmt
ENDSCAN

Of course, this isn't a particularly challenging task, but it is an example to demonstrate some of the great features in T-SQL 2005: in this case, Common Table Expressions and the ability to APPLY a table-valued UDF directly to a SQL SELECT query.

First, let's create some data:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Invoice]') AND type in (N'U'))
DROP TABLE [dbo].[Invoice]
GO

CREATE TABLE Invoice (InvPK int identity,InvoiceNum Char(20),
                    DebtorID int, InvoiceDate DateTime,
                    InvoiceAmount decimal(14,2))

INSERT INTO Invoice
    VALUES ('111',1,CAST('1-1-2007' AS DateTime), 500.00)
INSERT INTO Invoice
    VALUES ('222',1,CAST('1-6-2007' AS DateTime), 200.00)
INSERT INTO Invoice
    VALUES ('333',1,CAST('2-13-2007' AS DateTime),350.00)
INSERT INTO Invoice
    VALUES ('444',1,CAST('2-14-2007' AS DateTime),350.00)

Second, let's write a table-valued UDF that returns the cumulative sum of invoices for a customer, based on a current date. The final query will utilize this table-valued UDF to determine the cumulative balance for each date for which an invoice exists, and see if the cumulative balance exceeds a specific threshold.

IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[GetExceedingInvoice]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetExceedingInvoice]
GO

CREATE FUNCTION dbo.GetExceedingInvoice
            (@DebtorID INT,
            @CutOffDate DATETIME)

RETURNS @tMaxInvoice TABLE
    (InvoiceDate DATETIME,InvoiceAmount NUMERIC(14,2))
AS
BEGIN
    INSERT INTO @tMaxInvoice
         SELECT MAX(InvoiceDate) AS InvoiceDate,
                SUM(InvoiceAmount) AS InvoiceAmount
            FROM Invoice
                WHERE DebtorID = @DebtorID AND
                    InvoiceDate <= @CutOffDate
RETURN
END
GO

Because we're writing this as a table-valued UDF, we can utilize it from any other query/stored procedure in the database.

Finally, we can build the body of the query. We'll start by setting some parameters for the Customer (Debtor) and the threshold:

DECLARE @DebtorID int
DECLARE @Threshold decimal (14,2)
SET @DebtorID = 1
SET @Threshold = 1000.00

Next we'll build a CTE (common table expression) to retrieve and store the invoices for the specific debtor. If you're not familiar with CTEs, you can think of them as a temporary dynamic view or a derived table, but one that only stays in scope for the next SQL statement.

; WITH InvCTE as (
SELECT TOP 100 PERCENT * FROM Invoice
        WHERE DebtorID = @DebtorID ORDER BY InvoiceDate )
        -- you need want to an ORDER BY
        -- in a derived table, subquery, CTE,
        -- you need to use TOP 100 Percent

Last but certainly not least, the next statement will take the CTE (InvCTE), and APPLY the table-valued UDF (GetExceedingInvoice) to determine the specific invoice and invoice date (if any) that exceeds the cumulative balance to date:

SELECT TOP 1
    InvCTE.InvoiceNum,MaxInv.InvoiceDate,MaxInv.InvoiceAmount
        FROM InvCTE
        CROSS APPLY
            dbo.GetExceedingInvoice(@DebtorID,InvoiceDate) MaxInv
        WHERE MaxInv.InvoiceAmount >= @Threshold
                AND DebtorID = @DebtorID   

This will return the invoice that exceeded the threshold, along with the invoice date and the sum of customer invoices to that point. There are surely several ways to do this: this was a full first pass.

In conclusion, there may be data retrieval requirements that initially "appear" to be beyond what T-SQL can handle with a SET-oriented approach. Sometimes it may seem easiest to just pull some of the data into an application record set and munge through it sequentially. But hopefully this little exercise demonstrates that T-SQL 2005 does indeed provide the necessary functions to do it all in stored procedures using a SET-based approach.

Time for some beer and ice cream…KG