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


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

Regardless of your .NET language of choice, managing data is a vital skill for most applications.

Tip 10: SQL Server 2005 TOP N
Anyone who has written SQL TOP N queries where N is a variable knows that in SQL 2000, you had to either use dynamic SQL, or set the ROWCOUNT (and reset it afterwards). Why? Because SQL 2000 treated the N as a literal.

   -- Dynamic SQL to implement variable TOP N
   DECLARE @nTop int
   SET @nTop = 5
   DECLARE @cSQL nvarchar(100)
      CAST(@nTop AS VARCHAR(4)) +
      ' * FROM ORDERS ORDER BY Freight DESC'
   -- Setting ROWCOUNT to implement variable TOP N
   DECLARE @nTop int
   SET @nTop = 5
   -- Get the top 5 with the most Freight 
   -- Set back to 0
Fortunately, Microsoft enhanced the implementation of TOP N in SQL Server 2005 so that developers can treat the N as a variable, directly in the SQL statement.

   -- New implementation in SQL Server 2005
   DECLARE @nTop int
   SET @nTop = 5
   SELECT TOP (@nTop) * FROM Orders 
   ORDER BY Freight DESC
   -- The N can even be the result
   -- of another function
Developers can also use variable TOP N in UPDATE, INSERT, and DELETE statements!

   DECLARE @nTop int
   SET @nTop = 100
   UPDATE TOP (@nTOP) Orders 
   SET Freight = Freight * 1.1
Tip 11: SQL Server 2005 PIVOT
In the March/April 2005 issue of CoDe Magazine, I devoted a Baker's Dozen article to productivity tips for T-SQL 2000. One of the tips presented code to build a result set for an accounting aging report, by using a CASE statement to place overdue invoice rows into aging bracket columns based on date range.

In essence, the example turned, or pivoted, rows into columns based on some criteria-in this instance, date range. While using CASE statements remains a valid approach, SQL Server 2005 provides new PIVOT capabilities to simplify the process.

Listing 1 demonstrates a full example of the new PIVOT capability. In the example, you calculate the number of overdue days between each invoice and an "as-of" date, and place the invoice amount into the corresponding bracket with the PIVOT statement.

Tip 12: Recursive Queries and Common Table Expressions in SQL Server 2005
One of the more interesting new capabilities in SQL Server 2005 is the ability to build recursive queries. As the name implies, recursive queries actually query against their own results as part of the entire process. For example, you might query against hierarchical data to extract a variable number of parent-child relationships.

Let me demonstrate recursive queries and common table expressions (CTEs) with a simple example. Listing 2 shows two simple examples of querying up and down a hierarchy. The process involves two parts. The main or "anchor" query pulls the initial result set into a CTE. The CTE in many ways resembles a derived table. The second part recursively queries the CTE for either parents or children, depending on how you write the query. By default, SQL Server 2005 allows 100 levels of recursion, though you can configure this threshold.

Tip 13: APPLY the Results of a Table-Valued UDF in SQL Server 2005
I have to admit, this is one of my favorite new features in T-SQL 2005. The name perfectly describes what it does-it allows a developer to directly APPLY the results of a table-valued UDF in a SQL SELECT statement, without need for temporary tables.

While T-SQL doesn't lend itself to the level of program modularity that C# or Visual Basic developers enjoy, the new APPLY operator improves the integration of reusable table-valued UDFs with the different stored procedures that call them.

Let's consider the following table-valued UDF, which returns a table variable of the TOP N orders for a given customer, based on Order Amount.

   CREATE FUNCTION [dbo].[GetTopNOrders]
      (@CustomerID AS varchar(10), @nTOP AS INT)
      SELECT TOP(@N) OH.OrderID, CustomerID, OrderDate,
         (UnitPrice  * Quantity) as Orderamount
      FROM Orders OH
         JOIN [dbo].[Order Details] OD 
         ON OH.OrderID = OD.OrderID
      WHERE CustomerID =  @CustomerID
Now suppose you want to query against the entire customer database, and for each customer, run the UDF to return the TOP N orders. SQL Server 2005 allows you to APPLY the results of the UDF directly against a query into the customer file.

   DECLARE @nTopCount int
   SET @nTopCount = 5
   SELECT TOPOrd.CustomerID, TOPOrd.OrderID, 
      TOPOrd.OrderDate, TOPOrd.OrderAmount  
   FROM Customers
      CROSS APPLY   
            @nTopCount) AS TOPOrd
   ORDER BY TOPOrd.CustomerID,TOPOrd.OrderAmount DESC
On the Menu Next
As stated at the beginning of the article, this is the first of a two-part series on data handling capabilities. In the next issue, part two will cover .NET generics as well as the new ObjectDataSource tool in ASP.NET, and some more examples in T-SQL 2005. Stay tuned!

Closing Thoughts
You can find the entire source code on my website. For additional information, check out my blog.

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 (www.TheBakersDozen.net) for follow-up tips and notes on Baker's Dozen articles…and maybe a few additional treats!

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