CODE Magazine - Article: The Baker’s Dozen Doubleheader: 26 Productivity Tips for Managing Data (Part 2 of 2)

Reader rating:
Click here to read 4 comments about this article.
Article source: CoDe (2006 - Sep/Oct)

Article Pages: < Previous - 1 2 3 4 5 6 - Next >

The Baker’s Dozen Doubleheader: 26 Productivity Tips for Managing Data (Part 2 of 2) (Cont.)

Tip 9: Using the SQL Server COALESCE Function

Suppose I have a form (or Web page) that allows users to retrieve customers based on several input criteria, such as first and last name, address, city, zip, etc. The user may enter one field or multiple fields. I need to write a stored procedure to examine all possible parameters, but only query on those that the user entered.

I could write a stored procedure that examines each parameter, constructs a SQL SELECT string based on parameters the user entered, and execute the string using dynamic SQL. Many SQL developers opt for this approach.

Alternatively I can use the SQL Server COALESCE function as I’ve done in Listing 11. COALESCE is available both in SQL Server 2000 and SQL Server 2005, and gives me an alternative approach that arguably leads to cleaner T-SQL code. For each search condition, I pass COALESCE two values: the search variable, and a value to use if the search variable is NULL. So for any search values that the user did not specify, the search defaults to the column being equal to itself. This approach is still very fast, even when querying against millions of rows.

Tip 10: Ranking in SQL 2005 within a Group

SQL Server 2005 introduced a ROW_NUMBER function to rank result sets. I can also rank results within a group. Suppose I want to rank the top orders by customer, in descending sequence (for orders greater than $500).

Listing 12 shows how I can query the Northwind Orders database for orders greater than $500. I assign a ranking number for each set of customer orders. In the ROW_NUMBER OVER statement, I can define the PARTITION (in this context, somewhat like a group-level definition) as well as the Order to use when ranking the result set.

ROW_NUMBER() OVER (PARTITION BY CUSTOMERID
ORDER BY (UnitPrice * Quantity) DESC)
AS OrderRank

Tip 11: More on T-SQL 2005: Apply, UDFs, and Table-Valued UDFs in Correlated Subqueries

T-SQL 2000 is a bit awkward at allowing developers to integrate table-valued UDFs into queries. Often, developers must query these UDFs into a temporary table and then use the temporary table.

T-SQL 2005 allows developers to more efficiently integrate table-valued UDFs into queries. In Part 1 of this series, I showed an example that directly applied the results of a table-valued UDF in the same way I would use a derived table, using the new APPLY operator. The example contained a UDF that returned the TOP N orders from the Northwind database for a specified customer, and applied that directly against all customers in a query.

Here I’ll cover another instance where I can use table-valued UDFs more directly. T-SQL 2005 allows me to use a table-valued function in a subquery, where the arguments to the UDF come from columns in the outer query.

Using the Northwind database, suppose I want to know which customers have had at least two orders for more than $5,000 (or five orders for more than a $1,000, etc.). So our first step is to build a table-valued UDF called GetCustOrders_GT_X (the top part of Listing 13). The UDF contains two parameters (customer ID and dollar threshold), and returns a table variable of orders for that customer that exceed the threshold.

In the next step I run that UDF against every customer in the database, and determine which customers have at least two orders from the UDF. Ideally, I’d like to construct a subquery to pass each customer as a parameter to the UDF. Here’s where the power of T-SQL 2005 comes in.

In SQL Server 2000, table-valued functions within a correlated subquery could not reference columns from the outer query. Fortunately, SQL Server 2005 removes this restriction. I can now build a subquery that uses the UDF and passes columns from the outer query as arguments to the UDF (remainder of Listing 13).

Tip 12: SQL 2005 XML Enhancements

SQL Server 2000 contains a tremendous amount of functionality for dealing with XML: SQL Server 2005 extends XML with the new XML data type.

Listings 14 and 15 demonstrate some of the capabilities in working with XML data. Listing 14 shows different ways to insert XML data into standard columns. Listing 15 demonstrates how to search for strings inside an XML column. (Special thanks to the regulars in the Microsoft SQL Server newsgroup for help with the syntax on that one!)

Listing 11: Using COALESCE instead of dynamic SQL

-- This will work in both SQL2000 and SQL2005
-- You can use COALESCE to query on only those search values
-- that are not NULL

DECLARE @city varchar(50), @state varchar(50), @zip varchar(50),
        @FirstName varchar(50), @LastName varchar(50),
        @Address varchar(50)

SET @FirstName = 'Kevin'
SET @State = 'NY'

SELECT * FROM CUSTOMERS WHERE
                 FirstName = COALESCE(@FirstName,FirstName) AND
                 LastName = COALESCE(@LastName,LastName) AND
                 Address = COALESCE(@Address,Address) AND
                 City = COALESCE(@City,City) AND
                 State = COALESCE(@State,State) AND
                 Zip = COALESCE(@Zip,Zip)

Listing 12: Using ROW_NUMBER and PARTITION to rank within levels

SELECT  CustomerID, OH.OrderID, OrderDate,
  (UnitPrice  * Quantity) as Orderamount,
   ROW_NUMBER() OVER (PARTITION BY CUSTOMERID
    ORDER BY  (UnitPrice  * Quantity) desc ) AS OrderRank
   FROM Orders OH
      JOIN [dbo].[Order Details] OD
            ON OH.OrderID = OD.OrderID
      WHERE  (UnitPrice * Quantity) > 500
        ORDER BY CUSTOMERID, OrderAmount  DESC

-- Results, OrderRank partitioned (reset) on each Customer

