Table Valued Parameters in Sql server 2008

SQL Server 2008 introduced a nice feature called Table Valued Parameters created using user defined table type.

Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. There are tons of benefits, and here is the list provided by BOL:

• Do not acquire locks for the initial population of data from a client
• Provide a simple programming model
• Enable you to include complex business logic in a single routine
• Reduce round trips to the server
• Can have a table structure of different cardinality
• Table valued parameters are strongly typed
• Enable the client to specify sort order and unique keys

Reference URL:http://beyondrelational.com/blogs/nakul/archive/2011/02/21/table-valued-parameters-to-a-table-valued-function-underappreciated-features-of-microsoft-sql-server.aspx