Table Valued Parameters in SQL Server 2008

SQL Server 2008 introduces a new feature called Table Valued Parameters. In the earlier versions of SQL Server it was not possible to pass a table variable as a parameter to a stored procedure or a function. However, in SQL Server 2008 it is now possible to pass multiple rows to SQL Server in one go by storing the values in a table variable and later passing it to a stored procedure or a function as a READONLY input parameter. This is one of the greatest performance improvements when handling large volume of inserts. Prior to the introduction of Table Valued Parameters, if you had to insert more than one row of data into a table, then you had to execute the insert statement or a stored procedure which performs the insert as many times. With the introduction of Table Valued Parameters in SQL Server 2008 you can now send multiple records in a single TSQL statement or a stored procedure thereby reducing multiple round trips between the client and the server, thereby improving performance for applications passing large volume of data to SQL Server.

Advantages of Table Valued Parameters

• It is easier to maintain and provides better performance than the use of temporary tables
• Reduces multiple round trips between client and the server
• It is much easier to program and use Table Valued Parameters
• It doesn’t cause the statements to recompile
• It enables you to include complex business logic in a single routine
• They have a well defined scope at the end of which they are automatically cleared

How to use Table Valued Parameters
To create and use Table Valued Parameters you need to perform the below steps:

1. Create a User Defined Table Type with the same structure as that of the table, which can be passed as a Table Valued Parameter to a Stored Procedure or a Function 
2. Create a stored procedure which accepts table type as a parameter
3. Declare a variable as Table Type and refer the Table Type which you have created earlier
4. Populate the Table Type Variable by using an insert statement
5. Once the Table Type Variable is created and populated then the next step will be to pass the Table Type Variable as a parameter to the stored procedure or a function  

Limitations of Table Valued Parameters
There are few limitations with respect to the usage of Table Valued Parameters and they are mentioned below.

1. Statistics are not maintained on columns of Table Valued Parameters
2. One of the important things to be noted is that the Table Valued Parameter must be passed only as READONLY parameters. This also means that you will not be able to perform Data Manipulation Language (DML) operations such as INSERT, UPDATE or DELETE on a Table Valued Parameter
3. It is not possible to use Table Type Variables as OUTPUT parameters

Example of Table Valued Parameters in SQL Server 2008
Follow the below steps in the same order to know more about Table Valued Parameters feature which was introduced in SQL Server 2008.

Create SQL2008TVP Database
The initial step will be to create SQL2008TVP database by executing the below TSQL Query.

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'SQL2008TVP')
DROP DATABASE [SQL2008TVP]
GO

CREATE DATABASE SQL2008TVP
GO

Create CricketTeams Table in SQL2008TVP Database
The next step will be to create a new table named CricketTeams within the SQL2008TVP database.

USE SQL2008TVP
GO

IF OBJECT_ID (N'dbo.CricketTeams', N'U') IS NOT NULL
    DROP TABLE dbo.CricketTeams;
GO

/* Create CricketTeams Table*/
CREATE TABLE dbo.CricketTeams
(   
    CricketTeamID TINYINT NOT NULL PRIMARY KEY,
    CricketTeamCountry NVARCHAR(30),
    CricketTeamContinent NVARCHAR(50)
)
GO