Systems Engineering and RDBMS

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.

4 Responses to “Row Value Constructor Support in SQL Server 2008”

  1. […] values into the IN Clause of the filter criteria for a given column (or if the RDBMS supports row value constructors, then the set of values). That is not a scalable design. You should be specifying a criteria that […]

  2. […] question from one of our readers: “This is in reference to your post on the new feature of row value constructor in SQL Server 2008. Is there a way to do this in SQL Server 2000 or SQL Server […]

  3. […] pass in the values and execute the proc:declare @rcd_data as RCD_DATA_TYPE – we will use the row value constructor feature of SQL Server 2008 to create the data using a single insert […]

  4. […] can have 1-N Services and 1 Service can have 1-N Responses.  Also note that we are using the row value constructor feature of SQL Server 2008 to do these […]

Sorry, the comment form is closed at this time.

%d bloggers like this: