VALUES in Microsoft Transact-SQL

Original post at: https://siderite.dev/blog/values-in-microsoft-transact-sql

This blog post is about Table Value Constructors or Row Constructors. While they make intuitive sense and this is how I will present them, they were introduced in Microsoft Sql Server 2008 and because they look like another very old feature, most database developers are not aware of them.

So let's begin with a simple INSERT statement:

CREATE TABLE Position(X INT, Y INT)

INSERT INTO Position
VALUES (1,1),(1,2),(NULL,NULL),(2,1)

So we create a table and we insert some data using the VALUES expression. This is equivalent to

CREATE TABLE Position(X INT, Y INT)

INSERT INTO Position
SELECT 1,1
UNION ALL
SELECT 1,2
UNION ALL
SELECT NULL,NULL
UNION ALL
SELECT 2,1

I've certainly used this SELECT + UNION ALL construct to generate static data in my SQL statements. Sometimes, because it's such an annoying syntax, I've created a table or table variable and then inserted values into it in order to use data in a structured way. But could we use the VALUES expression in other contexts, not just for INSERT statements? And the answer is: Yes! (in Sql Server 2008 or newer)

Here is an example:

SELECT *
FROM (VALUES(1,1),(1,2),(NULL,NULL),(2,1)) as Position(X,Y)

This is not a disk table, nor is it a table variable, but an expression that will be treated as a table with columns X and Y, of type INT. As in a SELECT/UNION ALL construct, the type of the columns will be determined by the first set of values.

You can see a "real life" example in my previous post on how to solve Sudoku using an SQL statement.

Now, while I've explained how to remember the syntax and purpose of Table Value Constructors, there are differences between the VALUES expression used as a TVC and when used in an INSERT statement.

In an INSERT statement, VALUES is just a way to specify data to add and has been there since the beginning of SQL and therefore is subject to constraints from that era. For example, you cannot add more than 1000 rows in an INSERT/VALUES construct. But you can using an INSERT/SELECT/VALUES construct:

INSERT INTO Positions
VALUES (1,2),
       (1,1),
       -- ... more than 1000 records
       (0,1)

-- Error 10738 is returned

INSERT INTO Positions
SELECT x,y FROM (
VALUES (1,2),
       (1,1),
       -- ... more than 1000 records
       (0,1)
) as P(x,y)

-- Works like a charm

Hope it helps!