Blog Post

SQL Server : Implementation of Table Valued Parameter (TVP)

,

Some time there will be situation in application , where a

grid is populated with data and user has option to edit records or to add new records and save all his/her  changes in a single click on Save button. How do you save the data in the database? 


At times developers are tempted  to use the

.net  sqlBulkCopy class. Later they will

realize that  the login used in the application

to connect the database server does not have sufficient permission to perform

bulk copy operation.On approaching the DBA,  he will raise his concern on changing the security settings of the login. 

In this post let us go through various option available to

handle this scenario, but I will be concentrating on the new feature in SQL

server 2008 called Table Valued Parameter.Let us assume that the screes

loading a grid with two columns, for example county 

code ,Country name.


Save the record one by one: It is time consuming

process and not the efficient way of doing.This method might creates lots of blocking in a busy table as the

length of transaction increase based on the number of records.


Pass the Data as string: Pass the

parameter as comma separated values and use another delimiter (may be

a semicolon)  as a record delimiter. For

example pass the below string as a parameter to the procedure.

‘IN,India;US,United State;CN,China’ 
Inside the procedure

parse the string using UDF to a table variable and insert/update  the target table . This method is very conventional

way and work with any version of SQL Server. In SQL server there is no in built in function to parse the string and string parsing in costly operation as it is not a set operation. This method might work well with small number of records.It will become

worst when the number of records increase.Implementation of this method  also not very straight forward. In the application, we have to write a custom code to make the records in grid to a string. In the procedure also we have to write user defined function(UDF) to parse the string.

Pass the Data as XML: Pass the record as XML data to the procedure . SQL

server 2005 onwards it support XML data type.The XML representation of the data

is passed into the procedure. Inside the procedure the XML is parsed using

XQuery or sp_xm_preparedocument/OPENXML and insert/update  the target table.This option has performance improvement over multiple procedure call but the performance might affect when the number of records go beyond 5000.Many people will be reluctant to implement this method as they are not much familiar with the XML and XQuery.

sqlBulkCopy Class. From the developers

perspective this is very impressive. Easy to code and implement . This implementation

requires explicit insert/update permission on the target table for the login

used to connect the database. Otherwise the login/user should be member of

db_datawtiter database role. Moreover by default this operation will disable

the check constraints and triggers on the table.In other words,by default the trigger associted with the table will not get fired while inserting/updating the records using this method. To disable the check constraints and triggers, the login required

explicit alter permission on the table. If you try to perform sqlBulkCopy  on a table which has check constraint/trigger, it will throw an error as given below(Assuming that the login does not have permission to alter the table).


"Bulk copy failed. User does not have ALTER TABLE permission on table 'Salesop'. ALTER TABLE permission is required on the target table of a bulk copy operation if the table has triggers or check constraints, but 'FIRE_TRIGGERS' or 'CHECK_CONSTRAINTS' bulk hints are not specified as options to the bulk copy command."

To avoid this, check constraint and Fire

trigger options should be enabled while calling the SQLbulkcopy method in the application code. This

option is quiet fast and efficient in the scenario where we need to

insert/update more number of records like loading the table

from huge excel or csv file.

Table Valued Parameter(TVP):.

This is as a new feature in SQL server to pass the data table (Table variable)

to a store procedure from SQL code or from the application. As per the MSDN

documentation 

Using table-valued parameters

is comparable to other ways of using set-based variables; however, using

table-valued parameters frequently can be faster for large data sets. Compared

to bulk operations that have a greater startup cost than table-valued parameters,

table-valued parameters perform well for inserting less than 1000 rows.”  


The implementation of this method is very straight forward and easy to maintain. Let us see the implementation of  Table Valued Parameter. There are some

restriction in the usage of Table Valued Parameters.

  • Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine. 
  •  You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored procedure. 

Let us create a table,user defined table types and a procedure.Application call this procedure to insert/update record into the target table.


--Table creation

CREATE TABLE Country_lkp(
  
CountryCode     CHAR(3) NOT NULL,
  
CountryName     VARCHAR(50) NOT NULL
)
GO

ALTER TABLE Country_lkp ADD CONSTRAINT Pk_Country_lkp PRIMARY KEY(CountryCode)
GO--Creating user defined data type
CREATE TYPE Country AS TABLE(CountryCode CHAR(3) ,    CountryName VARCHAR(50) )
GO
--The procedure to call from the application
CREATE PROCEDURE Insert_Country_lkp
(   
     @Countrylist AS Country READONLY
)AS
BEGIN
   INSERT INTO
Country_lkp  (CountryCode,CountryName) SELECT * FROM @Countrylist 

END


User defined table types is a new feature in SQL server 2008 to pass multiple records to a SQL routine from SQL code or from the application. After executing the above script, you can see the use defined table data type in object explorer as given below.
























Now let us see how we will call this procedure from application . Please find below a sample code.

DataTable dtCountry = new DataTable();

            dtCountry.Columns.Add("Code", typeof(string));
            
dtCountry.Columns.Add("Name", typeof(string));

            DataRow dr = dtCountry.NewRow();
            
dr["Code"] = "IN";
            
dr["Name"] = "INDIA";
            
dtCountry.Rows.Add(dr);

            dr = dtCountry.NewRow();
            
dr["Code"] = "CN";
            
dr["Name"] = "CHINA";
            
dtCountry.Rows.Add(dr);

            dr = dtCountry.NewRow();
            
dr["Code"] = "US";
            
dr["Name"] = "UNITED STATE";
            
dtCountry.Rows.Add(dr);

            string connString = "Server=MyServer;user id=" + "LOGINNAME" + ";password=" + "PASSWORD" + ";database=MYDATABASE;packet size=4096";
       
            
using (SqlConnection conn = new SqlConnection(connString))
                
{
                conn.Open
();
   
                
using (SqlCommand cmd = new SqlCommand("Insert_Country_lkp", conn))
                
{
       
                    cmd.CommandType
= CommandType.StoredProcedure;
                  
cmd.Parameters.AddWithValue("@Countrylist", dtCountry);
                    
cmd.ExecuteNonQuery();
                
}
            }



After executing this code,you can see three records in the country_lkp table. 

We have different method to tackle this situation. Select the option wisely based on your environment. In my personal opinion, use sqlBulkCopy while loading the data from excel ,CSV or any other sources and load the intermediate table. From there insert/update the target table using a stored procedure. To insert/update the record from a screen (where the number of records to be updated/inserted less than 1000 in most of the scenario) , use either XML or TVP.




If you liked this post, do like my page on FaceBook

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating