Table-valued parameters in SQL Server 2008 (Part 1)

Published 10 May 09 10:05 PM | MartinBell
At the inaugural meeting of the Manchester SQL Server User Group I gave a talk on Table-valued parameters (TVPs), so for my first post I thought I’d share you some of the information from that session.

A question that commonly occurs in the SQL Server news groups is "How do you pass and array to a stored procedure?" You can tell that it is a very common question, because several MVPs have dedicated time and effort to add pages to their web sites just to cover this issue for instance Aaron Bertrand on aspfaq.com has an article and Narayana Vyas Kondreddi has an article as well on his website; but the daddy of them all has to be the pages Erland Sommarskog has put together on sommarskog.se. There are actually many solutions to this problem and it will depend on which version of SQL Server you are using as to which ones are feasible. Erland investigates the performance of many of the different methods and shows his finding at http://www.sommarskog.se/arrays-in-sql-perftest.html. The results make interesting reading; for example, he found the performance using XML attributes better than using XML elements and choosing which method to use will depend on if you have fixed length values or not.

At the moment there is no in-depth study using SQL Server 2008 which adds a new option to pass multiple values, that is a tabled-valued parameter.

You have always been able to use temporary tables created outside a stored procedure such as (code):

-- Create procedure referencing temporary table which has not been created yet!
 
CREATE PROCEDURE dbo.Get_Residents_From_Tmp_Table
AS
                                SELECT hid.ToString() AS node,
                                                hid.GetLevel() AS [level],
                                                name,
                                                [type]
                                FROM #tmphierarchy
                                WHERE [type]  = 'Resident'
GO

-- Create temporary table using new HierarchyId data type
CREATE TABLE #tmphierarchy ( hid HierarchyId NOT NULL PRIMARY KEY CLUSTERED,
                                                                name varchar(30) not null,
                                                               [type] char(10) not null
                                                )
GO

INSERT #tmphierarchy ( hid, name, type)
VALUES ( hierarchyid::Parse('/'),
                'Seattle Grace Hospital',
                'Hospital' ),
                ( hierarchyid::Parse('/1/'),
                'Cardiology',
                'Department' ),
                ( hierarchyid::Parse('/1/1/'),
                'Preston Burke',
                'Consultant' ),
                ( hierarchyid::Parse('/1/2/'),
                'Cristina Yang',
                'Resident' ),
                ( hierarchyid::Parse('/1/3/'),
                'Erica Hahn ',
                'Consultant' );
GO

EXEC Get_Residents_From_Tmp_Table
GO

INSERT #tmphierarchy ( hid, name, type)
VALUES  ( hierarchyid::Parse('/2/'),
                'General Surgery',
                'Department' ),
                ( hierarchyid::Parse('/2/1/'),
                'Richard Webber',
                'Consultant' ),
                ( hierarchyid::Parse('/2/2/'),
                'Miranda Bailey',
                'Consultant' ),
                ( hierarchyid::Parse('/2/3/'),
                'Meredith Grey ',
                'Resident' );
GO

EXEC dbo.Get_Residents_From_Tmp_Table
GO

But that is not a great solution if the stored procedure requiring the array is called directly from a client application. This example is slightly different to just passing an array, as I am looking at a more complex data structure including the use of the hierarchyid data type which is also new in SQL Server 2008 and that is a whole blog entry unto itself. To be able to pass an array of structures rather than a homogenous array would obviously require some modification to the methods described by Erland.

If you want to use tabled valued parameters the first thing you will need to do is declare a user defined type for the table type such as:

CREATE TYPE udt_HospitalHierarchy AS TABLE ( hid HierarchyId NOT NULL PRIMARY KEY CLUSTERED,
                                                                                                name varchar(30) not null,
                                                                                                [type] char(10) not null
                                                                                                )
GO

