Systems Engineering and RDBMS

Archive for August 19th, 2007

Row Value Constructor Support in SQL Server 2008

Posted by decipherinfosys on August 19, 2007

One of the new additions that is being made to SQL Server 2008 is the addition of the row value constructors. I have not tested it completely for all the scenarios but here are some examples:

Insert into dbo.decipher_test (col1, col2) values (1, 10), (2, 20), (3, 30)

Likewise, in an update statement:

update decipher_test

set (col1, col2) = (select col3, col4 from dbo.test where col3 = decipher_test.col1)

where colx > 10

Similarly, in a select statement:

select * from dbo.decipher_test where (col1, col2) in (select col3, col4 from dbo.test)

Update (SET and where clause) and Select statement (where clause) support for row value constructors has existed in other RDBMS like Oracle. DB2 LUW, MySQL as well as PostgreSQL support the row value constructors for the insert statement as well. It is good to see that SQL Server is moving in that direction as well. This is in fact an ANSI SQL standard. There are other good T-SQL features also that are coming our way in the next release (like the much awaited MERGE command, better support for the analytic functions – better support for the OVER clause for ranking functions and aggregates etc.) that we will be covering in our future blog posts.

Posted in SQL Server | 4 Comments »