dcsimg
LinkedIn
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

The Baker's Dozen: 13 Tips for SQL Server 2008 and SSRS 2008 : Page 2

SQL Server 2008 gets new developer features, and SQL Server 2008 Reporting Services gets a facelift and new architecture that no longer requires Internet Information Services.


advertisement
Tip 1: New T-SQL Language Features to Simplify Basic Operations
T-SQL Server 2008 contains new language features to reduce the number of lines of code for certain basic tasks:

  • You can now DECLARE a variable and initialize it in one line of code.
  • You can use new Row Constructor syntax to insert multiple rows in a single statement. You simply separate the row definitions with parenthesis and a comma.
  • You can use basic assignment operators to simplify incrementing.
This code sample demonstrates the new features:

   -- Variable initialization
   DECLARE @Counter int = 5
   DECLARE @Sales TABLE (EmpId INT, Yr INT, 
                      Sales MONEY, MyCounter int)
   -- Insert Row Constructors
   INSERT @Sales VALUES
       (1, 2005, 12000, @Counter), 
       (1, 2006, 18000, @Counter+1),
       (1, 2007, 25000, @Counter+2), 
       (3, 2006, 20000, @Counter+3), 
       (3, 2007, 24000, @Counter+4);
   -- Assignment operators
   UPDATE @sales set Mycounter += 1 
Tip 2: New GROUP BY Extensions
Have you ever queried data for a report and needed to add a table to the overall result set, just to summarize the details in a certain grouping manner? Or have you ever needed to bring back a single table and multiple summaries of that table, only to run into limitations of a report writer or other tool that didn't allow you to work with multiple result sets to begin with?

SQL Server 2008 offers new GROUP BY extensions that allow you to specify multiple GROUP BY options in a single table—yes, in the same record set that contains the details.

Let's take a look at some examples, using the AdventureWorks database. First, a bit of housekeeping. If you're using a version of AdventureWorks that came from SQL Server 2005, you'll need to set the DB compatibility level to SQL Version 10.

   sp_dbcmptlevel  Adventureworks, 100
OK, for the first example—suppose you wanted to group all the Purchase Orders by Vendor Name, by Vendor Name and Year, and then a grand total for all vendors. You can use the following syntax:

   -- Will summarize by Name, by Name/Year,
   -- and then a grand total summary
   SELECT Name,DATEPART(yy,OrderDate) AS Yr, 
      SUM(TotalDue) AS TotalDue
   FROM Purchasing.PurchaseOrderHeader PO
      JOIN Purchasing.Vendor VE 
      ON PO.VendorID = VE.VendorID
   GROUP BY ROLLUP(Name, DATEPART(yy,OrderDate))
Your results would look like the following (note the NULL entries for the rows summarized by Name, and by Total). Hopefully you can see from the results that the ROLLUP statement allows you to specify multiple GROUP BY definitions in a single result set.

   Name            Yr   TotalDue
   Advanced Bicycles      2001        300.67
   Advanced Bicycles      2002       1716.71
   Advanced Bicycles      2003       9406.83
   Advanced Bicycles      2004      17077.88
   Advanced Bicycles      NULL      28502.09
   Allenson Cycles        2001       9776.27
   Allenson Cycles        2002      29328.80
   Allenson Cycles        2003     127091.46
   Allenson Cycles        2004     332393.06
   Allenson Cycles        NULL     498589.59
   NULL                   NULL   70479332.64 
The ROLLUP syntax is like a clean sweep: it will work progressively across the columns you specify. However, if you want to define your own GROUP BY statements, you can use the GROUP BY GROUPING SETS statement. This first example summarizes orders by Name, by Name and Year, and by Grand Total (and produces the exact same query as the one above that used ROLLUP).

   -- Will produce the exact same results
   -- as the previous query
   SELECT Name,DATEPART(yy,OrderDate) AS Yr, 
        SUM(TotalDue) AS TotalDue
   FROM  Purchasing.PurchaseOrderHeader PO
           JOIN Purchasing.Vendor VE 
   on PO.VendorID = VE.VendorID
   GROUP BY GROUPING SETS (
            (Name),
            (Name, DATEPART(yy, OrderDate)),
             ())
Here are different examples if you want to specify multiple GROUP BY definitions that can't be expressed by a ROLLUP.

   -- 2 Groups, by name and by year
   GROUP BY GROUPING SETS (
            (Name), 
            (DATEPART(yy, OrderDate))
   -- 2 Groups, by name/year, and by total 
   GROUP BY GROUPING SETS (
            (Name, DATEPART(yy, OrderDate)),
            () )
   -- 3 Groups, by name/year, by name, and by year
   GROUP BY GROUPING SETS (
            (Name, DATEPART(yy, OrderDate)),
            (Name),
            (DatePart(yy,OrderDate)))
Remember, you'll need to account for the NULL entries when processing these result sets.

Tip 3: The New MERGE Statement to Combine INSERT/UPDATE/DELETE
Want to test if someone is a true SQL language guru? Ask them if they know what "UPSERT" means! This is a joke in the SQL world that refers to the long-requested ability to perform an INSERT and an UPDATE in the same line of code.

Suppose you are writing a maintenance routine that compares a target table to a source table. You need to do three things:

  • If data exists in the source table but not the target table (based on a lookup on an ID column), insert the data from the source to the target.
  • If data exists in both tables (again, based on a matching ID), update the data from the target table into the source table (some columns might have changed).
  • Finally, if any ID values are in the target, but don't appear in the source, delete those ID values in the source.
SQL Server 2008 contains a new MERGE statement that allows developers to handle all three of these situations in one line of code! To demonstrate this, here are two sample tables, tSource and tTarget.

   -- Create two test tables with name/Age
   CREATE TABLE tSource (ID int, Name char(50), 
       Age int)
   CREATE TABLE tTarget  (ID int, Name char(50), 
       Age int)
   INSERT INTO tTarget VALUES 
       (1, 'Kevin', 42), 
       (2, 'Steve', 40), 
       (3, 'Mike', 30);
   INSERT INTO   tSource VALUES 
       (1, 'Kevin Goff', 43), 
       (2, 'Steve', 41), 
       (4, 'John', 50);
To perform the tasks from above, you can use the MERGE statement, as follows:

   MERGE [tTarget] t 
      USING [tSource] s on t.ID = s.ID
         -- If we have an ID match, update tTarget
         WHEN MATCHED
            THEN UPDATE 
            SET t.Name = s.Name, t.Age = s.Age 
             -- If ID in source but not target, 
             -- insert ID into Target
         WHEN NOT MATCHED 
            THEN INSERT VALUES(ID, Name, Age)
            -- If ID is in target but not source, 
            -- delete the ID row in the target
         WHEN SOURCE NOT MATCHED THEN DELETE ;


Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date