Values blocks

Values blocks are a really useful little bit of code in SQL Server. Basically, they are a block of defined values that you can use pretty much like any other data set.

The main place you may have encountered them before is as a source for an input. Often when people need to add a set of records to a table I see something like this:

INSERT INTO dbo.Table1
    (
          Column1
        , Column2
    )
SELECT 1, 'some value'
UNION
SELECT 2, 'some other value';

Or even worse:

INSERT INTO dbo.Table1 (Column1, Column2)
SELECT 1, 'some value';
INSERT INTO dbo.Table1 (Column1, Column2)
SELECT 2, 'some other value';

The first attempt is ok, but it’s bulky and unnecessary, and it needs you to keep typing UNION over and over. The second attempt is actively inefficient, as each row is inserted individually instead of inserting everything as a set.

The cleaner way, that uses a VALUES block, is:

INSERT INTO dbo.Table1
    (
          Column1
        , Column2
    )
VALUES
      (1, 'some value')
    , (2, 'some other value');

This saves you from typing out the UNION all the time, and in my opinion looks neater on the page and makes your block of values easier to read.

The basic rules for a values block are:

1. The data in each row is comma separated
2. Each row of data is wrapped in a set of brackets
3. The rows themselves are also separated by commas
4. Each row has the same number of values (2 in the example above)
5. Each value position has to hold data of the same type for every row (int, varchar in the example above)
6. NULLs are allowed for any value

This use case is useful to know about by itself, but I think the more powerful use of values blocks comes when you start using them in other queries. To do this you need to treat them as a subquery, like in this example:

SELECT
      val.Column2
    , tbl.Column4
FROM dbo.Table2 AS tbl
INNER JOIN
    (
        VALUES
              (1, 'some value')
            , (2, 'some other value')
    ) AS val(Column1, Column2)
    ON tbl.Column1 = val.Column1;

So, all you have to do is wrap the values block in brackets, alias it , and name the columns, and you can use it like any other subquery. The only thing that’s different here is the need to name those columns when you do the aliasing, but you do that simply by listing the names in brackets after the alias.

Interestingly, this is something you can do with regular subqueries as well. It’s probably not something you will use very often as you’re more likely to rename the column in the subquery, but it never hurts to know about these things.

Finally, if I’m using a big values block in a SQL statement, it can be a bit unwieldy to have it in a subquery. It can dominate the rest of the statement, and if it’s long enough you won’t be able to see all of the statement on the screen, and the individual values rarely add much to your understanding of the code. That’s why I will often put the values block in a common table expression at the start of the statement. That also allows you to reuse it if you need to refer to it more than once. Example below:

WITH val AS
(
    SELECT
          val.Column1
        , val.Column2
    FROM
        (
            VALUES
                  (1, 'some value')
                , (2, 'some other value')
        ) AS val(Column1, Column2)
)
SELECT
      val.Column2
    , tbl.Column4
FROM dbo.Table2 AS tbl
INNER JOIN val
    ON tbl.Column1 = val.Column1;

Another option here might be to put the values into a table variable or temp table, but those won’t be as efficient for the query to process. The only time I’d consider that is if there were several statements that wanted to access the same data set, and the data set was small.

So, that’s pretty much everything I know about values blocks. Hope that was useful.