Table Valued Parameter and Grouping Set in SQL Server 2008
By : Kasim Wirama, MCDBA
Through this article, I would like to describe 2 T-SQL enhancements in SQL Server 2008. First is table valued parameter and second is grouping sets. There are still some more TSQL enhancements made in SQL Server 2008; but this time these 2 enhancements get me attracted most.
1. Table valued parameter.
I think this is most requested wish list from developer to Microsoft. And it is realized in SQL Server 2008. I think it will give developers more options to deliver bulk data into database server so that it reduces round trip between application server and database server.
To be able to use table value parameter, you need to create table valued type in SQL Server 2008; then you can use it for variable and input parameter(s) in SQL routines (stored procedure and functions). Note that for input parameter, you need to add READONLY clause after the table valued type.
Here is example of table valued parameters:
CREATE type myType as table (col1 int, col2 varchar(20));Go alter procedure dbo.usp_MyType(@param1 myType READONLY)asbegin select * from @param1;endGO declare @var1 myType;insert into @var1 values (1,'one'),(2,'two');exec dbo.usp_MyType @var1;GO
2. Grouping set.
Grouping set will add more functionality to GROUP BY clause by letting you specify one or more grouping condition with more efficient execution plan. For example, you would like to know total sales in sales order made by each sales persons and each year, but also you need to know total sales in each years and total sales made by sales persons who make orders. Before SQL Server 2008, you issue 3 queries:
1. First query grouped by sales person and year to get total sales.
2. Second query grouped by sales year to get total sales in corresponding year.
3. Third query grouped by sales person to get total sales in all years.
With grouping set in SQL Server 2008, you just need to issue 1 TSQL select statement here (I use AdventureWorks database for SQL Server 2008):
selectp.LastName, DATEPART(yy,soh.duedate) as DueYear, sum(soh.TotalDue) as totalfrom Sales.SalesOrderHeader as sohjoin Person.Person as pon soh.SalesPersonID = p.BusinessEntityIDgroup by GROUPING sets((p.LastName, DATEPART(yy,soh.duedate)),DATEPART(yy,soh.duedate),p.LastName)order by p.LastName, DueYear ;
With TSQL enhancements in SQL Server 2008, you will have more options to have more efficient code.