Table-valued types and parameters are among the many exciting new features of the recently released SQL Server 2008. These table-valued types allow you to pass table data to stored procedures and functions.

But why is this useful? In previous versions of SQL Server one could pass parameters to stored procedures and functions. A single parameter can describe a single value. But what did you do if the information you want to pass to a stored procedure is more dynamic? If not all information is required for the stored procedure? For example a customer order that has optional delivery time or priority information? You could pass null values, but if these make the code look rather unreadable. Previous solutions included optional parameters for stored procedures. Parameters that had a default value need not be specified. This made stored procedure calls nicer, but the parameter list of stored procedures could quickly become unmanageable. The situation was aggravated when new parameters were introduced.

Furthermore, this solution could not handle a different type of dynamic information, for example a customer order that contains multiple items. People started to use dirty hacks to circumvent this restriction, and used comma separated lists, and later XML. However, a database management system is certainly not ideal for processing strings or XML, especially when using T-SQL.

Another alternative was using a communication table that was either fixed or created on demand. This solution suffered too, because when the communication table was permanent, it needed regular clean up, concurrency needed to be handled manually, and with temporary tables there could be recompilation issues.

Using communication tables was also not ideal from the point of view of roundtrips or code simplicity between the applications and the database. Multiple insert statements needed to be executed just to set up the stored procedure call, all of this put into a single transaction, etc.

Well, this is hopefully the past, welcome the new table-valued types and parameters. So let’s see an example.

In order to use table-valued parameters one needs to set up a table type. This looks like the crossbreed of a user defined type and a table definition:

CREATE TYPE myTableType AS TABLE
    (
      id INT NOT NULL
             PRIMARY KEY,
      data NVARCHAR(100)
    )

Note that you can specify a primary key, constraints, computed columns, etc. This gives you a lot of flexibility, and this extra control will help you to optimize performance and ensure data integrity.

Once you have set up a table type, you can use it as a stored procedure or function parameter. In the following example we just select the information inside the table that is passed in as a parameter:

CREATE PROCEDURE myProcedure
    ( @TableVariable myTableType READONLY )
AS
    BEGIN
        SELECT  *
        FROM    @TableVariable
    END
GO

As the above example shows, you can query the table parameter as if it were an ordinary table variable. However, you cannot modify the table parameter, and this is spelled out explicitly by the mandatory “READONLY” word after the parameter specification.

To see how the above works in T-SQL we can declare a table variable using our new table type, insert data into this table variable (and I also use the new row constructor in SQL Server 2008 to insert multiple rows in a single statement), and call the stored procedure with the table variable as its parameter:

DECLARE @table myTableType
INSERT  INTO @table
VALUES  ( 1, 'a' ),
        ( 2, 'b' ),
        ( 3, 'c' )
EXEC dbo.myProcedure @table

After executing the above we get a resultset like:

id          data
----------- ----------------
1           a
2           b
3           c

(3 row(s) affected)

The above may simplify the work of people writing stored procedures and functions, but what about developers. Well, the above works just as well with ADO.Net. When creating a SQLCommand object for a stored procedure, one can now pass in a DataTable object as a parameter, exactly as it is done in the following C# code segment:

SqlConnection connection = new SqlConnection("server = ...");
 
SqlCommand command = connection.CreateCommand();
command.CommandText = "dbo.myProcedure";
command.CommandType = CommandType.StoredProcedure;
 
// declare a table to store the parameter values
DataTable paramTable = new DataTable();
paramTable.Columns.Add("id", typeof(int));
paramTable.Columns.Add("data", typeof(string));
 
// add the table as a parameter to the stored procedure
SqlParameter sqlParam = command.Parameters.AddWithValue("@TableVariable", paramTable);
sqlParam.SqlDbType = SqlDbType.Structured;
sqlParam.TypeName = "dbo.myTableType";
 
// add rows to the table parameter
paramTable.Rows.Add(new object[] {1, "a"});
paramTable.Rows.Add(new object[] {2, "b" });

 Note that there are no insert statements; we can just use a DataTable to pass in table parameters to the stored procedure. Hopefully this solution in SQL Server 2008 will get rid of all the now unnecessary “hacks” that used XML, comma separated lists and temporary tables used in a large number of database applications.

 If you want to hear more about what is new in SQL Server 2008, and you are in the UK or Poland on the following dates, do come to my presentations or talk to me later. For the first half of September my schedule looks like the following:

• September 2^nd 2008: SQL Server and the Sequel , NxtGenUG in Cambridge, UK
• September 6^th 2008: Managing change in the database world at ZineDay in Warsaw, Poland
• September 13^th 2008: Advanced T-SQL (2005 and 2008) at SQL Bits 3 in Hatfield, UK

I want to restore only a single table from my database backup. We have been hearing this request from many of our SQL Backup customers. The problem is simple: Given a large database backup and a live database that has a partial “corruption”, e.g. the data in a single table has been accidentally modified and committed, the DBA would like to have the table, and nothing else, restored. Or, another example is when a particular stored procedure has been modified, and we would like to get back the original version. This problem is a bit worse if we do not even know which backup file contains the right information.