This is also new for SQL Server 2008. Being a table you can obviously have a more complex definition than simple delimited string arrays and you can also create a primary key on a table type. If you want to look at the user defined table types the catalog view sys.table_types e.g. for the above definition you will get back the row (I've split this for readability)

Name

system_type_id

user_type_id

schema_id

principal_id

max_length

precision

scale

udt_HospitalHierarchy

243

257

1

NULL

-1

0

0

 

collation_name

is_nullable

is_user_defined

is_assembly_type

default_object_id

rule_object_id

is_table_type

type_table_object_id

NULL

0

1

0

0

0

1

37575172

One of the problems with user defined types which has always been a bit of an issue, is that once they have been referenced you can't drop them (see the "DROP TYPE" topic in Books Online), there is also no ALTER TYPE commands, so once it is defined and used you had better hope it is right!!

Another thing you can't do with user defined table type is define a computed column using a method e.g. the hierarchyid's level method can not the used:

CREATE TYPE udt_HospitalHierarchy_with_level AS TABLE ( hid HierarchyId NOT NULL,
                                                                                                [Level] AS hid.GetLevel() ,
                                                                                                name varchar(30) not null,
                                                                                                [type] char(10) not null
                                                                                                )
GO

This returns error 2785 indicating methods on CLR types can not be used in this context.

Once the user defined type has been declared it can then be used as a parameter to a stored procedure:

-- Create procedure using a table-valued paremeter
CREATE PROCEDURE dbo.Get_Residents_From_Table_Valued_Parameter (
@tvp udt_HospitalHierarchy READONLY
)
AS
                                SELECT hid.ToString() AS node,
                                                hid.GetLevel() AS [level],
                                                name,
                                                [type]
                                FROM @tvp
                                WHERE [type]  = 'Resident'
GO

The parameter has to be declared as READONLY. This restriction means that the values in the TVP can not be updated, inserted or deleted. It also can't be the target of a SELECT INTO or INSERT EXEC statement within the procedure.

The following example shows the TVP in use, anyone who saw my talk at SQLBits IV may recognise my "favourite" hierarchy! (code)

-- Create a table variable using the UDT
DECLARE @h1 udt_HospitalHierarchy
INSERT @h1 ( hid, name, type)
VALUES ( hierarchyid::Parse('/'),
                'Seattle Grace Hospital',
                'Hospital' ),
                ( hierarchyid::Parse('/1/'),
                'Cardiology',
                'Department' ),
                ( hierarchyid::Parse('/1/1/'),
                'Preston Burke',
                'Consultant' ),
                ( hierarchyid::Parse('/1/2/'),
                'Cristina Yang',
                'Resident' ),
                ( hierarchyid::Parse('/1/3/'),
                'Erica Hahn ',
                'Consultant' ) ;

/* Just show what is in there! */

SELECT hid.ToString() AS node,
                                hid.GetLevel() AS [level],
                                name,
                                [type]
FROM @h1

/* Execute the procedure */

EXEC dbo.Get_Residents_From_Table_Valued_Parameter @h1
GO

Calling this procedure will return Cristina Yang.

And to prove it is adaptable you can use a different table variable and pass that to the procedure:

DECLARE @h2 udt_HospitalHierarchy
INSERT @h1 ( hid, name, type)
VALUES ( hierarchyid::Parse('/'),
                'Seattle Grace Hospital',
                'Hospital' ),
                ( hierarchyid::Parse('/1/'),
                'General Surgery',
                'Department' ),
                ( hierarchyid::Parse('/1/1/'),
                'Richard Webber',
                'Consultant' ),
                ( hierarchyid::Parse('/1/2/'),
                'Miranda Bailey',
                'Consultant' ),
                ( hierarchyid::Parse('/1/3/'),
                'Meredith Grey ',
                'Resident' );

SELECT hid.ToString() AS node,
                hid.GetLevel() AS [level],
                name,
                [type]
FROM @h2

EXEC dbo.Get_Residents_From_Table_Valued_Parameter @h2
GO

Calling the procedure this time will return Meredith Grey as the resident. So that is the basics of using TVPs in my next post I'll look into how they can be used with a .NET client, and in the final part I look at performance

Comments

# SimonS Blog on SQL Server Stuff said on May 11, 2009 02:48 PM:

A fellow MVP and member of the SQLBits organising committee has finally pulled his finger out and started

# SQLBits said on May 11, 2009 02:48 PM:

A fellow MVP and member of the SQLBits organising committee has finally pulled his finger out and started

# SQLBits said on May 11, 2009 06:45 PM:

A fellow MVP and member of the SQLBits organising committee has finally pulled his finger out and started

# DBA & Developer Notes « 36 Chambers – The Legendary Journeys: Execution to the max! said on May 20, 2009 07:14 PM:

Pingback from  DBA & Developer Notes « 36 Chambers – The Legendary Journeys:  Execution to the max!

# Table-valued parameters in SQL Server 2008 (Part 2) - Martin Bell UK SQL Server MVP said on May 22, 2009 09:02 PM:

Pingback from  Table-valued parameters in SQL Server 2008 (Part 2) - Martin Bell UK SQL Server MVP

# Martin Bell UK SQL Server MVP said on May 22, 2009 09:10 PM:

Table-valued parameters are a new Feature in SQL Server 2008 in this post I try an look at how they perform compared to other methods you may have previously used.

This Blog

SQL Blogs

Syndication