Intro

When doing SQL surgery, I prefer to break up complicated queries in seperate virtual tables. This way it’s easier to debug and have someone else verify the intermediate results of each section.

It’s just that I keep forgetting all the different methods of creating virtual in-line tables, so I wrote myself a rundown for the next time I inevitably need to re-learn this again. These are the most common flavors I use:

Table Variable

Declare a variable as a table and give it a column structure. Then, fill it with a select statement and use it later:

DECLARE @Table AS TABLE (
    Id UNIQUEIDENTIFIER,
    SomeValue DECIMAL,
    IsDeleted BIT
)

INSERT @Table SELECT * FROM SomeDirtyTable t WHERE t.RowIsClean = 1  

SELECT * FROM @Table WHERE IsDeleted = 0;

Common Table Expression

Define a common table with a select query and use it as an input for a subsequent query:

 --Note: this buddy requires a subsequent expression, but can be reused in the same query
WITH @Table AS
(
    SELECT Id,SomeValue,IdDeleted
    FROM SomeDirtyTable t
    WHERE t.RowIsClean = 1
)

SELECT * FROM @Table WHERE IsDeleted = 0;

Derived Query

Select columns from a virtual table derived in-line by another select:

SELECT clean.Id,clean.SomeValue
FROM
    -- this is where the derived query is used
    (
        SELECT Id,SomeValue,IdDeleted
        FROM SomeDirtyTable t
        WHERE t.RowIsClean = 1
    ) as clean
WHERE clean.IsDeleted = 0

Temporary table

The INTO statement creates a temporary table and populates it with the result of the SELECT expression. The # prefix denotes it as temporary. This means that the table will be dropped when the connection is closed.

SELECT Id,SomeValue,IdDeleted
INTO #Clean
FROM SomeDirtyTable t
WHERE t.RowIsClean = 1

SELECT #Clean WHERE IsDeleted = 0;