In today’s blog I’m going to look at a new feature in SQL Server 2008 which, while it doesn’t provide any functionality not previously possible, it certainly makes a couple of jobs much easier. Some people are calling this feature row constructors. It has basically two uses.
Firstly, you can insert more than one row into a table using a single INSERT statement. Here is the syntax:
INSERT INTO monthly_sales (office_id, sales_year, sales_month, sales_value)
VALUES (42, '2009', '01', 324877)
, (42, '2009', '02', 329483)
, (42, '2009', '03', 381002)
, (89, '2009', '01', 50228)
, (89, '2009', '02', 49763)
, (89, '2009', '03', 49883)
Quite a neat addition, certainly very readable. Of more practical use possibly, the six rows to be inserted now either stand or fall together: if any of the value sets contain invalid data, the whole statement will fail and no rows will be inserted. In situations where this behaviour is desirable, this single statement can replace a potentially complex T-SQL block. (But of course beware that in some cases, you may NOT want each row to rely on others, and this could end up being a curse rather than a blessing).
The second use for row constructors is in formulating an on-the-fly virtual table, of the type you may previously have used a SELECT/UNION combination for.
Consider the following requirement. Using values from the table above, show the monthly sales for office 42 for every month in 2009. Since we only have data for the first three months, we will have to join the table to one we create on the fly. The following statement would probably be the best fit in previous versions of SQL Server:
SELECT ilv.month_name, ISNULL(ms.sales_value, 0) AS sales_value
FROM monthly_sales AS ms
RIGHT OUTER JOIN
( SELECT '01' AS month_name
UNION
SELECT '02' AS month_name
UNION
SELECT '03' AS month_name
UNION
SELECT '04' AS month_name
UNION
SELECT '05' AS month_name
UNION
SELECT '06' AS month_name
UNION
SELECT '07' AS month_name
UNION
SELECT '08' AS month_name
UNION
SELECT '09' AS month_name
UNION
SELECT '10' AS month_name
UNION
SELECT '11' AS month_name
UNION
SELECT '12' AS month_name) AS ilv ON (ilv.month_name = ms.sales_month AND ms.office_id = 42 AND ms.sales_year = '2009')
ORDER BY 1
Results:
01 324877
02 329483
03 381002
04 0
05 0
06 0
07 0
08 0
09 0
10 0
11 0
12 0
However the row constructor allows us to use a neater syntax to build up the inline view. Try this:
SELECT * FROM (VALUES ('01'),('02'),('03'),('04'),('05'),('06'),('07'),('08'),('09'),('10'),('11'),('12')) AS ilv (month_name)
You will see it gives us the same result as the sequence of SELECTs and UNIONs in the main query, i.e. a single column, 12 row table, called ilv, with the column name of month_name. We can then join this into our monthly_sales table as follows:
SELECT ilv.month_name, ISNULL(ms.sales_value, 0) AS sales_value
FROM monthly_sales AS ms
RIGHT OUTER JOIN
( VALUES ('01'),('02'),('03'),('04'),('05'),('06'),('07'),('08'),('09'),('10'),('11'),('12')) AS ilv (month_name)
ON (ilv.month_name = ms.sales_month AND ms.office_id = 42 AND ms.sales_year = '2009')
ORDER BY 1
Results:
01 324877
02 329483
03 381002
04 0
05 0
06 0
07 0
08 0
09 0
10 0
11 0
12 0
You are not restricted to one column, that’s just the example I chose to use.
Although it probably won’t make a major difference to your SQL life, keep row constructors in mind when working on SQL Server 2008.
About the poster
Dean Bullen is Impartica’s SQL course leader and has been working with SQL since 1998. He previously worked for Oracle and has been working with SQL Server since 2006.