| Downloads |
|---|
| 99778.zip |
SQL Server 2008 offers so many important T-SQL enhancements,
I ran out of space last month describing them (see “T-SQL Enhancements in
SQL Server 2008,” InstantDoc ID 99416). Now let’s look at more T-SQL enhancements you might find useful.
1. Table-Valued Parameters
If you’ve ever needed to pass a large number of parameters to a stored procedure or function, you’ll appreciate SQL Server 2008’s support for table-valued parameters.
As you can see in Listing 1, I created a new Table Type called MyTableType. Next, I created a stored procedure that uses a table as a parameter. Last, you can see the new MyTableType variable that was populated and passed to the usp_MyProc stored procedure.
2. Enhanced CONVERT Function
The CONVERT function now supports multiple conversion styles, giving you more
control over the output of the function. I’ve used the new styles in the following example:
SELECT CONVERT(char(8), 0x4D69636861656C, 0) AS ‘Style 0’ SELECT CONVERT(char(8), 0x4D69636861656C, 1) AS ‘Style 1’ SELECT CONVERT(char(8), 0x4D69636861656C, 2) AS ‘Style 2’
3. GROUPING SETS Operator
The new GROUPING SETS operator lets you specify multiple groupings of data
(or grouping sets) in a single query and gain more control over the aggregations the query returns.
In Listing 2, I used a SELECT statement to return three columns from the
Sales.SalesOrderHeader table and the Sales.Store table, aggregating the total
for the TotalDue column. The GROUPING SETS clause creates three grouping sets:
results by Name and DueDate, results by Name, and the entire result set.
YOUR SAVVY ASSISTANT - The Missing Link to IT ResourcesSometimes, you just can’t say it better than somebody else already has. In a September 2007 Letter to the Editor (InstantDoc ID 96635), Roger Reid wrote “a SQL Server admin supporting SharePoint needs to know a fair amount about SharePoint.” To learn more about Microsoft Office SharePoint Server (MOSS) and Windows SharePoint Services (WSS), check out these resources. For basic information about SharePoint: “Windows SharePoint Services 3.0 Out of the Box,” InstantDoc ID 94240 “What Can I Accomplish with Other SharePoint Technologies?” InstantDoc ID 96841 For information on how MOSS and SQL Server work together: “Migrating Reporting Services to MOSS,” InstantDoc ID 96975 “SSRS and MOSS 2007: Deploying the Power,” InstantDoc ID 97071 “Enabling Information-Management Policies,” InstantDoc ID 97156 “Information Integration: SSRS and MOSS 2007,” InstantDoc ID 98640 For in-depth SharePoint workshops:< br/> On September 30th and October 1st, SharePoint MVPs Dan Holme, Michael Noel, and Andrew Connell share their real-world perspective, experience, and expertise. You'll learn how to build a better SharePoint infrastructure, develop more effective SharePoint applications, and enable more powerful collaboration. To register, go to www.windowsitpro.com/go/elearning/gotSharePoint |
