I recently came across a situation where I needed to batch insert, update, and delete 1 to 100,000+ records to SQL Server at any given time.  Traditionally I would generate an xml string and parse it to a SQL Server table variable and do batch set operations from there.  However I heard about SQL Server 2008’s new UDTTs (user-defined table types) which I could pass a DataTable to a stored procedure as TVPs (table-valued parameters).  It sounded very attractive as I wouldn’t have to generate & parse xml strings to table variables in SQL Server.  I also thought it would give me huge performance gains.  I quickly found that UDTTs were not my saving grace after all and were less than impressive in my opinion.

Before I show examples on how to use UDTTs/TVPs from ADO.NET, I’ll give you my takeaways as to why I chose not to use them:

1) They do not scale.  If you profile SQL Server you’ll notice that for each record in your DataTable passed via ADO.NET, SQL Server will essentially create a table variable perform an insert for each record.  Imagine 10,000 inserts into a table variable before you can begin batch set operations… not a good thing.

2) “sp_executesql” does not play well with TVPs from ADO.NET.  I was in a situation where I had to dynamically generate SQL based on the schema of the table.  Therefore I wanted to cache the execution plan with “sp_executesql” as the schema would not change much.  However if you try and use “sp_executesql” with a SqlCommand with the CommandType as a stored procedure and are passing in a UDTT as a SqlParameter, it will fail.  Error:  Could not find stored procedure 'sp_executesql'.  This error only occurs if you try and pass a TVP from ADO.NET.  If you want to pass a TVP to “sp_executesql” with ADO.NET, you have to get creative with having the SqlCommand command’s type be text and use “sp_executesql” that way.

3) SQL Server compiles the execution plan every time.  You can see more info here and here.  If you use SQL Server Management Studio and pass TVPs to a stored procedure, no SQL compilations are performed.  However if you pass a TVP from ADO.NET, compilations will occur every time.  I won’t go into great detail over it as you can read the 2 links above.  However if you have a stored procedure which will be called a lot, you may want to consider this fact.

4) When passing a DataTable as a TVP from ADO.NET, the DataTable must have the exact same number of columns in the exact same order and the same compatible data types.  I can understand the data types, however there are times where I have a DataTable that may be used for other programmatic purposes outside of TVP/SQL operations. If I want to pass it as a TVP to SQL Server, I essentially have to make a new DataTable and refill it perfectly so the column count is the same and in the same order.  Very finicky and requires coordination and communication if anyone changes the UDTT as it will break code easily.

There’s my 4 reasons why I was not impressed with UDTTs/TVPs.  One nice thing is they are very simple to pass via ADO.NET and easy to use within a stored procedure once you set things up correctly.  Below is a quick example how to wire things up if you wish to use TVPs with ADO.NET.

Create the UDTT

IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'udtt_PersonNames' AND ss.name = N'dbo')
DROP TYPE [dbo].[udtt_PersonNames];
GO

CREATE TYPE [dbo].[udtt_PersonNames] AS TABLE(
	ID [int] NOT NULL,
	[Name] [varchar](255) NOT NULL,
	UNIQUE CLUSTERED ([ID] ASC) WITH (IGNORE_DUP_KEY = OFF)
);
GO

Create the Stored Procedure accepting the TVP as a UDTT

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_InsertPersonNames')
	BEGIN
		DROP PROCEDURE dbo.usp_InsertPersonNames
	END
GO

CREATE PROCEDURE dbo.usp_InsertPersonNames (
	@PersonNamesDT AS dbo.udtt_PersonNames READONLY
)
AS
	SET NOCOUNT ON;
	
	INSERT INTO dbo.PersonNames([ID],[Name])
	SELECT [ID], [Name] FROM @PersonNamesDT;

	SET NOCOUNT OFF;
GO

Create an ADO.NET DataTable (same # of columns and order as the UDTT)

        Dim dt As DataTable = New DataTable()
        dt.Columns.Add(New DataColumn("ID", GetType(Integer)))
        dt.Columns.Add(New DataColumn("Name", GetType(String)))

        dt.Rows.Add(New Object() {1, "Joe"})
        dt.Rows.Add(New Object() {2, "Mark"})
        dt.Rows.Add(New Object() {3, "Ted"})

Pass the ADO.NET DataTable as a TVP to the Stored Procedure

        Using conn As SqlConnection = New SqlConnection("Data Source=localhost\sqlexpress;Initial Catalog=UDTT_test;Integrated Security=True;")
            conn.Open()

            Dim cmd As SqlCommand = New SqlCommand()
            With cmd
                .Connection = conn
                .CommandType = CommandType.StoredProcedure
                .CommandText = "dbo.usp_InsertPersonNames"

                Dim tvParam As SqlParameter = New SqlParameter("@PersonNamesDT", dt)
                tvParam.SqlDbType = SqlDbType.Structured
                tvParam.TypeName = "udtt_PersonNames"
                .Parameters.Add(tvParam)

                .ExecuteNonQuery()
            End With
        End Using

UserDefinedTableTypes_Soln.zip (82.82 kb)