Existe uma diferença de desempenho entre CTE, sub-Query, tabela temporária ou variável de tabela?

Não há nenhuma regra. Eu acho os Eti mais legíveis, e os Uso a menos que exibam algum problema de desempenho, nesse caso eu investigo o problema real ao invés de adivinhar que o CTE é o problema e tentar reescrevê-lo usando uma abordagem diferente. Há geralmente mais sobre a questão do que a maneira que eu escolhi declarar declarativamente minhas intenções com a consulta.

Há certamente casos em que você pode desvendar CTEs ou remover subqueries e substituí-los por uma tabela # temp e reduzir a duração. Isto pode ser devido a várias coisas, tais como estatísticas estáticas, a incapacidade de obter estatísticas precisas (por exemplo, juntando-se a uma função de valor de tabela), paralelismo, ou mesmo a incapacidade de gerar um plano ótimo por causa da complexidade da consulta (neste caso, quebrando-o pode dar ao otimizador uma chance de luta). Mas há também casos em que o I / O envolvido com a criação de uma tabela # temp pode superar os outros aspectos de desempenho que podem fazer uma forma de plano particular usando um CTE menos atraente.

Muito honestamente, existem muitas variáveis para fornecer uma resposta "correta" à sua pergunta. Não há nenhuma maneira previsível de saber quando uma consulta pode ser dica em favor de uma abordagem ou outra - apenas saiba que, em teoria, a mesma semântica para um CTE ou um único subquery deve executar exatamente o mesmo. Penso que a sua pergunta seria mais valiosa se apresentasse alguns casos em que isso não é verdade-pode ser que tenha descoberto uma limitação na optimizer (ou descobriu um conhecido), ou pode ser que suas consultas não são semanticamente equivalentes ou que um contém um elemento que frustra a otimização. Então eu sugiro escrever a consulta de uma forma que lhe pareça mais natural, e só se desviar Quando você descobrir um problema de desempenho real que o otimizador está tendo. Pessoalmente, classifico-os como CTE, depois subquery, com a tabela #temp sendo um último recurso.

#temp é materializado e CTE não é.

O CTE é apenas sintaxe, por isso, em teoria, é apenas uma subaquática. É executado. # temp is materialized. Assim, um CTE caro em uma junção que é executada muitas vezes pode ser melhor em uma #temp. Por outro lado, se é uma avaliação fácil que não é executada, mas algumas vezes, então não vale a sobrecarga de #temp.

As são algumas pessoas em que não gostam de tabela variável, mas eu gosto deles como os são materializados e mais rápido para criar than # temp. Há momentos em que o otimizador de consulta faz melhor com um #temp em comparação com uma variável de tabela.

A capacidade de criar um PK numa variável #temp ou table dá ao optimizador da consulta mais informação do que um CTE (dado que não é possível declarar um PK num CTE).

Apenas duas coisas que eu acho que tornam sempre preferível usar uma tabela # Temp em vez de um CTE são:

1. Você não pode colocar uma chave primária em um CTE para que os dados que estão sendo acessados pelo CTE terá que atravessar cada um dos índices nas tabelas do CTE em vez de apenas acessar o PK ou índice na tabela temporária.

2. Porque você não pode adicionar restrições, índices e chaves primárias para um CTE eles são mais propensos a bugs se infiltrando e ruim dado.

---

- Um dia quando ontem

Aqui está um exemplo onde # restrições de tabela podem evitar dados ruins que não é o caso no CTE

DECLARE @BadData TABLE ( 
                       ThisID int
                     , ThatID int );
INSERT INTO @BadData
       ( ThisID
       , ThatID
       ) 
VALUES
       ( 1, 1 ),
       ( 1, 2 ),
       ( 2, 2 ),
       ( 1, 1 );

IF OBJECT_ID('tempdb..#This') IS NOT NULL
    DROP TABLE #This;
CREATE TABLE #This ( 
             ThisID int NOT NULL
           , ThatID int NOT NULL
                        UNIQUE(ThisID, ThatID) );
INSERT INTO #This
SELECT * FROM @BadData;
WITH This_CTE
     AS (SELECT *
           FROM @BadData)
     SELECT *
       FROM This_CTE;