Can you explain Table Value Parameters (TVP) in sql server 2008?
Submitted by: AdministratorA 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
GO
The TVP as parameter must have the READONLY attribute and TVPs are basically temporary tables persisted on the server in tempdb.
Submitted by: Administrator
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
GO
The TVP as parameter must have the READONLY attribute and TVPs are basically temporary tables persisted on the server in tempdb.
Submitted by: Administrator
Read Online SQL server 2008 Job Interview Questions And Answers
Top SQL server 2008 Questions
☺ | Explain Sparse Columns of sql server 2008? |
☺ | Do you know PowerShell included in SQL Server 2008? |
☺ | Explain how to store and query Spatial Data? |
☺ | What is Change Data Capture (CDC) feature in sql server 2008? |
☺ | Do you know Enhanced database mirroring in sql server 2008? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |
☺ | Sybase Interview Questions. |