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)
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!)