Table-Valued Parameters in SQL Server

In the latest update of the SQL Server, the table-values parameters are used to transmit data from multiple rows into a T-SQL statement that is stored in the form or procedure or function.

By using multiple records in a single statement or routine, round trips to the server can be reduced. You can declare this using a table that is defined by the user. In 2008, the initialization of  User-defined table types (UDTTs) and TVPs Table-valued parameters (TVP) were done  for use in SQL server. Before that, passing a variable through the table was challenging. Hence they made it possible by easily variable as a parameter through stored procedures. You can easily rectify this and with added features, this function was enhanced in SQL Server 2008 and above. By using this, now you can send multiple rows of data and store it in procedure or function using restoration techniques like temporary table creation, or passing many values through a parameter as a table variable that can be passed as a parameter. In this blog, we’re able to give you a demonstration with the help of our .net development team. Take a look!

1. Create a User-defined table type in SQL (UDTT)

You can predefine the user definition of tables with functions that are created using a schema. We also call it a schema definition that can be created using temporary data also known as UDTTs(User-defined table types).

These user-defined table types support almost the same features as normal data tables like primary keys, default values, unique constraints, etc. It is referred from a table that can be passed through parameters like stored procedures and functions.

For a table-valued parameter, we need a user-defined table type (UDTT).  UDTT can be created with the following T-SQL statement.

CREATE TYPE UDTT_Country AS TABLE(
 
    CountryName nvarchar(100),
 
    CurrencyName nvarchar(50)
 
)
GO

Note: Syntax for creating a user-defined table type is similar to performing a normally created table. As we know, there is no availability of a user interface in SQL server management studio. Primary Key, Indexes, Constraints, Computed columns, Identity columns can be declared in UDDT definition. However, foreign keys are not available.

There is no ALTER statement available for user defined table type (UDTT). For modification, we will need to use DROP and CREATE.

2. Table-Valued Parameter in Stored Procedure

The utilization of table-valued parameters is almost equivalent to other parameters. For other data types, we will have to give UDTT a name.  Table-Valued Parameter allows us to pass multiple columns and rows as input to the stored method.

The table values parameters must be passed through READONLY parameters. All the DML operations such as INSERT, DELETE and UPDATE cannot be stored in any procedure. You can only use functions like SELECT statements.

Given below is an example of a table-valued parameter in the stored procedure. Table Valued Parameter can’t be used as OUTPUT parameter in stored procedures.

CREATE PROCEDURE USP_AddCountries
 
    @Countries UDTT_Country READONLY
 
AS
 
BEGIN
 
    INSERT INTO CountryList (CountryName, CurrencyName)
 
    SELECT CountryName,CurrencyName FROM @Countries
 
END
 
GO

The use of table-valued parameters in user-defined functions is similar.

3. Execution from T-SQL statement

To execute a stored procedure, which has a table-valued parameter, we need to create a table variable, which references UDTT.   Following is an example of execution.

4. Execution from C# code

To execute stored procedures from .net code, we have to define parameters as Structured parameters.

Structure data type accepts DataTable, DbDataReader or IEnumarable<SqlDataRecord>. In the following example, the first is using a data table, while the second is using IEnumarable<SqlDataRecord> for List Records. The third example shows how to use table-valued parameters with a dapper.

Using Data Table

status void TableParameterUsingDataTable()
{  DataTable dtCurrency = new DataTable();
    dtCurrency.Columns.Add("Country", typeof(string));
    dtCurrency.Columns.Add("Currencyname", typeof(string));
    dtCurrency.Rows.Add("India", "Indian Rupee");
    dtCurrency.Rows.Add("USA", "US Dollar");
 
SqlConnection connection =new SQlConnection(connectionString);
connection.Open();
SqlCommand cmd = new SqlCommand("USP_AddCountries", connection);
cmd.CommandType = CommandType.StoredProcedure;
 
//Pass tabel valued parameter to Store Procedure
SqlParameter sqlparam = cmd.Parameters.AddWithValue("@Countries", dtCurrency);
SqlParam.SqlDbtype = SqlDbType.Structured;
cmd.ExecuteNonQuery();
connection.Close();

Using List

static void TableParameterUsingList()
{
    //Local Funtion
    IEnumerable<SqlDataRecord> CreateSQLDataRecords(IEnumerable<Country>) countries)
   {
        SqlMetaData[] metaData = new SqlmetaData[2];
        metaData[0] = new SqlMetaData("Countryname", SqlDbType.NVarChar, 100);
       metaData[1] = new SqlMetaData("Currencyname", SqlDbtype.NVarchar,50);
       SqlDataRecord record = new SqlDataRecord(metaData);
       foreach (var c in countries)
       {
           record.SetSqlString(0, c.Countryname); 
           record.SetSqlString(1, c.Currencyname);
       };
  }
  List<Country> currecnylist = new List<Country>
  {
          new Country("India", "Indian Rupee"),
          new Country("USA", "US Dollar"),
  };
IEnumerable<SqlDataRecord> sqlDataRecords = CreateSqlDataRecords(currencyList);
 
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand cmd = new SqlCommand("USP_AddCountries", connection);
cmd.CommandType = CommandType.StoredProcedure;
 
//Pass table Valued parameter to Store Procedure
Sqlparameter sqlParam = cmd.parameters.AddWithvalue("@Countries", sqlDataRecords);
sqlParam.SqlDbtype = SqlDbtype.Structured;
cmd.ExecuteNonQuery();
connection.Close();
}

