Table-Valued Parameters: Sending Thousands of Rows in One Call

A thousand one-row calls can spend more time crossing the network than inserting data. Table-valued parameters send a typed row set in one call. The gain is real only when the client path and the receiving plan are both measured.

Define a Strongly Typed Row Set

Create a user-defined table type with the columns the application actually sends. The primary key declares uniqueness and gives the engine a useful access structure. A procedure receives the value with READONLY, so it cannot update or delete rows inside the parameter. Validate duplicate and NULL behavior at the type boundary.

CREATE TYPE dbo.OrderLineBatch AS TABLE
(
    ClientLineID int NOT NULL PRIMARY KEY,
    ProductID int NOT NULL,
    Quantity int NOT NULL
);
GO
CREATE OR ALTER PROCEDURE dbo.InsertOrderLines
    @OrderID int,
    @Lines dbo.OrderLineBatch READONLY
AS
BEGIN
    SET NOCOUNT ON;
    INSERT dbo.OrderLine(OrderID,ClientLineID,ProductID,Quantity)
    SELECT @OrderID,ClientLineID,ProductID,Quantity
    FROM @Lines;
END;
GO

The target table is assumed to exist, so adapt its keys and constraints before running the example. I use a unique client line ID to make retry behavior easier to reason about. A TVP is an input set, not a substitute for a transaction strategy or duplicate handling. What should happen if the client repeats a batch after a timeout?

Test Table-Valued Parameters From T-SQL

A variable of the table type is a convenient test harness. Populate it, call the procedure, and verify inserted rows. This confirms the server-side contract before introducing client code. Keep the test in a disposable database or transaction, since this procedure writes to the target table.

DECLARE @batch dbo.OrderLineBatch;
INSERT @batch(ClientLineID,ProductID,Quantity)
VALUES (1,101,2),(2,205,1),(3,101,4);
EXEC dbo.InsertOrderLines @OrderID = 9001, @Lines = @batch;
SELECT * FROM dbo.OrderLine WHERE OrderID = 9001;

Grant callers EXECUTE on the procedure and the needed permissions on the type. A table type is a database object; deploying a changed type can require dropping dependent procedures and recreating them in a controlled order. Version the client contract rather than silently changing column order or types.

Send Table-Valued Parameters From .NET

The .NET SQL client API takes a DataTable or a stream of SqlDataRecord values with SqlDbType.Structured and a schema-qualified TypeName. The Windows PowerShell example uses System.Data.SqlClient from .NET Framework. Call the procedure once. For large batches, streaming avoids holding a duplicate DataTable in memory. Match names, types, lengths, and NULL rules to the server type.

# PowerShell
# Run in Windows PowerShell 5.1 with a valid connection string.
$table = New-Object System.Data.DataTable
[void]$table.Columns.Add('ClientLineID',[int])
[void]$table.Columns.Add('ProductID',[int])
[void]$table.Columns.Add('Quantity',[int])
[void]$table.Rows.Add(1,101,2)
[void]$table.Rows.Add(2,205,1)
$connection = New-Object System.Data.SqlClient.SqlConnection(
    'Server=localhost;Database=Demo;Integrated Security=True')
$command = $connection.CreateCommand()
$command.CommandType = [System.Data.CommandType]::StoredProcedure
$command.CommandText = 'dbo.InsertOrderLines'
[void]$command.Parameters.Add('@OrderID',[System.Data.SqlDbType]::Int)
$command.Parameters['@OrderID'].Value = 9001
$lines = $command.Parameters.Add('@Lines',[System.Data.SqlDbType]::Structured)
$lines.TypeName = 'dbo.OrderLineBatch'
$lines.Value = $table
try { $connection.Open(); [void]$command.ExecuteNonQuery() }
finally { $command.Dispose(); $connection.Dispose() }

That PowerShell block calls the .NET SQL client; replace the connection string and create the target objects before running it. Pointed at my test database, it inserted both rows in one call. In a C# application, create a SqlParameter named @Lines with the same properties and a scalar @OrderID. Do not concatenate a long VALUES list as a substitute. The TVP preserves a typed contract and avoids constructing SQL text from data.

