RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


The Baker's Dozen Doubleheader: 26 Productivity Tips for Managing Data (Part 2 of 2) : Page 5

The conclusion of a two-part article on managing data with Visual Studio 2005 and SQL Server focuses on .NET generics, provides an introduction to the new ObjectDataSource class, and demonstrates some additional new capabilities in T-SQL 2005.

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 shown in the following code:

   -- 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'
        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)
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 6 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.

   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.
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 (see the top part of Listing 7). 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 7).

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.

Listing 8 and Listing 9 demonstrate some of the capabilities in working with XML data. Listing 8 shows different ways to insert XML data into standard columns. Listing 9 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!)

Tip 13: Removing a Restriction on Table Variables in SQL 2005
Microsoft introduced table variables in SQL Server 2000 to the delight of many database developers. However, table variables carried a few restrictions. One restriction was that developers could not insert the results of a stored procedure directly into a table variable. So the following code would not work in T-SQL 2000:

   INSERT @tTable EXEC <sp_mystoredproc> 
Fortunately, T-SQL 2005 lifts this restriction. I can now execute a stored procedure directly into a table variable (see Listing 10).

Random Musings on Data
I probably sound like a broken record, but at the end of the day, much of our lives are about handling data. As I've shown in this two-part series, Visual Studio 2005 and SQL Server 2005 make our lives easier when working with data-either with major enhancements like generics and the SQL PIVOT command, or more subtle but important changes so that I can filter on distinct rows in ADO.NET.

If you are a developer maintaining Visual Studio 2003 code in the Visual Studio 2005 environment, you may not be able to take advantage of these new data-handling capabilities right away. But you can still study the new functions as time permits, build prototypes with them, and prepare yourself for when the time comes!

Recommended Reading
Ever wish you could just lock yourself in a nice cool room for a week and research all these new capabilities? Unfortunately, the demands of the world often make that difficult. However, if you can spare a few hours, here are some great references

First, CoDe Magazine has run some outstanding articles on T-SQL and XML in SQL Server 2005. Jim Duffy wrote an article in the January/February 2005 issue of CoDe, titled "SQL Server 2005 T-SQL Enhancements." Jim is an outstanding speaker and a well-rounded technology guru.

Shawn Wildermuth has an outstanding article in the May/June 2006 issue of CoDe, titled "Making Sense of the XML DataType in SQL Server 2005." I'd vote for this as article of the year, if such an award existed. Google Shawn's name and you'll find many tremendous online articles on ADO.NET.

Second, Manuel Abadia has written some online articles on the ObjectDataSource. He has also written his own class, called ExtendedObjectDataSource. Check out Manuel's content.

Finally, the book, "Professional .NET 2.0 Generics" (Wiley Publishing) by Tod Golding is excellent and covers generics in detail. The author presents code in both C# and VB.NET.

Next Issue: Closing Thoughts
Have you ever submitted something (an article, a paper, some code, etc.) and thought of some good ideas after the fact? Well, I'm the king of thinking of things afterwards. Fortunately, that's the type of thing that makes blogs valuable. Check my blog for follow-up tips and notes on Baker's Dozen articles…and maybe a few additional treats!

Article Source Code
You can find the full source code for this article on my Web site.

Kevin S. Goff is the founder and principal consultant of Common Ground Solutions, a consulting group that provides custom web and desktop software solutions in .NET, Visual FoxPro, SQL Server, and Crystal Reports. Kevin has been building software applications for 17 years. He has received several awards from the U.S. Department of Agriculture for systems automation. He has also received special citations from Fortune 500 companies for solutions that yielded six-figure returns on investment. He has worked in such industries as insurance, accounting, public health, real estate, publishing, advertising, manufacturing, finance, consumer packaged goods, and trade promotion. In addition, Kevin provides many forms of custom training.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date