In SQL Server the only way to solve the above problem is to restore the whole backup file to a new database, and copy the table data or the stored procedure to the current database.

Well, this problem is now history. We have just released the first public beta of SQL Compare 7. This tool, which is primarily used for comparing and synchronizing two database schemata, can now read the schema information directly from a backup file. This complements well SQL Data Compare 6.1 which already has this feature at the data level. These two products open up a lot of new recovery opportunities.

If you want to restore a few stored procedures, you just compare your live database with a backup file, and you can explore the differences in your schema objects. It may be that the modified stored procedure has a few dependent stored procedures that you also wish to restore. After selecting all the schema objects you wish to retrieve from the backup files, you can either restore them or create and save a synchronization script. When it comes to tables, Data Compare can do even more. Not only can you restore the data in a particular table, you can do this at a much finer level of granularity. First, you can see the differences between a table in the backup and the live database, and you can control which individual rows should be restored.

Anyway, back to SQL Compare 7 Beta 1. You can download it from http://www.red-gate.com/messageboard/viewtopic.php?t=6730

It was quite a challenge to implement reading schemata information from backup files. First, the core system tables are different in SQL Server 2000, 2005 and 2008. Some objects, like XML schema collections, have very cryptic representations, and there is of course backup compression in SQL Server 2008. We are still working on row and page compressions. Of course we support Red Gate’s SQL Backup files, both compressed and encrypted ones, but the tool works with SQL Server’s native backups just as well.

Another major feature for this beta is support for SQL Server 2008. Although we will not see SQL Server 2008 RTMed until at least Q3 2008, when it is out, we hope to have SQL Compare 7 ready and waiting for it. In the meantime, you are encouraged to download and try this beta. Please let us know if you find any issues with it, or if you think that there is something we have missed from this product. The forum for this beta is on http://www.red-gate.com/messageboard/viewforum.php?f=78

Regards,

  Andras

Among the large number of new features in SQL Server 2008 there are two, almost insignificant features, that I use almost constantly. These are variable initialization and the values clause of insert statements that now accept multiple rows.

When playing with SQL one inevitably declares some variables. After the declaration, and as per the SQL standard, we can use the SET statement to assign a value to a variable:

DECLARE @a int, @b int

SET @a = 1

SET @b = 2

If we do not stick to the standard, then we can use a SELECT statement to assign values to several variables in a single statement:

SELECT @a = 1, @b =2

Sometimes, using SELECT is indeed our only choice, e.g. when we want to get both the row count and the error code from the last statement. There is no SET alternative to the following statement:

SELECT @RowCount = @@ROWCOUNT, @ErrCode = @@ERROR

Anyway, SQL Server 2008 allows us to declare and initialize variables in a single statement, like:

DECLARE @a int = 1, @b int = 2

While I still do not understand why saving on the number of lines of code on my screen makes me feel better (two 19’’ screens provide me with plenty of space :) ), somehow I like this feature.

Another small extension to T-SQL that prevents me from including more typos is adding multiple rows in an insert statement:

DECLARE @table TABLE ( a INT, b INT )

INSERT INTO @table VALUES (1,1), (2,2), (3,3)

The INSERT statement here inserts three rows into the @table. This is something I got used to before, and was missing from SQL Server.

It is nice to see that in SQL Server 2008 Microsoft is adding such syntactic sugar in addition to the number of other improvements, like spatial data types, data compression, transparent data encryption, etc. I’ve been talking about these improvements at VBUG and NxtGen events during the last few weeks, so in my blog I’d also like to say thanks to the people who attended and organized these events.

SQLBits was held in Reading this Saturday and it certainly superseded my expectations. There were over 300 people, excellent talks and plenty of opportunities to meet others.

There were four parallel sessions, one for BI, Dev, Katmai and DBA, and since there is only one me, it was, as always, difficult to choose.

One thing that probably tops my list of interesting findings for the day is the “British language”. Since the event was in the UK, this was of course bound to happen. And it did happen at least four times in Dave McMahon’s entertaining session. He liked using “SET LANGUAGE ‘British’”. This is a statement that certainly increases the comfort level of SQL Server. It is useful when you want to parse date strings in your session, and these strings are following the format of a different culture. Let’s say, they are in the format commonly used in the UK. You may not want to play with SET DATEFORMAT, just type in “SET LANGUAGE ‘British’”.  Note that sys.messages is looked up based on the language_id, which in case of British is still 1033, so do not expect different error messages after using SET LANGUAGE ‘British’ :)

There were of course more interesting bits at this event, but I leave them for later. For now I just want to say thanks to Simon Sabin and Tony Rogerson for organizing the event, and of course, many thanks to those who attended my session.

Pivots in SQL Server 2005 can rotate a table, i.e. they can turn rows into columns. PIVOTs are frequently used in reports, and they are reasonably simple to work with. However, I've seen quite a few questions about this operator. Most questions were about the column list in the PIVOT statement. This list is fixed, but many times the new columns are determined by the report at a later stage. This problem is easily solved when we mix pivots with dynamic SQL, so here is a very simple example about how to dynamically generate the pivot statement:

