T-SQL 101: 121 Using VALUES Clauses as Row Constructors in SQL Server T-SQL

SQL Server 2008 and later support a VALUES clause. We can use this to construct a table on the fly.

In the example above, you can pretty much ignore the outer query as I just put it there to select from the table that was derived in the VALUES clause.

With VALUES, I put a list of rows that I want to include. They each must have the same number of columns. There can be up to 1000 rows at a time.

Aliases

Importantly, I need to provide aliases for the columns, as the rows don’t include the column names. Notice that I’ve added the aliases after the alias for the derived table. (It must also have one). This is another way that you can alias columns in any derived table.

So I’ve said the first column is called CityID; the second column is called CityName. And so you can see below the query, what the output looks like. It’s just like a table.

Alternatives

A common alternative that developers would use with earlier versions (and still sometimes do), is to use a UNION ALL instead. We could have written the subquery this way:

SELECT 1 AS CityID, 'Brisbane' AS CityName

UNION ALL

SELECT 2, 'Melbourne'

UNION ALL

SELECT 3, 'Sydney'

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-02-26