Table-Valued Parameters aka TVPs are commonly used to pass a table as a parameter into stored procedures or functions. They are helpful in a way, we can use a table as an input to these routines and we can get rid of dealing more complex steps to achieve this process.
Introduction
Most of us have heard this question from the software developers who are familiar with SQL Server:
“How can we pass a list of parameters to a stored procedure or a function?”
Basically, in this article, we will try to find the answer to this question. However, the short answer to this question can be like this:
“When a data-driven application needs a list of parameters to any routines, we can overcome this issue by using the TVPs”. In the next sections of this article, we will learn using table-valued parameters with straightforward examples. This way, we will build a better understanding to answer this question too.
User-defined table types
User-defined table types are the predefined tables that the schema definition is created by the users and helps to store temporary data. User-defined table types support primary keys, unique constraints and default values, etc. However, the noticeable capability of the user-defined table type is that it is used as the referenced table type when we want to pass a TVP to stored procedures or user-defined functions.
User-defined table types definition syntax looks like as below:
|
1 2 3 4 |
CREATE TYPE LessonType AS TABLE (LessonId INT, LessonName VARCHAR(100) ) |
When we execute the above query, we can see the created user-defined table type under the User-Defined Table Types folder in SQL Server Management Studio (SSMS).
Using Table-Valued Parameters in Stored Procedures
TVPs reference their types from the user-defined table so they inherit the schema of the table. In this way, we can obtain a parameter that can transfer the multiple columns and rows into the stored procedure as input. In this manner, the multiple parameter values can pass as an input to the stored and then they can be handled by the stored procedure.
In this first example, we will insert multiple rows into a table with the help of the TVP. Firstly, we will create a table whose name is Lesson.
|
1 2 3 4 |
CREATE TABLE Lesson ( Id INT PRIMARY KEY, LName VARCHAR(50) ) |
In the second step, we will create a stored procedure that can accept LessonType as a parameter. So, the stored procedure will gain the ability to take a table as a parameter. In this stored procedure, we will insert all rows of the @ParLessonType variable to the Lesson table.
|
1 2 3 4 5 |
CREATE PROCEDURE Usp_InsertLesson @ParLessonType LessonType READONLY AS INSERT INTO Lesson SELECT * FROM @ParLessonType |
Tip: The TVPs must be declared read-only therefore we have used the READONLY keyword in the definition of the parameter. The reason for this usage method is that we cannot make any manipulation (INSERT, UPDATE, DELETE) on the TVP in the routine body.
In this last step, we will declare a TVP that is referenced from the LessonType table type and will pass it to the stored procedure.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DECLARE @VarLessonType AS LessonType INSERT INTO @VarLessonType VALUES ( 1, 'Math' ) INSERT INTO @VarLessonType VALUES ( 2, 'Science' ) INSERT INTO @VarLessonType VALUES ( 3, 'Geometry' ) EXECUTE Usp_InsertLesson @VarLessonType |
Finally, when we check the Lesson table, we can see that the multiple rows have been inserted into the table.
|
1 |
SELECT * FROM Lesson |
Using Memory-Optimized Table-Valued Parameters
In-memory OLTP, objects can provide an advantage to improve the performance of the queries. In this context, memory-optimized tables can enable us with more effective data access. Moving from this idea, it seems possible to create memory-optimized TVPs. The main advantage of using memory-optimized TVPs is minimizing tempdb activity.
In the following example, we will create a memory-optimized table type:
|
1 2 3 4 |
CREATE TYPE LessonType_MemOptimized AS TABLE (LessonId INT PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000) , LessonName VARCHAR(100) ) WITH ( MEMORY_OPTIMIZED = ON ) |
In this syntax, the MEMORY_OPTIMIZED =ON clause defines that this table type is memory-optimized. In addition to this, we have created a hash index because the memory-optimized table requires at least one index in order to manage the data.
Now, we will create a stored procedure that uses a memory-optimized table-type as an input.
|
1 2 3 4 5 |
CREATE PROCEDURE Usp_InsertLessonMemOpt @ParLessonType LessonType_MemOptimized READONLY AS INSERT INTO Lesson SELECT * FROM @ParLessonType |
Let’s try to execute the Usp_InsertLessonMemOpt procedure with memory-optimized table-valued parameter.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DECLARE @VarLessonType_MemOptimized AS LessonType_MemOptimized INSERT INTO @VarLessonType_MemOptimized VALUES ( 4, 'Math_MemOptimized ' ) INSERT INTO @VarLessonType_MemOptimized VALUES ( 5, 'Science_MemOptimized ' ) INSERT INTO @VarLessonType_MemOptimized VALUES ( 6, 'Geometry_MemOptimized ' ) EXEC Usp_InsertLessonMemOpt @VarLessonType_MemOptimized SELECT * FROM Lesson |
Monitoring tempdb activity of the Table-Valued Parameters
We mentioned that memory-optimized TVPs do not show activity on the tempdb database. In this part, we will prove this concept. At first, we will launch the performance monitor and clear all existing counters with the delete key.
In the second step, we will click the (+) sign in order to add a new counter. We will find out the SQL Server: Databases and then will add the Write Transaction/sec counter to the Added counters panel for the tempdb database.
At first, we will execute the following query in a loop 100000 times which will use the disk-based TVP:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
TRUNCATE TABLE Lesson GO DECLARE @Counter AS INT=1 WHILE @Counter <= 100000 BEGIN DECLARE @VarLessonType AS LessonType SET @Counter = @Counter+1 INSERT INTO @VarLessonType VALUES ( @Counter, 'Math' ) SET @Counter = @Counter+1 INSERT INTO @VarLessonType VALUES ( @Counter, 'Science' ) SET @Counter = @Counter+1 INSERT INTO @VarLessonType VALUES ( @Counter, 'Geometry' ) EXECUTE Usp_InsertLesson @VarLessonType DELETE @VarLessonType END |
After completion of this query, we will execute the following query which will use the memory-optimized TVP.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
TRUNCATE TABLE Lesson GO DECLARE @Counter AS INT=1 WHILE @Counter <= 100000 BEGIN DECLARE @VarLessonType_MemOptimized AS LessonType_MemOptimized SET @Counter = @Counter+1 INSERT INTO @VarLessonType_MemOptimized VALUES ( @Counter, 'Math' ) SET @Counter = @Counter+1 INSERT INTO @VarLessonType_MemOptimized VALUES ( @Counter, 'Science' ) SET @Counter = @Counter+1 INSERT INTO @VarLessonType_MemOptimized VALUES ( @Counter, 'Geometry' ) EXECUTE Usp_InsertLessonMemOpt @VarLessonType_MemOptimized DELETE @VarLessonType_MemOptimized END |
When we analyze the performance monitor graph, it represents that the only disk-based TVP creates activity on tempdb.
In a word, memory-optimized TVPs usage reduces the tempdb activity despite of that this usage type may increase memory consumption. On the other hand, disk-based TVP creates activity on the tempdb.
You can refer to “How to pass multiple parameters into an Inline table-valued function” part of this article, SQL Server inline table-valued functions to learn TVPs usage details with functions.
Conclusion
In this article, we learned about the Table-Valued Parameter usage details and we also mentioned memory-optimized TVPs performance issues. At this point, we will have to decide which TVP type provides maximum benefit according to the resource consumption and performance balance.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023