Thursday, August 16, 2007

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  Katmai Day 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...

4 comments:

Mladen said...

maybe i'm mising something but couldn't you do the same thing with
select like so:

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

Greg said...

Likely... But it wouldn't be near as cool... ;)

(And for me, as a developer, the SQL 2008 version is easier to read and seems to make more sense...)

The reason I find this so cool is that it's lowering the impedance mismatch between T-SQL development and traditional (VB,C#, etc) development.

And I firmly believe that there's a strong case for the future of T-SQL (as apposed to those who want to replace all T-SQL with SQLCLR...) and I'm just glad that there's some serious work being done to improvement improve T-SQL. (Work that I feel started in SQL 2005)

Steve said...

If you understand the idea of impedance mismatch and have an idea of what such a system would offer developers then you should explore the D4 language of Dataphor from Alphora. Quite frankly it is light years ahead of MS t-sql. I have blogged about it quite extensively.

www.beyondsql.blogspot.com
www.alphora.com

Steve said...

I hope you'll permit me one more comment in case some people misinterpret the issue. This is from one of my articles:
'Learning D4 is like learning two systems, D4 and relearning sql all over again. D4 will bring a new clarity to sql and will make clear when the use of each system is most appropriate. It is not a one or the other contest, but a learning of when to use which where.

Thanks,