Interview Questions Answers.ORG
Interviewer And Interviewee Guide
Interviews
Quizzes
Home
Quizzes
Interviews Databases Programming Interviews:BtrieveClipperData ModelingData StructuresDatabase AdministrationDatabase Administrator (DBA)Database AnalystDatabase DeveloperDB DevelopmentEDI/Data Integration ExpertFirebirdHierarchicalIBM DB2InformixJava DatabaseMariaDBMicrosoft Access DeveloperMongoDBMS SQL ServerMySQL ProgrammingNetworkNoSQLObject RelationalPostgrePostgreSQLProgressRDBMSRelationalSQLSQL AdministratorSQL and PL/SQLSQL Notification ServicesSQL server 2008SSRSStored ProcedureSybaseTeradata
Copyright © 2018. All Rights Reserved
SQL server 2008 Interview Question:
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
Copyright 2007-2024 by Interview Questions Answers .ORG All Rights Reserved.
https://InterviewQuestionsAnswers.ORG.
https://InterviewQuestionsAnswers.ORG.