BT

Table Value Constructors and Compound Assignments for SQL Server 2008

| by Jonathan Allen Follow 525 Followers on Oct 03, 2007. Estimated reading time: 1 minute |

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

Rate this Article

Adoption Stage
Style

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread
Community comments

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Discuss

Login to InfoQ to interact with what matters most to you.


Recover your password...

Follow

Follow your favorite topics and editors

Quick overview of most important highlights in the industry and on the site.

Like

More signal, less noise

Build your own feed by choosing topics you want to read about and editors you want to hear from.

Notifications

Stay up-to-date

Set up your notifications and don't miss out on content that matters to you

BT