A major software release either contains a substantial number of enhancements, or a small number of substantial enhancements.
The last two releases of SQL Server remind me of the two types of Christmas: SQL 2005 was like receiving a large number of presents, and SQL Server 2008 is like receiving a smaller number of big gifts. Those of you who asked Santa for the ability to INSERT/UPDATE/DELETE in one SQL statement, or to specify multiple GROUP BY statements in a single SQL query, you will be very happy. If you wanted a hierarchical data type, you will be ecstatic. SQL Server 2008 also includes a new and completely re-written version of SQL Server Reporting Services (SSRS)-complete with a new interface, new charting features, and (get ready to applaud) no more reliance on Internet Information Services (IIS). So while the temperatures are getting warmer, it’s Christmas Day here at the Baker’s Dozen, and we’re about to unwrap thirteen major SQL Server/SSRS 2008 gifts from Microsoft.
Confessions of a Data Guy
When Microsoft releases new language features or .NET Framework features, I go through my little circle of life. One can almost hear Elton John singing “Can you feel the code tonight?” I go through a few iterations of the grokking phrase, I talk to others about it, and then I try it out in a few prototypes before taking the production plunge.
I confess-I get just as stoked when I read about new capabilities in SQL Server to store data, query data, and visualize data. I was pretty excited about the language offerings in SQL Server 2005, and I’m just as fired up about what Microsoft has done with SQL Server 2008. The new enhancements demonstrate that not only do they listen when people ask for new language statements; they also care about making developer tasks more seamless.
What’s on the Menu?
Let’s get right to the menu. (Or, using the Christmas analogy, let’s open the presents under the tree!) This Baker’s Dozen article will cover eight tips for using new developer functionality in SQL Server 2008, and five tips for new capabilities in Reporting Services. Here they are:
- Some T-SQL Server 2008 “stocking stuffers” (do you get the feeling I’m anxious for Christmas?) -Insert Row Constructors, Assignment Operators, and Variable Initializers.
- The new GROUP BY extensions so that developers can specify multiple groupings in a single result set
- The new MERGE keyword that allows developers to perform INSERT/UPDATE/DELETE operations in one SQL statement.
- The new Hierarchy ID system data type.
- The new Table data type, for passing Table-like structures as parameters.
- The new Date and Time data types. (Yes, you can have a Date column that’s JUST the date!)
- Other miscellaneous SQL Server 2008 enhancements.
- The new architecture for SSRS 2008.
- Exploring the new development environment in SSRS 2008.
- The new SSRS Tablix control (table plus matrix controls, plus more!).
- New charting features in SSRS 2008 (courtesy of Dundas).
- New export/delivery capabilities.
- Better SharePoint integration.
Tip 1: T-SQL Server 2008 “Stocking Stuffers”-New 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 ;
Tip 4: The New HierarchyID
SQL Server 2005 gave developers new language capabilities to perform recursive queries against data stored hierarchically. SQL Server 2008 now allows you to store hierarchical data better than before, and also provides new functions to query data without needing to use common table expressions.
SQL Server 2008 introduces a new data type called the HierarchyID data type. As with many new concepts, a simple example can be worth a thousand words of explanation, so here’s a simple example of a product hierarchy structure (family, brand, category, subcategory, etc.) stored in a single table called ProductTree. First, the syntax to create the ProductTree table is as follows:
CREATE TABLE ProductTree
(ProductTreePK int IDENTITY,
Description varchar(1000),
ProductLevelPK int, HierID hierarchyid)
Next, here’s a foreign table of descriptions for each ProductLevel:
CREATE TABLE ProductLevels
(ProductLevelPK int IDENTITY,
Description varchar(50))
-- PK of 1 (All)
INSERT INTO ProductLevels VALUES ('All Products')
-- PK of 2 (Family)
INSERT INTO ProductLevels VALUES ('Family')
-- PK of 3 (Brand)
INSERT INTO ProductLevels VALUES ('Brand')
-- PK of 4 (Category)
INSERT INTO ProductLevels VALUES ('Category')
-- PK of 5 (SubCategory)
INSERT INTO ProductLevels VALUES ('SubCategory')
-- PK of 6 (SKU)
INSERT INTO ProductLevels VALUES ('SKU')
The HierarchyID data type contains methods to Get Descendants, Ancestors, as well as a root definition. So you can use the following functions as part of building an “API” for inserting into/retrieving from a hierarchical data type.
hierProductParentID.GetDescendant(@LastChild,NULL)
hierarchyid::GetRoot()
GetAncestor(1)
With an API for inserting (Listing 1), you can make creating data very easy.
exec InsertProductTree null,'All Products', 1
exec InsertProductTree 1 ,'Family A', 2
exec InsertProductTree 1 ,'Family B', 2
exec InsertProductTree 1 ,'Family C', 2
declare @TempParent int =
(SELECT ProductTreePK FROM ProductTree WHERE
Description = 'Family A')
exec InsertProductTree
@TempParent ,'Family A - Brand AA', 3
exec InsertProductTree
@TempParent ,'Family A - Brand AAA', 3
Figure 1 shows the results.
Tip 5: The New Table Data Type
Despite reactions from database “purists”, many database/application developers have often asked for the ability to pass a table definition (or an ADO.NET DataTable) as a pure parameter. While Table-valued functions allow developers to write UDFs to return table variables, most developers would have to use XML or other tricks to actually pass a table as a parameter.
Fortunately, SQL Server 2008 contains a new Table type that finally gives developers what they’ve been asking for. For each instance where you wish to pass a table, you’ll need to establish an actual Table type in the database. For example, if you have a table of sales information that you want to pass as a parameter (say, to do a mass update of order information), you can define a Table type like so:
-- Step 1: Create a new Table type
-- in the database
CREATE TYPE SalesHistoryTableType AS TABLE
(
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
Next, let’s create an actual physical table that we’ll use as the destination table (actual sales orders).
-- Step 2: Create an actual sales history table
CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int]
IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
Our objective is to create a stored procedure that will take a temporary table variable (of type SalesHistoryTableType) and insert it into the permanent table. The code below contains a stored procedure that performs this task. Note that the Table type parameter is READONLY-you cannot modify the contents of a Table type inside the procedure that receives it as a parameter.
-- Step 3: Create a stored procedure that will
-- read a variable of type SalesHistoryTableType
-- and insert certain rows into the main Table
CREATE PROCEDURE usp_InsertProductOne
(
@TableVariable SalesHistoryTableType READONLY
)
AS
BEGIN
INSERT INTO SalesHistory
(Product, SaleDate, SalePrice)
SELECT Product, SaleDate, SalePrice
FROM @TableVariable
WHERE Product = 'Product 1'
END
Finally, you can write code to create a variable of type SalesHistoryTableType, populate it with data, and then pass it to the stored procedure.
-- Step 4: Create a variable of type
-- SalesHistoryTableType, and add test data
DECLARE @tTB AS SalesHistoryTableType
DECLARE @i SMALLINT = 1
WHILE (@i <=1000)
BEGIN
INSERT INTO @Ttb (Product, SaleDate, SalePrice)
VALUES ('Product 1',
DATEADD(mm, @i, '3/11/2007'), @i),
('Product 2',
DATEADD(mm, @i, '3/11/2007'), @i),
('Product 3',
DATEADD(mm, @i, '3/11/2007'), @i);
SET @i += 1
END
-- Step 5: Call the stored procedure
EXECUTE usp_InsertBigScreenProducts
@TableVariable = @DataTable
You may be wondering, “Can I do this from a .NET application?” The answer is YES! The SqlClient data provider for SQL Server 2008 contains a new SqlDbType called Structured that allows you to pass a DataTable (or a DataReader, or any IEnumerable).
As an example, you can create a DataTable in ADO.NET:
DataTable dt = new DataTable();
dt.Columns.Add("Product", typeof(String));
dt.Columns.Add("SaleDate", typeof(DateTime));
dt.Columns.Add("SalePrice", typeof(Decimal));
dt.Rows.Add("Product 1", DateTime.Today,
10000.00);
Second, you can create a SQL connection:
SqlConnectionStringBuilder osb =
new SqlConnectionStringBuilder();
osb.DataSource = "localhost";
osb.InitialCatalog = "AdventureWorks";
osb.IntegratedSecurity = true;
SqlConnection oConn =
new SqlConnection(osb.ConnectionString);
oConn.Open();
And finally, you can pass the DataTable as a parameter, using the new SqlDbType.
SqlCommand oCmd =
new SqlCommand("usp_InsertProductOne", oConn);
oCmd.CommandType = CommandType.StoredProcedure;
SqlParameter oParm =
oCmd.Parameters.AddWithValue
("@TableVariable", dt);
oParm.SqlDbType = SqlDbType.Structured;
oParm.TypeName = "dbo.SalesHistoryTableType";
oCmd.ExecuteNonQuery();
oConn.Close();
Finally, another application for the Table type is handling a variable number of user selections. For example, many developers have faced situations where they need to query against invoices, where the user might select one account, or two, or ten, etc. Often, developers will pass a comma-separated list of integer keys (or an XML string) as a parameter to a stored procedure. With the new Table type, you can now pass an ADO.NET DataTable as a parameter. This represents the simplest and most efficient means yet for this type of process.
Tip 6: Simplified Date and Time Data Types
Of all the new enhancements in SQL Server 2008, the one that often gets the most applause is the fact that you can now create separate Date and Time data types.
-- Ability to specify a Date as a type
DECLARE @tDates TABLE (WorkDate DATE)
INSERT INTO @tdates VALUES ('1-1-2008'),
('1-1-2009'),
('12-31-2007'),
('3-1-2008'),
('11-1-2009'),
('12-1-1620');
SELECT DATEPART(yy,workDate),
WorkDate FROM @tDates ORDER BY WorkDate
Tip 7: Other New SQL Server 2008 Enhancements
While the first six tips in this article focused on specific language and data type enhancements in SQL Server 2008, there are other significant enhancements in SQL Server 2008.
-
XML enhancements: Suppose you need to validate a single XML document against different schemas. SQL Server 2008 provides a new keyword (processContents=”lax”) to relax validations on undefined node elements that would otherwise invalidate the XML document. You can also use the new date and time data types in SQL Server 2008 XML documents. -
Filtered indexes: This works exactly the way it sounds. You can define an index that only contains references to the rows that meet your filter condition. For large databases, this can have a dramatic impact on performance. -
Sparse columns: Once again, another enhancement that is almost self-explanatory. You can define columns in a table as sparse, and the columns will take up almost no space if the value for the row is NULL. However, a sparse column will take up four more bytes if the value contains data. SQL Server 2008 Books Online contains more information on guidelines for when sparse columns are beneficial. -
Spatial Computing: SQL Server 2008 contains new GEOMETRY (“Flat Earth Model”) and GEOGRAPHY (“Ellipsoidal model”) data types. These are actually abstract data types you can use to work with more specific spatial data types (e.g. point, linestring, polygon, Multipoint, etc.). Typically you would use the GEOMETRY abstract type for two-dimension problems, and you would use the GEOGRAPHY abstract type for spherical data with latitude/longitude coordinates.
One final note on spatial computing-ESRI is using the new spatial features in SQL Server 2008 as part of the next scheduled release of their ArcGIS Geography Information System. You can follow this link to a press release relating to this news: http://esri.com/news/releases/08_1qtr/sql_server_2008.html
Tip 8: SQL Server Reporting Services 2008-A Lean and Mean Architecture, sans IIS
SQL Server Reporting Services 2005 was a big leap forward in terms of functionality from SSRS 2000. However, many developers/users experienced two major challenges:
- SSRS 2005 required Internet Information Services (IIS) to run.
- SSRS 2005 is extremely resource intensive, so much so that some IT shops would deploy SSRS 2005 on a different box than the SQL Server database.
Microsoft has rewritten SSRS 2008 from the ground up with goals of a simpler and more scalable architecture. As a result, you will find these major changes:
- First, SSRS 2008 no longer requires IIS. SSRS 2008 uses the http.sys driver and listens for report requests through http.sys. Not only does this reduce deployment headaches, it also reduces server overhead.
- All SSRS 2008 components work under one application pool and one Windows service.
- SSRS 2008 utilizes memory more efficiently, especially when working with reports that contain large sets of data. Additionally, SSRS 2008 will often load the first page of a report faster than SSRS 2005.
Tip 9: The New SSRS 2008 Designer Environment
Microsoft has implemented a number of significant changes and improvements to the overall design-time experience in SSRS 2008:
- In SSRS 2005, non-programmers had to use the Visual Studio 2005 shell (Business Intelligence Development Studio, “BIDS”) to create SSRS reports. Microsoft has built a stand-alone report designer for SSRS 2008, one that has an Office 2007-style look and feel. For those who prefer to use Visual Studio, you’ll see the same UI enhancements in the SSRS 2008 Project Template.
- The tabbed dialog boxes for various report options have been replaced with new dialogs, where options are more logically organized. Figure 2 shows an example.
- The report design area has been enhanced for clarity. Figure 3 shows the new design area, which includes new row/column group panes-this makes it much easier to visualize report groupings. Also note in Figure 3 that available report fields and “special fields” are available in the report elements task list in the upper left.
Tip 10: Tablix Means Never Having to Say “The Control Won’t Let Me Do This”
SSRS 2005 contains two report controls called Table and Matrix, for most reports to generate listings of data. The Table control allowed you to specify as many columns as you wanted, so long as the columns belonged to one horizontal group. The Matrix control allowed you to create a crosstab/pivot effect of a variable number of entities across the top (years, quarters, etc.) -so long as the entities belonged to the same group.
The strength of each control was a deficiency in the other. Often, developers wanted the functionality of the matrix control, but still wanted to place specific columns “free-form” either before, in between, or after horizontal column groups. Fortunately, Microsoft has implemented functionality that takes the best features of the table and matrix, and has called it the Tablix control.
If you’ve already experimented with any of the CTPs for SQL Server 2008, you may already know that the SSRS 2008 toolbox does not contain an actual control called Tablix. Microsoft has essentially expanded the functionality of both the Table and Matrix controls, so that you receive the same enhanced benefits, regardless of which control you initially selected.
Figure 5 shows one of the primary new benefits of the Tablix functionality-the ability to insert new columns either inside or outside of an existing column group, and either to the left or right. So now, if you have an existing measure you wish to add to a Matrix report (e.g. grand total freight in the same horizontal band as a column group for sales by quarter or year), you can do so! Figure 6 shows a report sample using the Tablix functionality.
Tip 11: Baker’s Dozen Spotlight: New Charting Features in SSRS 2008 (Courtesy of Dundas)
Charting in SSRS 2005 was, in my opinion, a decent but not spectacular experience. However, you’ll want a large supply of pens to “write home” about charting in SSRS 2008. Microsoft purchased charting functionality from Dundas Data Visualization Inc**.-**as a result, SSRS 2008 contains the following:
- New chart types (Figures 7 and 8 show new standard chart options and new gauge report types).
- Redesigned chart options dialogs (Figure 9).
- Enhanced charting capabilities, such as the ability to build a chart with a dual-Y axis. Figure 10 shows a chart that plots dollar sales as one Y-axis (vertical bars) and also plots sales ratio percentage as a second Y-axis (lines). Note the option back in Figure 9 for a secondary axis.
- Support for a calculated series (e.g. if you want to take plotted data and chart a moving average).
- Support for custom CSS themes for the SSRS report preview/toolbar-an example URL would be as follows:
http://localhost/reportserver?/MyReport
&rs:Command=Render&rc:Stylesheet=MyCSS.css
The Dundas Chart product continues as a separate product. For more information, check out this link: http://www.dundas.com/Products/Chart/RS/Demos/index.aspx
NOTE: As of the current SQL Server 2008 CTP, SSRS 2008 does not support any custom code you may have previously written for Dundas Chart.
Tip 12: New Exporting Capabilities in SSRS 2008
SSRS 2008 now supports Microsoft Word/Rich-Text rendering, along with all of the prior export formats (Excel, PDF, etc.) Microsoft has also improved report delivery to MOSS (Microsoft Office SharePoint Services).
Tip 13: Better Integration Between SSRS 2008 and SharePoint
SSRS 2008 contains an add-in for Microsoft SharePoint Integrated mode. This process will include/respect data-driven subscriptions. You’ll be able to cleanly display SSRS 2008 reports on a SharePoint site, right beside other Web Parts showing KPIs (Key Performance Indicators), Excel pivot tables, and other Business Intelligence Web Parts (e.g. PerformancePoint Server dashboards).
Final Thoughts
Check my blog (www.TheBakersDozen.net) for any follow-up tips, additional resources, and notes on Baker’s Dozen articles.
Listing 1: Code to insert data into a table with a Hierarchical ID
CREATE PROCEDURE [dbo].[InsertProductTree]
(@ProductParentPK int, @Description varchar(50),
@ProductLevelPK int)
AS
BEGIN
DECLARE @varlastChild VARCHAR(50),
@hierProductParentID hierarchyid,
@LastChild hierarchyid
-- Take the product's specified parent
-- and find the HierarchyID
SELECT @hierProductParentID = HierID
FROM ProductTree
WHERE ProductTreePK = @ProductParentPK
-- Take the HierarchyID for the parent and covert to string
-- now stored as something like '/1/'
Set @varlastChild = @hierProductParentID.ToString()
-- Determine the current Last Child
SELECT @LastChild = MAX(HierID)
FROM ProductTree
WHERE HierID.ToString() LIKE @varLastChild +'[0-9]/'
-- Use GetDescendant to get the HierarchyID for the parent
-- of the last child
DECLARE @NewHierID hierarchyid = @
hierProductParentID.GetDescendant(@LastChild,NULL)
-- If you're trying to save a root level row, get the root ID
IF @NewHierID is null
SET @NewHierID = hierarchyid::GetRoot()
INSERT INTO ProductTree
(Description, ProductLevelPK, HierID)
VALUES
(@Description, @ProductLevelPK, @NewHierID)
END ;
