More SQL Server 2008 T-SQL Coolness - Table Value Constructors (or better, Row Constructors)

The Zen of Database Development - Katmai: Looking into Row Constructors

"Thursday is   here at ZoDD, and today I’m going to be discussing the first in a series of posts covering features that show up in SQL Server 2008, using the latest Community Technology Preview, CTP4. This morning’s subject will be the new functionality they are calling Table Value Constructors, and sometimes, perhaps more clearly, Row Constructors. ...

...

But now, in Katmai, these multiple sets of values may all be assigned within a single VALUES clause. This syntax opens up this clause for some very table-like behavior. For instance:

INSERT INTO Contacts (Name, Age, SalesId)
VALUES(‘John Doe’,25,5), (‘Jane Doe’, 36,6), (‘Peter Doe’, 49,7)

But wait-there’s more!

Now that we have established how to use these Row Constructors in the INSERT statement, Katmai expands their usage to allow them to be used as a more general table source.

...

SELECT * 
FROM (VALUES(‘John Doe’,25,5), 
(‘Jane Doe’, 36,6), 
(‘Peter Doe’, 49,7)) psuedoTable(pName, pAge, pSalesID) 

Check out the new aliasing syntax. Now you can not only specify a name for the “table”, but you can name all of the columns in the table as well. This’ll come into heavy play later when we cover INSERT OVER DML. For now, suffice it to say that this effectively let’s you set up tables in script. Note that this aliasing is not necessary for use in the INSERT statement.

This can get more sophisticated:

SELECT *
FROM Employees emp
JOIN (VALUES(‘John Doe’,25,5),  
(‘Jane Doe’, 36,6),
(‘Peter Doe’, 49,7)) psuedoTable(pName, pAge, pSalesID)
ON emp.EmployeeID = pseudoTable.pSalesID

Think about how this can affect your application!

..."

That is just too cool. I can think of SO many ways to use this...

I'm thinking hard about skipping SQL Server 2005 and just going with SQL Server 2008.

 

Related Past Post XRef:
New SQL Server 2008 T-SQL Features - It's the little things that are cool...