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.

28 questions
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,722
  • 4
  • 23
  • 56
13
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
  • 495
  • 2
  • 5
  • 14
8
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
  • 29,978
  • 4
  • 56
  • 131
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
  • 881
  • 1
  • 10
  • 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
  • 1
  • 10
  • 18
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
  • 4
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
  • 785
  • 1
  • 17
  • 28
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,192
  • 10
  • 29
  • 48
3
votes
1 answer

Procedures using TVPs are slower when the TVP numeric value gets larger?

A legacy application has a nightly job that repeatedly calls some store procedure using a TVP and passes in batches of 10,000 ids that are in sequential order that it needs to process. Now that the ids are in the millions, it seems that this…
Michael B
  • 519
  • 2
  • 12
3
votes
1 answer

Passing an array of integers as a parameter of an (inline) user defined function (SQL Server)

I have TBV function . Like this ALTER FUNCTION [dbo].[fn_Functiont] ( @accessibleIds ListAccesableIds READONLY ) RETURNS TABLE AS RETURN ( SELECT d.*, b.Name AS Name, ps.Name AS PaymentSystemName, c.UserName AS UserName, c.FirstName AS…
So_oP
  • 145
  • 1
  • 7
3
votes
2 answers

Table Value Constructor as Procedure Parameter

I can find information on creating procedures with table-valued parameters, and information on table literals, but I can’t find any mention of the two combined. Suppose I have a procedure defined something like: create type info as table(stuff int,…
Manngo
  • 2,183
  • 6
  • 23
  • 44
3
votes
1 answer

Why are Column Defaults on my User-Defined Table Type (UDTT) not being honored?

I create a table type in SQL: CREATE TYPE [dbo].[MyObject_TableType] AS TABLE ( [Name] VARCHAR(MAX) DEFAULT '', [Index] VARCHAR(MAX) DEFAULT '' ) I build a DataTable and populate it with one DataRow. I provide that table as a parameter to a…
Rainbolt
  • 770
  • 1
  • 9
  • 17
2
votes
1 answer

Understanding execution plans using TVPs

I have some slow running queries (8 seconds across 1M records grouped by months) and have been trying to make sense of the execution plans. We use around 10 TVPs to send in a set of filters that the user chooses and I have an indexed view that…
bhs
  • 131
  • 6
1
2