Output constants in the rows of a SELECT statement

What if you need to create a temporary table, but you don’t want or need to create a table variable or temporary table? One option is to ‘synthesize’ a table using constants — also known as a row constructor.

Not only is this a viable way to derive a table of values, but it’s also more performant than declaring a table variable and inserting data into it. All those transactions add up.

I feel like this SQL pattern is best used when you have a smallish set of fixed values. A large set of data may be better handled some other way.

SELECT DomainNames
FROM
	(VALUES ('google.com'),
			('bing.com'),
			('duckduckgo.com'),
			('startpage.com'))
	AS SearchProviders (DomainNames)

| DomainNames    |
|----------------|
| google.com     |
| bing.com       |
| duckduckgo.com |
| startpage.com  |

Here’s an example of how to use this pattern to synthesize a table with multiple columns and rows.

SELECT *
FROM
	(VALUES (1, 'google.com'),
			(2, 'bing.com'),
			(3, 'duckduckgo.com'),
			(4, 'startpage.com'))
	AS SearchProviders (Id, DomainNames)

| Id | DomainNames    |
|----+----------------|
|  1 | google.com     |
|  2 | bing.com       |
|  3 | duckduckgo.com |
|  4 | startpage.com  |

From there you can perform operations on the table as you would any other.

WITH Constants_CTE (Id, DomainName) AS
(
	SELECT DISTINCT *
	FROM
		(VALUES (1, 'google.com'),
				(2, 'bing.com'),
				(3, 'duckduckgo.com'),
				(4, 'startpage.com'))
		AS SearchProviders
			(Id, DomainNames)
)
SELECT
	Id,
	DomainName,
	CASE WHEN DomainName = 'duckduckgo.com' THEN 'Preferred'
		 ELSE 'Alternate'
	END as Preference
FROM Constants_CTE

| Id | DomainName     | Preference |
|----+----------------+------------|
|  1 | google.com     | Alternate  |
|  2 | bing.com       | Alternate  |
|  3 | duckduckgo.com | Preferred  |
|  4 | startpage.com  | Alternate  |