User-Defined Table Type and Table Valued Parameter (TVP) in SQL Server 2008

User-Defined Table Type and Table Valued Parameter (TVP) in SQL Server 2008

 

With SQL Server 2008, you can create a user-defined table type which represents the definition of a table structure. To ensure that the data in a user-defined table type meets specific requirements, you can also create unique constraints and primary keys on this type. Further, to send multiple rows of data to a stored procedure or a function without creating a temporary table or many parameters, you can use a user-defined table type to declare table-valued parameters for stored procedures or functions.

Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. Table-valued parameters offer the following benefits; for example it does not acquire locks for the initial population of data from a client, it does not cause a statement to recompile, reduce round trips to the server, enable the client to specify sort order and unique keys etc. To learn more about these new exciting features and how to use it from .Net application, clich here.