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