Table Valued Constructors

Table Valued Constructors, also called Row Constructors, were introduced with SQL Server 2008, but many SQL Server professionals haven’t heard about them. They make it easy to insert multiple rows of hard-coded values into a table with just one insert statement. NOTE: In this post, I’ll abbreviate Table Valued Constructors as TVCs.

I’ll often see people using older techniques to populate sample tables when asking for T-SQL help on the forums. The following example demonstrates two techniques.

CREATE TABLE #test(Col1 INT, Col2 VARCHAR(10));
--Method 1, using UNION
INSERT INTO #test(Col1, Col2)
SELECT 1,'a'
UNION ALL
SELECT 2,'b'
UNION ALL
SELECT 3,'c';

--Method 2, multiple select statements
INSERT INTO #test(Col1, Col2)
SELECT 4, 'd';
INSERT INTO #test(Col1, Col2)
VALUES( 5, 'e');

The first method takes advantage of the UNION ALL operator to combine three SELECT queries into one set of results. The second example uses a separate INSERT statement for each row to be inserted. One statement uses a SELECT with hard-coded values, while the second uses the VALUES keyword.

Beginning with the 2008 version, you can specify multiple rows with the VALUES syntax which is now called TVC. Each set of values must be within parentheses and each set must be separated by a comma. Example 2 demonstrates this technique.

CREATE TABLE #testTVC(Col1 INT, Col2 VARCHAR(10));
INSERT INTO #testTVC(Col1, Col2)
VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E');

The big advantage of TVCs is that you save typing. Of course, looking cool on the forums is a bonus.

While using the TVC to insert multiple rows with one insert statement is pretty fantastic, there are some other interesting uses. You can create a set of results using a TVC within a derived table. Take a look at this example:

SELECT *
FROM
(VALUES ('January'),('February'),
('March'),('April'),('May'),
('June'),('July'),('August'),
('September'),('October'),
('November'),('December')
) AS Months ([Month]);

In the months example above, my TVC is part of the FROM clause. Notice that I must alias the TVC just like a derived table. I also have to provide column names right after the alias.

Another interesting use of TVCs involves the APPLY operator. A feature of APPLY is that columns from the outer query are visible inside the APPLY. Not only can you have hard-coded values as in the previous example, you can use columns from the outer query as well. The following example demonstrates this technique.

SELECT SOD.SalesOrderID, Dates.*
FROM Sales.SalesOrderHeader AS SOD
CROSS APPLY(VALUES('Order Date',OrderDate),
('Due Date',DueDate),
('Ship Date',ShipDate)) AS Dates(TypeOfDate,TheDate);

In this example, using the AdventureWorks database, each row from the SalesOrderHeader table is returned three times, once for each kind of date. Instead of displaying each date in its own column. This example “unpivots” the dates.

Table Valued Constructors can save you some typing and make your code look more organized. And, as you have seen here, they are also capable of some neat little tricks.


Trovato qui.