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:
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.
4 Responses to “Row Value Constructor Support in SQL Server 2008”
Sorry, the comment form is closed at this time.