Get Your Own Blog
If you would like to blog on SQLServerCentral.com then send an email to
Contact the author
for this blog
I have total of 11 years of IT experience with Application development, Database Development and Database Administration. I have worked with different version of SQL server from 7.0 to 2008.Started my carrier as VB ,VC++ and database developer in a banking sector for implementing their core banking solution. Currently working as Database Administrator with wide knowledge in performance tuning, high availability solution, troubleshooting and server monitoring. This blog is my humble attempt to share my knowledge and what I learned from my day to day work.
- Posted on 1 August 2012
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.
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
“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.
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-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.
CREATE TABLE Country_lkp(
CountryCode CHAR(3) NOT NULL,
CountryName VARCHAR(50) NOT NULL
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) )
--The procedure to call from the application
CREATE PROCEDURE Insert_Country_lkp
@Countrylist AS Country READONLY
INSERT INTO Country_lkp (CountryCode,CountryName) SELECT * FROM @Countrylist
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();
DataRow dr = dtCountry.NewRow();
dr["Code"] = "IN";
dr["Name"] = "INDIA";
dr = dtCountry.NewRow();
dr["Code"] = "CN";
dr["Name"] = "CHINA";
dr = dtCountry.NewRow();
dr["Code"] = "US";
dr["Name"] = "UNITED STATE";
string connString = "Server=MyServer;user id=" + "LOGINNAME" + ";password=" + "PASSWORD" + ";database=MYDATABASE;packet size=4096";
using (SqlConnection conn = new SqlConnection(connString))
using (SqlCommand cmd = new SqlCommand("Insert_Country_lkp", conn))
cmd.CommandType = CommandType.StoredProcedure;
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
Leave a comment on the original post
[www.practicalsqldba.com, opens in a new window]