SQLINFO – We play with data and technology

Location : Orion Systems Integrators, infopark, cochin

Tea Break:  4:30, 07-07-2011

Mahesh: Hi Varun, Do you know, what is Table valued Constructor?

Varun: No, Is it TVP?

Mahesh: No it is TVC, I looked into so many blogs, I didn’t understand. Can u explain me after learning it?

Varun: Yes, Sure Mahesh.

 Mahesh is a funny guy who has 6+ years in .Net technology. He is a very hard working guy and technology lover.He is Senior Software Engineer in Orion Systems Integrators. He has quite lot of interest in SQL Server too.

 Most of the days he will ask SQL questions to me, which help me to learn more on SQL Server.

 This was one which blows a fresh air in T-SQL Programming.

What is TVC?

Table valued constructor specifies a set of row value expressions to be constructed into a table. The T-SQL TVC allows

multiple rows of data to be specified in a Single Statement. The table value constructor can be specified in the VALUES clause of the INSERT statement, in the USING <source table> clause of the MERGE statement, and in the definition of a derived table in the FROM clause.Look at the below example.

 (Ref: http://msdn.microsoft.com/en-us/library/dd776382(v=sql.100).aspx)

--One Method of Inserting data into table 

INSERT INTO [Test].[dbo].[CustomerDetails]

            ([Name],

             [Address])

VALUES      (‘Varun’,

             ‘TVM’),

            (‘FAZAL’,

             ‘Delhi’),

            (‘Mahesh’,

             ‘KLM’)

--Select  Methold of Inserting data into table

INSERT INTO [Test].[dbo].[CustomerDetails]

            ([Name],

             [Address])

SELECT‘Varun’,

      ‘TVM’

UNION ALL

SELECT ‘FAZAL’,

       ‘Delhi’

UNION ALL

SELECT ‘Mahesh’,

       ‘KLM’

--Intrducing TVC in SQL Server 2008

INSERT INTO [Test].[dbo].[CustomerDetails]

            ([Name],

             [Address])

VALUES     (‘MANU’,

            ‘TVM’),

            ((SELECT [NAME]

              FROM   dbo.customer

              WHERE  customer_id = 1),

             (SELECT [Address]

              FROM   dbo.customer

              WHERE  customer_id = 1));

GO 

Limitations of TVC:

• The maximum number of rows that can be constructed using the table value constructor is 1000
• Only single scalar values are allowed as a row value expression
• A subquery that involves multiple columns is not allowed as a row value expression