In SQL Server 2008, T-SQL will be getting some welcome enhancements to the syntax itself. Included among these are table value constructors, also known as row constructors, inline variable declaration, and compound assignments.
One of the more annoying aspects of T-SQL is that you have to declare variables on a separate line from where you set their initial value. While this does not actually limit what you can do with variables, it is a tedious distraction. Inline variable declaration is being added with SQL Server 2008, giving you the ability to append "= [value]" at the end of any DECLARE statement.
Compound assignments are something made popular in the C family of languages. This is where one writes "SELECT @x += @y" instead of " SELECT @x = @x + @y". As the last one, it isn't earth shattering but useful none the less.
The final one being discussed today is Table Value Constructors. In the simplest use, it allows one to insert multiple rows at one time. For example:
INSERT INTO dbo.MyTable (a, b) Values (1, 2), (10, 20), (100, 200)
While useful on its own, things really start getting interesting when you apply this syntax to other types of statements. For example, here is an alternate syntax for UPDATE.
UPDATE dbo.OtherTable SET (c, d) = (SELECT a, b FROM dbo.MyTable WHERE b=d)
Certainly this is not the only way one could do it, but it does seem a bit cleaner. Here is another example, this time using SELECT.
SELECT * FROM dbo.OtherTable WHERE (c, d) in (SELECT a, b FROM dbo.MyTable)
Some may be tempted to try the same thing using inner joins, but that can result in duplicate rows if one is not careful. An EXISTS clause can also be used, but they rely on sub-queries that tend to be slower.
Next up on this tour is using table value constructors to replace temp tables. Here is an example from Greg Duncan on using it to create an in-line temp table he joins against.
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