Missing Row Constructors?

Published 21 May 09 10:23 AM | MartinBell

Row constructors are a new feature of SQL Server 2008, but if you enter ROW CONSTRUCTOR in the index of the RTM version of Books Online you only get one reference being returned. This is the topic “INSERT (Transact-SQL)”. In the January edition there are also references in “MERGE (Transact-SQL)” and “FROM (Transact-SQL)”. There is no reference to “Programmability Enhancements (Database Engine)” although that topic only has a link to “INSERT (Transact-SQL)” and not the other two!!

In essence that was the reason for my blog post. If you search the internet for ROW CONSTRUCTOR usually you will only get an example for the option to specify multiple values for an insert statement. The example in BOL is:

USE tempdb;
GO
IF OBJECT_ID (N'dbo.Departments', N'U') IS NOT NULL
    DROP TABLE dbo.Departments;
GO
CREATE TABLE dbo.Departments (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30),
    Manager nvarchar(50));
GO
INSERT INTO dbo.Departments
    VALUES (1, 'Human Resources', 'Margheim'),(2, 'Sales', 'Byham'),
           (3, 'Finance', 'Gill'),(4, 'Purchasing', 'Barber'),
           (5, 'Manufacturing', 'Brewer');
GO

In earlier versions of SQL Server you would have needed to use INSERT..SELECT e.g.

INSERT INTO dbo.Departments (DeptID, DeptName, Manager)
SELECT 1, 'Human Resources', 'Margheim'
UNION ALL SELECT 2, 'Sales', 'Byham'
UNION ALL SELECT 3, 'Finance', 'Gill'
UNION ALL SELECT 4, 'Purchasing', 'Barber'
UNION ALL SELECT 5, 'Manufacturing', 'Brewer';
GO

But if you look in the “FROM (Transact-SQL)” topic you will find under the definition for derived table

“derived _table can use the Transact-SQL row constructor (table value constructor) feature to specify multiple rows. For example, SELECT * FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);.”

SELECT *
FROM (    VALUES (1, 'Human Resources', 'Margheim'),(2, 'Sales', 'Byham'),
           (3, 'Finance', 'Gill'),(4, 'Purchasing', 'Barber'),
           (5, 'Manufacturing', 'Brewer') ) Departments (DeptID, DeptName, Manager);
GO

This would have been written in earlier versions of SQL Server as:

SELECT *
FROM (
      SELECT 1 AS DeptID, 'Human Resources' AS DeptName, 'Margheim' AS Manager
      UNION ALL SELECT 2, 'Sales', 'Byham'
      UNION ALL SELECT 3, 'Finance', 'Gill'
      UNION ALL SELECT 4, 'Purchasing', 'Barber'
      UNION ALL SELECT 5, 'Manufacturing', 'Brewer') Departments;
GO

Entering “table value constructor” in the index of BOL only gives “MERGE (Transact-SQL)” and “INSERT (Transact-SQL)” !!!!

In the merge statement row constructors can be used in the USING clause, in the same way as the derived table. So if Barber has staged a coup and taken over as manager of all the departments..

MERGE INTO dbo.Departments AS Dest
USING  (    VALUES (1, 'Human Resources', 'Barber'),(2, 'Sales', 'Barber'),
           (3, 'Finance', 'Barber'),(4, 'Purchasing', 'Barber'),
           (5, 'Manufacturing', 'Barber') ) Src (DeptID, DeptName, Manager)
ON Dest.DeptId = Src.DeptId
WHEN MATCHED THEN
      UPDATE SET DeptName = Src.DeptName,
                 Manager = Src.Manager
OUTPUT $action, inserted.*, deleted.*;
GO
 

If you look at the query plans using ROW CONSTRUCTORS and the older methods, they are exactly the same. For the select statements this is (this is wrapped!):

|--Constant Scan(VALUES:(((1),'Human Resources','Margheim'),((2),'Sales','Byham'),((3),'Finance','Gill'),((4),'Purchasing','Barber'),((5),'Manufacturing','Brewer')))                                                                                          1           2           1           Constant Scan                  Constant Scan                  VALUES:(((1),'Human Resources','Margheim'),((2),'Sales','Byham'),((3),'Finance','Gill'),((4),'Purchasing','Barber'),((5),'Manufacturing','Brewer'))  NULL          5             0             5.157E-06     28          5.157E-06        [Union1015], [Union1016], [Union1017]  NULL     PLAN_ROW                                                         0        1

I think ROW CONSTRUCTORS should have their own topic in BOL, so I created a connect suggestion.

 

Comments

# Matija Lah said on May 21, 2009 02:49 PM:

A very useful suggestion!

Love the title, too! :)

BTW, you haven't voted for a reason?

# Matija Lah said on May 21, 2009 02:51 PM:

Ahem...

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/36b19e68-94f6-4539-aeb1-79f5312e4263.htm

# MartinBell said on May 21, 2009 03:13 PM:

An oversight on my part, unlike the Eurovision song contest you can vote for yourself "douze points" :)

# MartinBell said on May 21, 2009 03:33 PM:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/36b19e68-94f6-4539-aeb1-79f5312e4263.htm

is the "FROM (Transact-SQL)" topic I am not sure why you posted that?

# Matija Lah said on May 22, 2009 09:56 AM:

When one searches for row constructors (esp. for the very first time), and among the top results is a rather lengthy article describing the FROM clause, how easy is it to find any mention of row constructors?

I posted the link to illustrate your point.

# MartinBell said on May 22, 2009 10:20 AM:

That is where CTRL+F is useful! There is only one instance of "ROW C"

# Matija Lah said on May 22, 2009 12:17 PM:

Should BOL be the kind of place where resorting to Ctrl+F is the norm? :)

# MartinBell said on May 22, 2009 12:29 PM:

I wouldn't say norm, that would imply that the topics are too long. and most (IMO) are a reasonable size to scan through. But it can be useful and worth bearing in mind whan you need something specific.

# Martin Bell UK SQL Server MVP said on July 1, 2009 10:54 PM:

New documentation on ROW CONSTRUCTORS indicate the feature has had a name change to Table Value Constructors

# Martin Bell UK SQL Server MVP said on March 15, 2010 10:34 PM:

At the VBUG meeting in Manchester on 3rd March, I was scheduled to talk about Table Valued Parameters

This Blog

SQL Blogs

Syndication