PrismitaLEARN · PRACTICE · GROW

What is a table-valued parameter, and when do you use one?

SQL Server T-SQL Intermediate 2–3 Yrs3–5 Yrs Conceptual 1 min read

Quick Answer

A TVP lets you pass a whole set of rows into a stored procedure or function as a single parameter, typed by a user-defined table type. It's the clean way to send a list (of ids, of line items) from application code instead of a comma-separated string or many round-trips.

A table-valued parameter is how you pass a set of rows into a stored procedure in one shot. You create a user-defined table type, declare the parameter as that type marked READONLY, and from the application you hand it a DataTable or a record enumerable with SqlDbType.Structured. It replaces the old hacks of building a comma-delimited string and splitting it, or calling the proc in a loop. The one thing to know is the optimizer assumes a fixed small row count for a TVP, so for a big batch you may want OPTION (RECOMPILE).

  • Define CREATE TYPE IdList AS TABLE (Id int PRIMARY KEY); then CREATE PROC ... (@ids IdList READONLY).
  • TVPs are always READONLY inside the proc.
  • From .NET: pass a DataTable or IEnumerable<SqlDataRecord> with SqlDbType.Structured; Dapper supports it too.
  • Cardinality estimate for a TVP is fixed unless you add OPTION (RECOMPILE) — matters for large sets.
All SQL Server questions More on T-SQL