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

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


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.




Application Security Testing: An Integral Part of DevOps

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 ;

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date