Table-valued parameters (TVP) are a great feature that was new in SQL Server 2008 that allow you to insert a dataset into a table. Previously, the most common way of doing this was by passing and parsing XML. As I’ve previously posted, TVP’s perform an astounding 94% faster than singleton inserts and 75% faster than XML inserts. But for some reason, TVP’s still aren’t widely used and understood. In this post, I’ll walk you through how to use these and how to query the metadata for TVP’s.
I’ve previously posted about what TVP’s are and how to use them. But in honor of Halloween this week, I’ve updated my demo script:
/* Create some tables to work with */ CREATE TABLE dbo.orders ( order_id INT IDENTITY(1,1) Not Null , orderDate DATE Not Null , customer VARCHAR(20) Not Null CONSTRAINT PK_orders PRIMARY KEY CLUSTERED(order_id) ); CREATE TABLE dbo.orderDetails ( orderDetail_id INT IDENTITY(1,1) Not Null , order_id INT Not Null , lineItem INT Not Null , product VARCHAR(20) Not Null CONSTRAINT PK_orderDetails PRIMARY KEY CLUSTERED(orderDetail_id) CONSTRAINT FK_orderDetails_orderID FOREIGN KEY(order_id) REFERENCES dbo.orders(order_id) ); /* Create our new table types */ CREATE TYPE dbo.orderTable AS TABLE ( orderDate DATE , customer VARCHAR(20) ); GO CREATE TYPE dbo.orderDetailTable AS TABLE ( lineItem INT , product VARCHAR(20) ); GO /* Create a new procedure using a table-valued parameter */ CREATE PROCEDURE dbo.insert_orderTVP_sp @myOrderTable orderTable READONLY , @myOrderDetailTable orderDetailTable READONLY AS BEGIN SET NOCOUNT ON; DECLARE @myOrderID INT; INSERT INTO dbo.orders SELECT orderDate , customer FROM @myOrderTable; SET @myOrderID = SCOPE_IDENTITY(); INSERT INTO dbo.orderDetails SELECT @myOrderID , lineItem , product FROM @myOrderDetailTable; SET NOCOUNT OFF; END GO /* Call our new proc! */ DECLARE @myTableHeaderData AS orderTable , @myTableDetailData AS orderDetailTable; INSERT INTO @myTableHeaderData (orderDate, customer) VALUES (GETDATE(), 'Zombie'); INSERT INTO @myTableDetailData (lineItem, product) SELECT 10, 'Brains' UNION ALL SELECT 20, 'More Brains'; EXECUTE dbo.insert_orderTVP_sp @myTableHeaderData , @myTableDetailData; DELETE FROM @myTableHeaderData; DELETE FROM @myTableDetailData; INSERT INTO @myTableHeaderData (orderDate, customer) VALUES (GETDATE(), 'Vampire'); INSERT INTO @myTableDetailData (lineItem, product) SELECT 10, 'Blood Type O+' UNION ALL SELECT 20, 'Blood Type B-' UNION ALL SELECT 30, 'Blood Type AB+' UNION ALL SELECT 40, 'Blood Type A+'; EXECUTE dbo.insert_orderTVP_sp @myTableHeaderData , @myTableDetailData; /* Check our data */ SELECT * FROM dbo.orders; SELECT * FROM dbo.orderDetails; |
Once you’ve run this, you should see the following data:
order_id orderDate customer ----------- ---------- -------------------- 1 2010-10-28 Zombie 2 2010-10-28 Vampire (2 row(s) affected) orderDetail_id order_id lineItem product -------------- ----------- ----------- -------------------- 1 1 10 Brains 2 1 20 More Brains 3 2 10 Blood Type O+ 4 2 20 Blood Type B- 5 2 30 Blood Type AB+ 6 2 40 Blood Type A+ (6 row(s) affected) |
Now that we’ve successfully created a couple of table types to support our TVP’s, how do we go back and find out which objects we’ve created? You can query the sys.types catalog view to find out. Just search for system_type_id 243, which identifies the record as a table type.
/* Let's check out our new data types */ SELECT name , system_type_id , is_table_type FROM sys.types WHERE system_type_id = 243; GO |
name system_type_id is_table_type -------------------- -------------- ------------- orderTable 243 1 orderDetailTable 243 1 (2 row(s) affected) |
Even better yet, you can use the sys.table_types catalog view. This gives us the same information as sys.types but also gives us the type_table_object_id, which we’ll need shortly.
SELECT name , system_type_id , is_table_type , type_table_object_id FROM sys.table_types; |
name system_type_id is_table_type type_table_object_id -------------------- -------------- ------------- -------------------- orderTable 243 1 917578307 orderDetailTable 243 1 933578364 (2 row(s) affected) |
What if you need to look up the table type definition? You can do this using the type_table_object_id and joining to sys.columns.
SELECT tt.name AS 'table_type_name' , c.name AS 'column_name' , t.name AS 'data_type' FROM sys.table_types AS tt JOIN sys.columns AS c ON tt.type_table_object_id = c.object_id JOIN sys.types As t ON c.system_type_id = t.system_type_id; |
table_type_name column_name data_type -------------------- --------------- --------------- orderTable orderDate date orderDetailTable lineItem int orderTable customer varchar orderDetailTable product varchar (4 row(s) affected) |
And last, but certainly not least, how do we see if any procs are currently using the table types? SQL Server 2008 makes this easy for us with the sys.dm_sql_referencing_entities DMV.
SELECT referencing_schema_name, referencing_entity_name, referencing_id FROM sys.dm_sql_referencing_entities ('dbo.orderTable', 'TYPE'); |
referencing_schema_name referencing_entity_name referencing_id ----------------------- ----------------------- -------------- dbo insert_orderTVP_sp 949578421 (1 row(s) affected) |
If you’re wondering how to implement SQL Server TVP’s in your .NET code, well… I can’t tell you how to do it, but I can point you to a place that can. Stephen Forte has a post that explains how easy it is to do.
So now that you have a better understanding of how to work with TVP’s, why don’t you go implement one in your environment and see how for yourself just how awesome it is? 
Oh, and Happy Halloween!
Minor correction — I believe you want to join sys.types on user_type_id instead.
FROM sys.table_types AS tt
JOIN sys.columns AS c ON tt.type_table_object_id = c.OBJECT_ID
JOIN sys.types AS t ON c.user_type_id = t.user_type_id;