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.