Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [table-valued-parameters]

TVPs are a feature of Microsoft SQL Server. Introduced in SQL Server 2008, TVPs allow for sending a structured table of data to a stored procedure or function. Using TVPs can improve performance over sending multiple rows as XML or doing row-by-row processing.

Table-Valued Parameters (TVPs) are a feature of Microsoft SQL Server. Introduced in SQL Server 2008, TVPs allow for sending a structured table of data to a stored procedure or function. The mechanism used to hold the data is a table variable, and the structure of that table variable -- a user-defined table type -- has to be predefined in the database prior to being able to declare it as a stored procedure input parameter type (i.e. the structure cannot be determined at run-time / ad hoc).

While it is possible to declare a table variable from a user-defined table type, either for convenience of using the same structure many time or to just pass data between stored procedures, a major benefit of TVPs is in giving application code (.NET only at the moment? -- still no support in JDBC as of version 6.0 "Preview" / November, 2015) the ability to pass in a structured array / collection of data instead of either serializing multiple rows into an XML document or making individual insert calls. TVPs allow for streaming the data from .NET code into SQL Server so that multiple rows can be handled in true set-based fashion. XML serialization also allows for treating multiple rows as a set, but TVPs have the advantages of:

  • being strongly typed fields (which helps identify bad data before it gets to the database),
  • allowing for constraints such as NOT NULL, CHECK, and UNIQUE (which helps identify bad data before it gets to the database),
  • being more efficient since XML requires:
    • serialization in the app layer,
    • deserialization using the .nodes() function in the database (or OPENXML)
    • more data validation (often, but not always)

For additional information, please see the MSDN documentation for Use Table-Valued Parameters.

35 questions
70
votes
11 answers

Passing array parameters to a stored procedure

I've got a process that grabs a bunch of records (thousands) and operates on them. When I'm done, I need to mark a large number of them as processed. I can indicate this with a big list of IDs. I'm pulling the IDs down along with "payload" data via…
D. Lambert
  • 1,067
  • 1
  • 8
  • 8
21
votes
2 answers

Why must TVPs be READONLY, and why can't parameters of other types be READONLY

According to this blog parameters to a function or a stored procedure are essentially pass-by-value if they aren't OUTPUT parameters, and essentially treated as a safer version of pass-by-reference if they are OUTPUT parameters. At first I thought…
Erik
  • 4,833
  • 4
  • 28
  • 57
18
votes
3 answers

How do I check for a null or empty table-valued parameter?

I have a stored procedure (SS2k8) with a couple table-valued parameters that will sometimes be null or empty. I have seen this StackOverflow post that says that null/empty TVPs should simply be omitted from the calling parameter list. My problem…
Dan
  • 545
  • 2
  • 6
  • 14
14
votes
1 answer

Why does a simple natively compiled stored procedure run out of memory when table variables are used?

My version of SQL Server is SQL Server 2019 (RTM-CU18). The following repro code requires that an in memory filegroup is created. For anyone following along, please remember that an in-memory filegroup cannot be dropped from a database once it is…
Joe Obbish
  • 32,976
  • 4
  • 74
  • 153
9
votes
1 answer

Why does this TVF throw error 9820 with GETDATE() as an input parameter?

I am testing on SQL Server 2019 CU14. Consider the following table-valued function created against a SQL Server database with compatibility level 130 or 140: -- requires database compat 130 or 140 to see the issue CREATE OR ALTER FUNCTION…
Joe Obbish
  • 32,976
  • 4
  • 74
  • 153
8
votes
2 answers

TVP vs JSON vs XML as input parameters in SQL Server

I've found that using TVPs to pass multiple values to a stored procedure is much easier and faster, especially when dealing with a few columns and a few hundred rows, as it eliminates the need for additional data parsing. Why might someone choose…
lifeisajourney
  • 751
  • 1
  • 8
  • 20
7
votes
2 answers

Does OPTIMIZE FOR UNKNOWN do anything for table-valued variables/parameters?

I have a query that is both prone to parameter sensitivity and is suffering from its table-valued parameter. I'm lazy and just want to solve this with query hints. When I'm lazy, I can solve parameter sensitivity with OPTION (OPTIMIZE FOR UNKNOWN).…
J. Mini
  • 1,161
  • 8
  • 32
6
votes
2 answers

Can a JSON array be sent as a stored procedure parameter in a streaming fashion?

With the database setup CREATE TYPE dbo.TableType AS TABLE ( prop1 int, prop2 datetime2, prop3 varchar(1000) ); GO CREATE OR ALTER PROC dbo.TestTableTypePerf @Data dbo.TableType READONLY AS SELECT COUNT(*) FROM @Data; The following code passes…
Martin Smith
  • 87,941
  • 15
  • 255
  • 354
6
votes
1 answer

Generic TVP tradeoffs?

Is there a best practice or strategy for table types used in TVPs? For instance, given the following: CREATE TABLE dbo.Colors ( Id int identity PRIMARY KEY, Name nvarchar(100), ); CREATE TABLE dbo.Shapes ( Id int identity PRIMARY KEY, …
Daniel Liuzzi
  • 163
  • 5
6
votes
1 answer

Is there an upper limit to the SqlDbType.Structured ADO.NET type?

Is there an upper limit to the number of rows\total size of the SqlDbType.Structured type when sending data to a Table parameter in a sproc? I can't find anything to suggest there is so I'm assuming that there'd just be a time-out if a giant data…
BanksySan
  • 1,011
  • 1
  • 12
  • 16
5
votes
1 answer

Getting error "must declare the scalar variable" in SQL function even though it is declared

I am altering function like: ALTER FUNCTION [dbo].[fn_CalculateListing](@Listing TypeListingDatePrice READONLY) RETURNS TypePreviousListingResult AS BEGIN DECLARE @tbl_ListingDateDetails TypePreviousListingResult RETURN…
Kaishu
  • 517
  • 2
  • 12
  • 19
4
votes
1 answer

Efficiently bulk upsert unrelated rows

As part of a manual replication process between databases with different but related schemas, for each table, we identify new and modified records in the source database and feed them via a table valued parameter to a stored procedure in the SQL…
Edward Brey
  • 143
  • 1
  • 7
4
votes
1 answer

Proper use of varbinary type in MS SQL Server

I am at a new position and I am being told to implement a stored procedure that will accept a list of user ids and update a flag. When I suggested the use of a table value parameter (array emulation), I was told to implement as demonstrated below,…
bbqchickenrobot
  • 155
  • 5
4
votes
1 answer

View Existing Table-valued Parameters

I have seen many tutorials on how to create a table-valued parameter - but how do you view a table-valued parameter after it has been created? I would prefer a way of using the GUI in SSMS but T-SQL would suffice as well.
BellHopByDayAmetuerCoderByNigh
  • 795
  • 2
  • 20
  • 32
4
votes
1 answer

Can you create a table param WITHOUT a pre-defined TVP in a SP?

In the body of an SP I have the following line of code: DECLARE @assetCode NVARCHAR(50) = ( SELECT DISTINCT [Asset Code] FROM WHERE hProp = xxx ) Is it possible to do the same with a TABLE variable type? i.e. something along the lines…
Zach Smith
  • 2,430
  • 13
  • 34
  • 65
1
2 3