ALFKI   10692   1997-10-03 00:00:00.000   878.00   1
ALFKI   10835   1998-01-15 00:00:00.000   825.00   2

ANTON   10535   1997-05-13 00:00:00.000   1050.00   1
ANTON   10677   1997-09-22 00:00:00.000   936.90   2
ANTON   10535   1997-05-13 00:00:00.000   825.00   3
ANTON   10573   1997-06-19 00:00:00.000   820.00   4
ANTON   10573   1997-06-19 00:00:00.000   702.00   5

AROUT   10953   1998-03-16 00:00:00.000   4050.00   1
AROUT   10558   1997-06-04 00:00:00.000   1060.00   2
AROUT   10707   1997-10-16 00:00:00.000   780.00   3

Listing 13: UDF inside a subquery

CREATE FUNCTION [dbo].[GetCustOrders_GT_X]
   (@CustomerID AS varchar(10), @nThreshold AS decimal(14,2))
RETURNS @tOrders TABLE (OrderID int, CustomerID varchar(10),
                     OrderDate datetime, OrderAmount decimal(14,2))
AS
BEGIN
   INSERT INTO @tOrders
          SELECT  OH.OrderID, CustomerID, OrderDate,
                  (UnitPrice  * Quantity) as Orderamount
   FROM Orders OH
      JOIN [dbo].[Order Details] OD
            ON OH.OrderID = OD.OrderID
           WHERE CustomerID =  @CustomerID AND
             (UnitPrice  * Quantity)  > @nThreshold
   ORDER BY OrderAmount  DESC
RETURN
END

Go

DECLARE @nNumOrders int, @nMinAmt decimal(14,2)
SET @nNumOrders = 2
SET @nMinAmt = 5000.00

SELECT CustomerID FROM Customers
     WHERE  (SELECT COUNT(*) FROM
     DBO.GetCustOrders_GT_X(CustomerID,@nMinAmt)) >=@nNumOrders

-- ResultsHUNGO
QUICK
ERNSH
SAVEA
RATTC

-- To get the actual orders > 5000 for these 5 customers,
-- we can turn the query above into a derived table, and then
-- use a CROSS APPLY

SELECT MaxOrders.* FROM
   (SELECT CustomerID FROM Customers
      WHERE  (SELECT COUNT(*)  FROM
        dbo.GetCustOrders_GT_X(CustomerID,@nThresholdAmt)) >=
          @nNumOrders) as CustList
   CROSS APPLY GetCustOrders_GT_X(CustomerID,@nThresholdAmt)
     AS MaxOrders

Listing 14: Inserting XML data into standard columns

-- First Example of inserting XML into a table of columns
DECLARE @cXMLDoc XML
declare @hdoc int
SET @cXMLDoc = '<AddressType  >
   <AddressRecord
       AccountID = "1"
       Street="31 Main Dr" City="Philly" State="PA" Zip="12345"/>
   <AddressRecord
       AccountID = "2"
       Street="1 Wilson Dr" City="Newark" State="NJ" Zip="22222"/>
</AddressType>'

EXEC sp_xml_preparedocument @hdoc OUTPUT, @cXMLDoc

SELECT * FROM OPENXML (@hdoc, '/AddressType/AddressRecord',1)
  WITH (AccountID int,Street varchar(100), City varchar(100),
        State varchar(10), ZipCode varchar(13))

-- Second example of inserting XML into a table of columns
-- uses the Address tag to specify nested columns

DECLARE @cXMLDoc XML
declare @hdoc int
SET @doc = ' <customer>
<Customernum>48456</Customernum>
<Firstname>Kevin</Firstname>
<Lastname>Goff</Lastname>
<Address>
   <city>Allentown</city>
   <state>PA</state>
</Address>
</customer>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

DECLARE @tTemp TABLE (Customernum int, Firstname char(50),
    Lastname char(50), City char(50), State Char(10))

insert into @ttemp
  SELECT Customernum,firstname,lastname,city,state
  FROM OPENXML (@idoc, '/customer',2)
  WITH (Customernum int,Firstname varchar(50),Lastname varchar(50),
        city varchar(50) './Address/city',
        state varchar(50) './Address/state')

Listing 15: Searching within an XML data type

-- Performing a partial text search inside an XML column

declare @tTest table (address xml)

insert into @ttest values ('<Address >
   <AddrRecord
       AccountID = "1"
       Street="31 Main Dr" City="Newark" State="NJ" Zip="11111" />
   </Address>' )

insert into @ttest values ('<Address >
   <AddrRecord
       AccountID = "2"
       Street="1 Wilson Rd" City="Philly" State="PA" Zip="22222"/>
   </Address>' )

SELECT * FROM @ttest WHERE
Address.exist('/Address/AddrRecord [contains(@City,"hil")]') = 1

Article Pages: < Previous - 1 2 3 4 5 6 - Next Page: 'Tip 13: Removing a Restriction on Table Variables in SQL 2005' >>

Page 1:	The Baker’s Dozen Doubleheader: 26 Productivity Tips for Managing Data (Part 2 of 2)
Page 2:	Tip 2: Using Generics to Store a Custom Class
Page 3:	Tip 5: Using the List Class to Store Other Objects

Page 4:	Tip 8: An Overview of the ASP.NET 2.0 ObjectDataSource
Page 5:	Tip 9: Using the SQL Server COALESCE Function
Page 6:	Tip 13: Removing a Restriction on Table Variables in SQL 2005

How would you rate the quality of this article?

	1	2	3	4	5
Poor						Outstanding

Tell us why you rated the content this way. (optional)

Average rating:
4.4 out of 5

15 people have rated this article.