Table Value Parameters (TVP) - Sql server 2008

Explain Table Value Parameters (TVP) in sql server 2008

A user defined tables are allowed between queries using the Table Value Parameters feature of SQL Server 2008. It also supports defining the tables between a client and a server. Querying, joining, inserting values, updating values etc., can be done as is being done with a normal table. Instead of a query taking a long list of parameters, they simple take TVP as a parameter.

For creating TVP, one need to define a user defined type and the columns which the TVP would hold. The following example creates a customer type which holds an id and name.

CREATE TYPE Customer AS TABLE (id int, CustomerName nvarchar(50))

A dummy table is created for persisting the information.
CREATE TABLE Customers (id int, CustomerName nvarchar(50)) GO

A procedure can also be created which takes a single parameter as a Table Value Parameter. Data can be inserted into two different tables, however from the outside it is a single object and only a single stored procedure is being called.

CREATE Procedure AddCustomers(@customer Customer READONLY) AS
INSERT INTO Customers SELECT id, CustomerName FROM @customer

The TVP as parameter must have the READONLY attribute and TVPs are basically temporary tables persisted on the server in tempdb.
Intellisense in sql server 2008
Prior to SQL Server 2008, IntelliSense was available from third-party products. Red-Gate has a pretty good IntelliSense product..........
PowerShell, included in SQL Server 2008
Windows PowerShell is a new and powerful scripting shell which can be used by developers and administrators for automating server administration.........
Declarative Management Framework (DMF) - Sql server 2008
Declarative Management Framework is a system for managing the instances of SQL Server 2008. It is a policy based system...........
Post your comment