Вы можете использовать CTE вместо подзапроса или когда вам нужна рекурсивность.

CTE доступен только во время выполнения инструкции SQL, которая включает его. Предыдущие и последующие утверждения не будут иметь к нему доступа и не будут его видеть. Он ведет себя как подзапрос, но может использоваться несколько раз в следующем select/update.

Этот запрос с подзапросом с подзапросом, используемым дважды:

Select D.* From D
Inner Join (
        Select id value, date From A
        Inner Join B on A.data < B.date
        Inner Join C on C.data > B.date
    ) CTE a c1 on c1.id = D.id+1
Inner Join (
    Select id value, date From A
    Inner Join B on A.data < B.date
    Inner Join C on C.data > B.date
) as c2 on c2.id = D.id-1

Можно заменить на CTE:

; with CTE as (
    Select id value, date From A
    Inner Join B on A.data < B.date
    Inner Join C on C.data > B.date
)
Select D.* From D
Inner Join CTE as c1 on c1.id = D.id+1
Inner Join CTE as c2 on c2.id = D.id-1

Это полезно в данном случае, потому что один и тот же подзапрос не нужно писать несколько раз.

Рекурсивный CTE (это только пример, это не должно быть задание SQL Server для манипулирования строковыми данными, как это):

Declare @data varchar(50) = 'Recursive CTE' 
; With list(id, letter) as (
    Select 1, SUBSTRING(@data, 1, 1)
    Union All
    Select id+1, SUBSTRING(@data, id+1, 1) From list
    Where id < len(@data)
) 
Select * from list

Рекурсивного CTE-выражения могут быть использованы для получения данных в иерархии.

Табличные переменные

Табличные переменные существуют только во время выполнения запроса. Он виден всем SQL-операторам после его создания.

Вы можете использовать их, когда вам нужно передать данные в хранимую процедуру или функция, использующая параметр типа таблицы:

Create Proc test(
    @id int,
    @list table_type_list READONLY
)
begin
    set nocount on
    select * from @list
end

Declare @t table_type_list
Insert into @t(name) values('a'), ('b'), ('c')
Exec test 1, @t

Вы также можете использовать их, когда вам нужно хранить что-то не слишком большое и не требующее индексов. Вы не можете вручную создать индекс, хотя первичный ключ или уникальное ограничение в объявлении таблицы автоматически создаст индекс.

Статистика по переменным таблицы не создается, и вы не можете создать статистику.

Временная Таблица

Временная таблица может использоваться, когда вы имеете дело с большим количеством больше данных, которые выиграют от создания индексов и статистики.

В сеансе любой оператор может использовать или изменять таблицу после ее создания:

create table #temp
Insert into #temp(...) select ... From data
exec procA
exec procB
exec procC

Как ProcA, ProcB, так и ProcC могут выбирать, вставлять, удалять или обновлять данные из #temp.

Таблица # temp будет удалена, как только пользовательская сессия будет закрыта.

Если вы не хотите сохранять временную таблицу между сеансами, вы можете использовать глобальную временную таблицу (##temp). Он будет доступен до тех пор, пока не будет отброшен или сервер перезапущен.