What does SELECT FROM VALUES actually do?
Whenever you needed a small table to use for example for joins or something similar, we worked with temporary tables (hashtables, #-tables) here in the Dojo. The disadvantage of this is that if you want to run your script multiple times, you always had to drop these objects with a DROP TABLE IF EXISTS, which doesn’t really increase the readability. And even if you want to join against a small table in an application, which you don’t want to persist in the database or create as a temporary object in the TempDB, SELECT FROM VALUES is an alternative to the popular #-tables.
How can you work with SELECT FROM VALUES?
Let’s start by creating a small collection of values to query from:
SELECT
NinjaName
,Kills
FROM
(
VALUES
('Chimozuki', 45)
) as t(NinjaName, Kills)
Of course we can also create more than one line of data here:
SELECT
NinjaName
,Kills
FROM
(
VALUES
('Chimozuki', 45)
,('Lushikute', 82)
) as t(NinjaName, Kills)
What you might notice here is that the SQL Server automatically takes the Kills column as an integer value and returns the average as an integer value, so you get 63 as the average. If you specify decimal places for the kills, you will get the exact value 63.5:
SELECT
AVG(Kills)
FROM
(
VALUES
('Chimozuki', 45.0)
,('Lushikute', 82.0)
) as t(NinjaName, Kills)
But you can also execute joins against the table objects as mentioned before. For example, if you want to filter the Application.Countries table against some fixed values in the WideWorldImporters database, you can do this as follows:
SELECT
[CountryID]
,[CountryName]
,[FormalName]
,[IsoAlpha3Code]
,[LatestRecordedPopulation]
,[Continent]
FROM [Application].[Countries] as c
INNER JOIN
(
SELECT FilteredID
FROM (VALUES (1), (11), (100)) as SelectedCountries(FilteredID)
) s
ON c.CountryID = s.FilteredID
Of course, this would be easier with an IN in the WHERE condition, but this is a matter of principle and not of the most elegant solution for some sample queries… Why should you use SELECT FROM VALUES instead of #-tables? It’s simple: your code will be easier if you don’t have to clear the objects yourself and can simply run queries multiple times in the same context without worrying about duplicates. Also, you don’t need to write CREATE statements, you can simply create and use the objects directly in the query. Why should you use # tables instead of SELECT FROM VALUES? Simple: you can index and reuse #-tables, which will improve the performance of your queries. You can also create the columns of #-tables in the correct datatypes, reducing the probability of implicit converts in the execution plan. So, as so often, it is a question of what you want to do with the temporary objects, which object is the right choice for you. As with the Ninja, a katana is a fine thing, but if the enemy is still 50 meters away, shurikens may be the better choice. Adjust the choice of your temporary objects to your opponent.
