Missing Row Constructors?
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.