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


The Baker's Dozen: 13 Productivity Tips for Transact-SQL 2005 : Page 5

SQL Server 2005 offers T-SQL language features that can improve your productivity.

Scenario: You want to assign ranking numbers to your result set so that you can physically show the numbers 1 through 10 to annotate the top ten orders for each vendor. You also want to see other applications for partitioning.

Prior to SQL Server 2005, if you ever wanted to assign ranking numbers to a result set, you either had to create an identity column in a result set or manually loop through the results and assign a ranking number. If you needed to assign ranking numbers within a group (e.g. assign ranking number to orders by vendor), you know that the processes became even more involved.

Fortunately, SQL Server 2005 provides functionality to easily create a ranking column in a result set. The code below shows two examples of ranking: one for the entire result set, and a second within a group. You can use the new ROW_NUMBER() function to assign a ranking number, and the OVER (ORDER BY) clause to indicate how SQL Server should order the ranking logic:

   SET @nTop = 50
   SELECT  TOP(@nTop) 
      EmployeeID, PurchaseOrderID, OrderDate, TotalDue,  
      ROW_NUMBER() OVER ( ORDER BY  TotalDue DESC  ) AS OrderRank
   FROM Purchasing.PurchaseOrderHeader
   SELECT EmployeeID, PurchaseOrderID, OrderDate, TotalDue,  
          ROW_NUMBER() OVER
         (PARTITION BY EmployeeID ORDER BY TotalDue DESC) AS OrderRank
   FROM Purchasing.PurchaseOrderHeader
Note how the second example uses the PARTITION statement to assign ranking numbers by a certain group (EmployeeID, and then TotalDue descending within EmployeeID), you can use the PARTITION statement:

The PARTITION statement has other uses beyond ranking. While this would be an unusual case, suppose you wanted to execute an in-line summary of a column, or maybe to SUM and GROUP a specific column without dealing with the GROUP rule that all non-aggregate columns must be specified in a GROUP BY. Here's an example that shows how to create aggregate partitions:

   --Aggregate partitions as an alternative to GROUP BY
   SELECT PurchaseOrderID, PurchaseOrderDetailID,ProductID, 
        SUM(LineTotal) OVER (PARTITION BY PurchaseOrderID) AS POTotal,
       COUNT(*) OVER (PARTITION BY PurchaseOrderID) AS POItemCount,
       SUM(LineTotal) OVER (PARTITION BY ProductID) AS ProductTotal, 
       COUNT(*) OVER (PARTITION BY ProductID) AS ProductItemCount 
   FROM Purchasing.PurchaseOrderDetail
   ORDER BY PurchaseOrderID
Finally, you can use the ROW_NUMBER function to implement custom paging of result sets in a web application. If a user query results in 500 rows, and you want to display only 20 at a time and implement custom paging logic, you can use the ranking functions to assign result set numbers and return offset ranges (e.g. rows 21-40, 41-60). I presented a full solution for this in the March-April 2007 issue of CoDe Magazine.

Scenario: You want to implement TRY…CATCH capabilities in SQL Server 2005 stored procedures in the same way you can implement TRY…CATCH capabilities in .NET. You also want to be able to RAISE an error back to the calling application.

Both database developers and DBAs will be happy to know that the TRY…CATCH language features in C# and Visual Basic are now available in T-SQL 2005. You can place nested TRY…CATCH statements inside stored procedures (though you cannot use FINALLY).

Listing 8 shows an example of a TRY…CATCH block of code that attempts to delete a row from the PO header table. The code will generate an error because of the constraint between the header and detail files. You want to catch the error and raise an exception back to the calling application so that any TRY…CATCH blocks in the application layer can detect and process the exception.

The CATCH block in Listing 8 reads the error severity, number, message, and error state information from SQL Server. The block of code then uses the RAISERROR function to raise an exception back to the calling application. But in between, the code examines the Error State setting to see if the value is zero. The reason is because some errors (e.g. constraint violations) do not return an error state, and so you must set the error state to a value of one, before raising the error back to the application.

Scenario: You're looking for a replacement for IN and NOT IN clauses that are simpler to code and better at handling NULL values.

