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.
35 questions
7 votes
2 answers
417 views
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 ...
- 1,342
6 votes
2 answers
346 views
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 ...
- 88.7k
10 votes
3 answers
2k views
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 ...
- 821
0 votes
1 answer
94 views
In-Memory Table Type with Primary Key causing Eager Index Spool
I have defined an In-Memory Table Type to hold a sequence of unique integers for the purpose of passing them between stored procedures. The type has the following definition: CREATE TYPE [dbo].[...
- 165
14 votes
1 answer
2k views
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 ...
- 33.3k
2 votes
1 answer
490 views
Multiple Computed, Dependent Columns on INSERT in SQL Server?
I'm relatively new to database programming and am looking for some some theory/best practices and feedback on an issue involving multiple dependent, cascading, computed columns for an OLTP SQL Server/...
- 21
9 votes
1 answer
303 views
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 ...
- 33.3k
3 votes
1 answer
1k views
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 ...
- 519
-1 votes
1 answer
62 views
insert a variable and table value as parameters to procedure [closed]
CREATE PROCEDURE [dbo].[GetFruitName] ( @quantity int ) AS BEGIN SET NOCOUNT ON; INSERT INTO Table1(fname,fprice,quantity) SELECT f.name,f.price from ...
1 vote
1 answer
3k views
How to pass a table-valued parameter to sp_execute_external_script?
I have a stored procedure that calls an external script via a SQL Server language extension. I would like for my stored procedure to use a caller supplied table valued parameter (TVP) to then submit ...
- 215
6 votes
1 answer
374 views
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 ...
- 163
1 vote
1 answer
3k views
alternatives to passing temp table as a parameter to a stored procedure
some time ago I was developing a simple procedure to get the database sizes on a particular server. Here it is the procedure: USE MASTER GO ALTER PROCEDURE dbo.sp_getDBSpace @Radhe sysname=null, ...
- 17.3k
0 votes
3 answers
159 views
Slow performance matching columns to lists of values
I've made a stored procedure to get all results which match specific students and objectives. CREATE PROCEDURE [dbo].[GetResults] @students [IdList] READONLY, @objectives [IdList] READONLY AS SELECT ...
- 103
4 votes
1 answer
5k views
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 ...
- 143
-1 votes
1 answer
84 views
How to acquire a table result set from "Table - valued function" when the result set has variable structures?
I need to write a Table - valued function from which I can acquire a table result set with different structure each time. another thing is that I need to declare variables inside my function . I've ...
user157749