PIVOT allows you to turn data rows into columns. For example, if you have a table like this (I use only three months here for simplicity):

CREATE TABLE Sales ([Month] VARCHAR(20) ,SaleAmount INT)

INSERT INTO Sales VALUES ('January', 100)
INSERT INTO Sales VALUES ('February', 200)
INSERT INTO Sales VALUES ('March', 300)

SELECT * FROM SALES
 

Month             SaleAmount
----------------  -----------
January           100
February          200
March             300 

Suppose we wanted to convert the above into this:

 
January     February    March
----------- ----------  ----------
100         200         300

We can do this using the PIVOT operator, as follows:

SELECT  [January]
      , [February]
      , [March]
FROM    ( SELECT    [Month]
                  , SaleAmount
          FROM      Sales
        ) p PIVOT ( SUM(SaleAmount)
                    FOR [Month] 
                      IN ([January],[February],[March])
                  ) AS pvt

However, in the above example, I have the column names fixed as the first three months. If I want to create a result in which the columns are dynamic (for example, they are read from a table), then I need to construct the above query dynamically. To demonstrate this let’s look at the following example:

In the first table I have the column names I want to use:

CREATE TABLE Table1 (ColId INT,ColName VARCHAR(10))
INSERT INTO Table1 VALUES(1, 'Country')
INSERT INTO Table1 VALUES(2, 'Month')
INSERT INTO Table1 VALUES(3, 'Day')

In the second table I have the data. This consists of a row identifier (tID), a column ID (ColID) that refers to the column type in Table1, and a value:

CREATE TABLE Table2 (tID INT,ColID INT,Txt VARCHAR(10))

INSERT INTO Table2 VALUES (1,1, 'US')
INSERT INTO Table2 VALUES (1,2, 'July')
INSERT INTO Table2 VALUES (1,3, '4')
INSERT INTO Table2 VALUES (2,1, 'US')
INSERT INTO Table2 VALUES (2,2, 'Sep')
INSERT INTO Table2 VALUES (2,3, '11')
INSERT INTO Table2 VALUES (3,1, 'US')
INSERT INTO Table2 VALUES (3,2, 'Dec')
INSERT INTO Table2 VALUES (3,3, '25')

Now I would like to retrieve data from these two tables, in the following format:

tID         Country    Day        Month
----------- ---------- ---------- ----------
1           US         4          July
2           US         11         Sep
3           US         25         Dec 

In other words I want to turn the data rows in Table2 into columns. If I had a fixed set of columns for the result, i.e. the columns Country, Day, and Month were fixed, I could use SQL Server 2005’s PIVOT operator in a query like:

SELECT  tID
      , [Country]
      , [Day]
      , [Month]
FROM    ( SELECT    t2.tID
                  , t1.ColName
                  , t2.Txt
          FROM      Table1 AS t1
                    JOIN Table2 
                       AS t2 ON t1.ColId = t2.ColID
        ) p PIVOT ( MAX([Txt])
                    FOR ColName IN ( [Country], [Day],
                                     [Month] ) ) AS pvt
ORDER BY tID ;

However I need to construct this query dynamically, because the column names Country, Day, and Month are specified in a table, and can be changed independently from my query. In our case these columns are given in Table1.

In the first step to generate the final pivot query I need to create the list of columns, in this case [Country], [Day], [Month].

Since there is no string concatenation aggregator in SQL (a concatenation aggregator would not be deterministic without some order restriction), and since the column names are stored in rows of a table, I need to flatten these columns into a single row or variable. There are various solutions to achieve this. One solution would be to use a query like:

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = COALESCE(@cols + ',[' + colName + ']',
                         '[' + colName + ']')
FROM    Table1
ORDER BY colName

This query works both on SQL Server 2000 and 2005. It is efficient, but some may not like it because it uses the same variable (@cols) on both sides of an assignment. Another solution that works on SQL Server 2005 only is to use XML PATH.

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + t2.ColName
                        FROM    Table1 AS t2
                        ORDER BY '],[' + t2.ColName
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'

This second query (I’ve seen this posted by Peter Larsson) has the advantage that it does not use the @cols variable on the right hand side. I like this solution more, since this can be extended as a general string concatenation aggregate in more complex queries.

Both of the above queries generate, from Table1, the string: ‘[Country],[Day], [Month]’. This column list is used twice in the pivot query that we aim to construct. Once it is use in the list of columns that we want to retrieve, and once it is used as the list of values that should become columns. Having constructed this list of columns above, we can just concatenate it with the missing parts of the pivot query like:

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT tID, '+
@cols +'
FROM
(SELECT  t2.tID
      , t1.ColName
      , t2.Txt
FROM    Table1 AS t1
        JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p
PIVOT
(
MAX([Txt])
FOR ColName IN
( '+
@cols +' )
) AS pvt
ORDER BY tID;'

Executing this with

EXECUTE(@query)

will give us the expected result: a table that is pivoted and shows columns that were specified in a table:

tID         Country    Day        Month
----------- ---------- ---------- ----------
1           US         4          July
2           US         11         Sep
3           US         25         Dec