INTERSECT and EXCEPT statements in SQL Server 2005 are lesser-known (but still valuable) language features in SQL Server 2005, as they provide cleaner alternatives for IN and NOT IN. Here's an example of using INTERSECT and EXCEPT:

   --Using INTERSECT/EXCEPT instead of IN/NOT IN
   USE AdventureWorks
   SELECT Vendor.VendorID  FROM Purchasing.Vendor Vendor 
   INTERSECT SELECT VendorID FROM Purchasing.PurchaseOrderHeader
   SELECT Vendor.VendorID  FROM Purchasing.Vendor Vendor 
   EXCEPT SELECT VendorID FROM Purchasing.PurchaseOrderHeader
Along with slightly improved readability, INTERSECT and EXCEPT also do not require ISNULL or COALESCE statements to test for NULL values in the sub-query, whereas IN and NOT IN require them.

Tip 13: Flexible UPDATE Procedures
Scenario: You want to build UPDATE stored procedures so that you only have to pass parameters for column values that changed, and you also want to pass explicit NULL values for columns that should be NULLed out.

Unlike all the other code in this article, this tip can apply to SQL Server 2000 as well as SQL Server 2005.

Recently I participated in a debate regarding stored procedures to handle UPDATE statements. Essentially, someone posed the question about whether an UPDATE stored proc could handle the following:

  • Optional parameters (for example, a table has twenty columns, but a user only changes three of them, so the application should only have to pass those three parameters, not all twenty).
  • Explicit NULL parameters (the ability to set a non-null column value to NULL)
Some people have asked me about the second scenario. You might have a survey application where NULL means "No Response." A user might accidently post a response, click Save, and then realize later that the response should have been "No Response." Another example might be an insurance application that ties individual accounting transactions to a particular policy. A transaction might be shifted from policy A to another policy, but not right away. So the related policy foreign key might need to be set to NULL for a period of time, until the client determines the correct foreign key. Bottom line: sometimes it's necessary to set values from non-null to NULL.

Addressing both conditions proves to be an interesting challenge. A single column parameter might be NULL because the application did not pass it, in which case you don't want the column to change from the current value. On the other hand, a single column parameter might be NULL because the application explicitly passed a NULL value. So how can the stored procedure code determine the "intent" of the NULL?

Some database developers handle this by adding a second set of Boolean parameters to indicate that the NULL parameter is an explicit NULL. While this works, it adds to the stored procedure's API by requiring more parameters. An alternate solution shown below utilizes a default token value for each datatype that would otherwise never be used. You can then check in the UPDATE statement if the parameter is still equal to this token value:

   --UPDATE stored proc to handle implicit and explicit NULL values
   CREATE PROCEDURE [dbo].[UpdateData] 
     @PK int,  @FullName char(100)='-999999', 
     @LastEvalDate DateTime = '01-01-1800',
     @Salary Decimal(14,0)  = -999999, @Interviewed int = -99 
   UPDATE NameTest 
   SET FullName = CASE 
   WHEN @Name = '-999999'  THEN Name
   ELSE  @Name END, LastEvalDate = CASE
   WHEN @LastEvalDate = '01-01-1800' THEN LastEvaldate
   ELSE @LastEvalDate END, Salary = CASE 
   WHEN @Salary= -999999  THEN Salary
   ELSE @Salary END, Interviewed = CASE 
   WHEN @Interviewed = -99  THEN Interviewed
   ELSE CAST(@Interviewed AS BIT) END
   OUTPUT Inserted.*  
Note that the Boolean column for Interviewed is represented as an integer column. This is because it's not possible to set a token value for a bit column, as the only possible values are NULL, 0, or 1. However, you can define the parameter as an integer and then CAST it as a bit if the value does not equal the token value. A developer can still pass a true/false value from the application.

Sneak Preview of Katmai (SQL Server 2008)
As I write, Microsoft has released the June 2007 Community Technology Preview (CTP) for SQL Server 2008 (codenamed "Katmai"). Microsoft provides two links you can follow for more information on Katmai:

Katmai features a number of language enhancements to the T-SQL language. Here are a few:

  • Programming shortcuts for declaring and assigning values to variables
  • Table types and table parameters
  • Expansion of GROUP BY clause
  • A new MERGE statement to perform an INSERT/UPDATE/DELETE in one statement
  • Row constructors for INSERT statements
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. You can find the entire source code 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