SQL Server Brain Basher of the Week #019 - TSQL

Here is a challenge related to TSQL.

What would be the result of below query?

SELECT *
FROM ( VALUES
(1, 'abc'),
(2, 'lmp'),
(3, 'xyz')) AS d (Id, Value);

1. Error that says Incorrect syntax near the keyword 'values'..
2. Error that says Column name or number of supplied values does not match table definition.
3. Error that says Incorrect syntax near the keyword 'SELECT'.
4. Tabular resultset with two columns and three rows.

Though it looks like that the syntax of the code is wrong, it works without any issue. The reason for this is the enhanced VALUES clause. The VALUES clause was enhanced with SQL Server 2008 for supporting multiple row inserts with a single insert statement. This enhancement was not limited to it, it allows us to construct a derived table as if we construct a standard table value constructor.

Now, you know the answer. If you execute the above, you will see a resultset with two columns named as Id and Value with three rows.