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. Personally, I hope the SQL Team decides to go with the “Row Constructors” term, because the other sounds a little too much like Table-valued Parameters - which is another, completely different feature I’ll be covering in another post at a later date. Anyway, Row constructors, as I’ll call them for now, are a new TSQL construct in SQL Server 2008 that leverages some familiar syntax from the INSERT Statement to expose some pretty handy new functionality.

So, if you’re familiar with INSERT syntax, you’ll remember that the way to insert individual values into an inserted row is to use the VALUES clause. (This is, of course, the alternative to inserting valves using a SELECT statement - but that’s a different subject). In Yukon, (SQL server 2005) a VALUES statement looks something like this:

INSERT INTO Contacts (Name, Age, SalesId)

VALUES(‘John Doe’,25,5)

If you want to add additional values, then you would use additional INSERT statements.

INSERT INTO Contacts (Name, Age, SalesId)

VALUES (‘Jane Doe’, 36,6)

 

INSERT INTO Contacts (Name, Age, SalesId)

VALUES (‘Peter Doe’, 49, 7)

 

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)

I’ve had a lot of customers in the past who load up domain value tables through scripts that consist primarily of a host of separate INSERT statements. This new VALUES clause syntax will make it much more convenient to load these sorts of tables. It’s also worth noting the impact this has on the nature of implicit transactions used in this context. Instead of having to explicitly define a transaction context that wraps a whole series of discrete INSERT statements, the entire set of valves is bounded by the single transaction, implicit in the single INSERT statement itself.

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))

 

Here, the VALUES statement is parenthesized and used just like it was a logical table. This has a lot of implications. There’s a whole set of TSQL Statements that are able to use this VALUES clause in places where we used to find a table or a subquery.

OK, so I lied. If you actually execute that line in Katmai, it will fail with a syntax error. That’s because there is another piece of syntax enhancement that needs to be in place in order for the parser to make sense of this. Here’s the statement that actually does work:

                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!

 

And if that’s still not enough, Katmai restores a capability found in SQL 7.0 that was abandoned in Shiloh and Yukon – the ability to include scalar (as in, single value, not multi-column) subqueries as elements in a Row Constructor. Each of the these subqueries can only return one value – useful for extracting minimums or maximums from a table. And again, this same VALUES clause can be used in place of a table in SELECT syntax.

INSERT INTO Contacts (Name, Age, SalesId)

                VALUES ((SELECT Top 1 Name FROM foo),

(SELECT Top 1 Age FROM foo),

(SELECT Top 1 fooId from foo))

 

Note that you cannot mix scalar subqueries with straight values in the same VALUES clause. So this would be invalid:

                VALUES ((SELECT Top 1 Name FROM foo), 35,7)

 

This is just one of many features in the new Katmai CTP. If you haven’t tried it out yet, you can download the public July Community Technology Preview, version 4, from here.  The SQL Team wants your feedback. Here at DataDude, we’re deep into figuring out what all these new features are going to mean to DBPro. Check it out!

Next Week: We’ll cover Variable Initialization and Assignment Operators.

Comments

• Anonymous
  August 23, 2007
  Real nice (and we will use it!), but what everyone was expecting was: Select * From MyTable Where (1,'44') in (Select A, B From Other Table...)

• Anonymous
  October 13, 2007
  Hi Duncan, The following example works for me: DECLARE @Employees TABLE(       EmployeeID INT,       FirstName NVARCHAR(64),       LastName NVARCHAR(64)); INSERT INTO @Employees(EmployeeID, FirstName, LastName) VALUES (1,'John','Smith'),(2,'Peter','Anderson'); INSERT INTO @Employees(EmployeeID, FirstName, LastName) VALUES ((SELECT 1+MAX(EmployeeID) FROM @Employees),'Brad','Johnson');