Using Dapper

static void TableparameterUsingDapper()
{ 
     List<Country> currencyList = new list<Country>
       {
            new Country("India", "Indian Rupee"),
            new Country("USA", "US Dollar")
};
DataTable dtCurrency = new DataTable();
using (var reader = ObjectReader.Create(currencyList))
{
      dtCurrecny.Load(reader);
}
//For DataTable, start from here
SqlConnection connection =  new SqlConnection(connectionString);
DynamicParameters parameters = new DynamicParameters();
parameters.Add("@Countries", dtCurrency.AsTableValuedParameter("UDTT_Country"));
connection.Query("USP_AddCountries", parameters, commandType: CommandType.StoredProcedure);
}

5. Modifying data with Table-valued Parameters (Transact-SQL)

To perform set-based data modifications impacting different aspects of rows through execution of a single statement query. You can see a good impact on the table-valued parameters. Say for instance,  you have all the rights to choose relevant rows and add them to a database table. You can also perform DML operations such as create, delete and update the table-valued parameter by adjoining it with a table that needs upgradation.

The below-depicted UPDATE statement explains how to use Transact-SQL to use Table-valued parameters by performing a join with Countries table.

While you are using table-valued parameters, you can use the function JOIN in the FROM clause. We can also call this table-valued parameter “Edited countries” as shown in the image below.

UPDATE dbo.Countries  
 
    SET Countries.CountryName = editedCountries.CountryName  
 
    FROM dbo.Countries INNER JOIN @tvpEditedCountries AS editedCountries  
 
    ON dbo.Countries.CountryID = editedCountries.CountryID;

The below-mentioned Transact-SQL statement closely defines how we can choose the specific set of rows from a table-valued parameter.

INSERT INTO dbo.Countries (CountryID, CountryName)  
 
SELECT newCountries.CountryyID, newCountries.CountryName FROM @tvpNewCountries AS newCountries;

In the above query, the INSERT option closely defines a single set-based operation. 

6. Using Memory-Optimized Table-valued Parameters

This memory-optimized table value parameter is an efficient way of managing data structure and memory-optimized tables utilizing the same memory-optimized algorithms. This will maximize the efficiency of the process by accessing the table variables from a compiled native module. 

Using the same concept, it is quite possible to initiate memory-optimized Table-valued parameters which have a prime focus of reducing temps activity and use memory-optimized TVPs.

The following example is a clear demonstration of memory-optimized table-valued parameters.

CREATE TYPE Countries_MemOptimized AS TABLE
 
(CountryId  INT PRIMARY KEY NONCLUSTERED  HASH WITH (BUCKET_COUNT = 1000), CountryName VARCHAR(100)) WITH ( MEMORY_OPTIMIZED = ON )

For any syntax, if you see the MEMORY_OPTIMIZED=ON clause then it means this type of table type is memory-optimized. Additionally, you can also create a hash index that will manage the data using the indices of memory-optimized.

CREATE PROCEDURE Usp_InsertCountryMemOpt
 
@ParCountry Countries_MemOptimized READONLY AS
 
INSERT INTO Countries
 
SELECT * FROM @ParCountry

You will now create using the stored procedure with full memory optimization as an input type. Later using the same memory-optimized table value, we can execute Usp_InsertLessonMemOpt procedures.

DECLARE @VarCountry_MemOptimized AS Countries_MemOptimized

INSERT INTO @VarCountry_MemOptimized
 
VALUES ( 4, 'India_MemOptimized')
 
INSERT INTO @VarCountry_MemOptimized
 
VALUES ( 5, 'USA_MemOptimized')
 
INSERT INTO @VarCountry_MemOptimized
 
VALUES ( 6, 'UK_MemOptimized')
 
EXEC Usp_InsertCountryMemOpt @VarCountry_MemOptimized
 
SELECT * FROM Countries

Output

Memory-optimized Table-Value Parameters usage reduces the tempdb activity even though this usage type may increase memory consumption. If we see from the other perspective, we will see that the table-value parameter creates activity based on tempdb files.

7. Table-Valued Parameters vs BULK INSERT Options

When comparing with other set-based parameters that are used to perform updates in large data sets. When we compare it to bulk operations that may include higher startup costs, table-valued parameters which may also need at least 1000 rows as an input.

Table-valued parameters can also benefit from temporary table caching when reused. Table caching enables greater scalability compared to BULK INSERT options.

The table-valued parameters are efficient and perform way better than other equivalent parameters and array implementations.

8. Benefits of Table-valued Parameters

• Simple programming model but may sometimes face complex business logic but can be implemented in a single regular method.
• Reduce round trips to server
• Using Merge Statement, Multiple Insert/Update/Delete operation is possible in a single routine.
• Provides more flexibility over temporary tables.

9. Limitations of Table-valued Parameters

• Table-valued parameters cannot be passed to CLR user-defined functions.
• As we know that SQL Server does not keep data on table-value parameters, it is only possible to index the table-value parameters to support Special or PRIMARY KEY restrictions.
• In the Transact-SQL language, table-valued parameters are read-only. You can’t change the column values in the rows of a parameter with a table value, and you can’t insert or subtract rows. You must inject the data into a temporary table or into a table variable to change the data that is transferred to a stored procedure or parameterized expression in a table-valued parameter.
• You cannot use ALTER TABLE statements to modify the design of table-valued parameters.

10. Conclusion

As explained above, using table-valued parameters, we can send multiple records to a single server trip, and complex business logic can be implemented.