Measure Round Trips Fairly

Compare a row-by-row procedure loop with TVP batches of, for example, 100, 1,000, and 10,000 rows. Time from the client, include serialization and network time, and record server CPU and logical reads. A local SSMS loop hides the network cost that table-valued parameters address. Use the same transaction and constraint behavior for both paths.

I test a warm path and a cold path, and I count successful rows rather than relying on elapsed time alone. An oversized batch can hold locks longer and increase log pressure. The best batch size depends on row width and workload. The point is reducing avoidable round trips while keeping failure recovery understandable.

Know the Estimate Limit of Table-Valued Parameters

Microsoft documents that SQL Server does not maintain statistics on TVP columns. That limits the optimizer's knowledge when the procedure joins a TVP to large tables. SQL Server 2019 table variable deferred compilation improves estimates for local table variables at compatible settings; it does not add column statistics to a TVP. Do not expect that feature to fix TVP estimates.

DECLARE @Lines dbo.OrderLineBatch;
INSERT @Lines VALUES (1,101,2),(2,205,1);
SELECT o.ProductID, SUM(l.Quantity) AS requested
FROM @Lines AS l
JOIN dbo.Product AS o ON o.ProductID = l.ProductID
GROUP BY o.ProductID;

The query assumes a Product table and the table type created earlier. The typed variable makes it runnable in a fresh query window after those objects exist. Inspect actual versus estimated rows and join choice for representative batch sizes. A plan compiled for a small batch can be poor for a large batch. Test the production procedure, not only a one-line insert.

Stage Large Inputs When Joins Need Statistics

If the TVP feeds complex joins, copy its rows into a temporary table and create an appropriate index. SQL Server can maintain statistics on the temporary table. The copy has a cost, so measure it against the improvement in the downstream plan. For a simple insert, the extra staging step can be unnecessary.

DECLARE @Lines dbo.OrderLineBatch;
INSERT @Lines VALUES (1,101,2),(2,205,1);
SELECT ClientLineID,ProductID,Quantity
INTO #Lines
FROM @Lines;
CREATE INDEX IX_Lines_ProductID ON #Lines(ProductID);
SELECT p.ProductID,SUM(l.Quantity) AS requested
FROM #Lines AS l
JOIN dbo.Product AS p ON p.ProductID = l.ProductID
GROUP BY p.ProductID;

Run this in a clean query session after creating the table type and sample Product table, or adapt it inside a procedure that accepts the TVP. I compare total procedure duration, not just the join operator, because a fast join after an expensive copy can still lose overall. Keep the TVP as the transport contract even when staging helps execution.

Match the Type's Indexes to the Join

A TVP cannot be indexed after it reaches the procedure. Its only indexes are the primary key, unique constraints, and inline INDEX clauses declared in the type. If ProductID is the dominant join key, add an inline index on it in the type, or stage into a temporary table. Changing a type is a deployment operation with dependent procedures, so benchmark before modifying a shared contract. I keep type names versioned when clients and server releases cannot happen at once.

Handle Errors and Retries

For an import API, return an accepted batch identifier and the count inserted. Record the identifier in a target-side unique key so a retry after an ambiguous timeout cannot duplicate the same business rows. The network call becomes one operation, but correctness still depends on a durable retry rule.

Use a transaction when the entire batch must succeed or fail together. Return a clear result to the client, and choose an idempotency key if retries are expected. A primary key on the TVP detects duplicate input IDs; a target unique constraint protects persisted data. Neither one alone describes how to recover from a network timeout after the server committed.

A TVP is a practical boundary between an application batch and set-based T-SQL. I keep the row-by-row benchmark as a baseline, the TVP version as the candidate, and an actual plan for the largest realistic batch. If a plan suffers from missing column statistics, temporary staging is an explicit and measurable repair.

Related reading on this blog: 2008: Introduction to Table-Valued Parameters with Example and Temp Table vs Table Variable: Cardinality Estimation.

A TVP is not a promise of a good join plan, it is an efficient typed transport for a row set.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.