Tudo Sobre CTEs no Microsoft SQL Server: Conceitos, Recursividade e Desempenho

Common Table Expressions (CTE) é um recurso poderoso no SQL Server, permitindo a criação de consultas mais legíveis, reutilizáveis e até mesmo recursivas. Neste artigo vamos explorar CTEs de forma prática, abordando desde os fundamentos até testes de desempenho comparando CTEs com tabelas temporárias e subqueries.

📘 O que é uma CTE?

Uma CTE (Common Table Expression) é uma expressão temporária que pode ser referenciada dentro de uma única instrução SELECT, INSERT, UPDATE ou DELETE. Ela é declarada com WITH e funciona como uma "view inline".

🧪 Exemplo Básico: Produtos Acima da Média

Vamos listar produtos cujo preço está acima da média geral:

WITH PrecoMedio AS (
  SELECT AVG(ListPrice) AS Media
  FROM Production.Product
)
SELECT p.Name AS Produto, p.ListPrice AS Preco, pm.Media
FROM Production.Product p
CROSS JOIN PrecoMedio pm
WHERE p.ListPrice > pm.Media
ORDER BY p.ListPrice DESC

📌 Resultado: Apenas produtos com preço superior à média são retornados, e o código permanece limpo e reutilizável.

---

🔄 CTE Recursivo: Subindo Hierarquias

Já CTEs recursivos são utilizados para navegar em estruturas hierárquicas.

🎯 Cenário: Funcionários e seus Chefes

Imagine uma tabela Funcionario com campos PK, Nome, Cargo, Chefe, e Salario.

Com uma CTE recursiva, conseguimos listar todos os chefes de um funcionário:

DECLARE @FuncionarioAtual INT = 8

;WITH HierarquiaChefes AS (
  SELECT PK, Nome, Cargo, Chefe, Salario, 0 AS Nivel
  FROM Funcionario
  WHERE PK = @FuncionarioAtual
  UNION ALL
  SELECT f.PK, f.Nome, f.Cargo, f.Chefe, f.Salario, h.Nivel + 1
  FROM Funcionario f
  JOIN HierarquiaChefes h ON f.PK = h.Chefe
)
SELECT PK, Nome, Cargo, Salario, Nivel
FROM HierarquiaChefes
WHERE Nivel > 0
ORDER BY Nivel

🧠 Isso substitui o uso de cursores para percorrer hierarquias de forma elegante e performática.

🚀 Desempenho: CTE vs Tabela Temporária vs Subquery

Comparando a performance dessas três abordagens para identificar o cliente que mais comprou em cada mês.

Abaixo Script para preparar o ambiente para os testes.

use master
go
CREATE DATABASE LiveDB
go
ALTER DATABASE LiveDB SET RECOVERY simple
go

use LiveDB
go


/*********************************
 Cria Tabelas para Hands On
**********************************/
set nocount on

-- SalesTerritory
DROP TABLE IF exists dbo.SalesTerritory
go
SELECT TerritoryID,[Name],CountryRegionCode,[Group]
INTO dbo.SalesTerritory
FROM AdventureWorks.Sales.SalesTerritory

-- Product
DROP TABLE IF exists dbo.Product
go
SELECT *
INTO dbo.Product
FROM AdventureWorks.Production.Product
go

-- ProductSubcategory
DROP TABLE IF exists dbo.ProductSubcategory
go
SELECT *
INTO dbo.ProductSubcategory
FROM AdventureWorks.Production.ProductSubcategory
go

-- Productcategory
DROP TABLE IF exists dbo.Productcategory
go
SELECT *
INTO dbo.Productcategory
FROM AdventureWorks.Production.Productcategory
go

-- Customer
DROP TABLE IF exists dbo.Customer
go
CREATE TABLE dbo.Customer (
CustomerID int not null CONSTRAINT pk_Customer PRIMARY KEY, 
Title nvarchar(8) null, 
FirstName nvarchar(50) null, 
MiddleName nvarchar(50) null, 
LastName nvarchar(50) null,
[Name] nvarchar(160) null,
TerritoryID int null) 
go

INSERT dbo.Customer (CustomerID, Title, FirstName, MiddleName, LastName, [Name],TerritoryID)
SELECT c.CustomerID, Title, FirstName, MiddleName, LastName, 
FirstName + isnull(' ' + MiddleName,'') + isnull(' ' + LastName,'') as [Name],
c.TerritoryID
FROM AdventureWorks.Sales.Customer c
JOIN AdventureWorks.Person.Person p on p.BusinessEntityID = c.PersonID
go

-- SalesOrderHeader
DROP TABLE IF exists dbo.SalesOrderHeader
go
CREATE TABLE dbo.SalesOrderHeader(
SalesOrderID int NOT NULL identity CONSTRAINT pk_SalesOrderHeader PRIMARY KEY,
OrderDate datetime NOT NULL,
Status tinyint NOT NULL,
OnlineOrderFlag bit NOT NULL,
SalesOrderNumber char(200) NOT NULL,
CustomerID int NOT NULL,
SalesPersonID int NULL,
TerritoryID int NULL,
SubTotal money NOT NULL,
TaxAmt money NOT NULL,
Freight money NOT NULL,
TotalDue money NOT NULL,
Comment nvarchar(128) NULL)
go

-- Alimenta tabela com 31.465.000 linhas
INSERT dbo.SalesOrderHeader (OrderDate, [Status], OnlineOrderFlag, SalesOrderNumber, CustomerID, SalesPersonID, TerritoryID, SubTotal, TaxAmt, Freight, TotalDue, Comment)
SELECT OrderDate, Status, OnlineOrderFlag, 
SalesOrderNumber, CustomerID, SalesPersonID, TerritoryID,  
SubTotal, TaxAmt, Freight, TotalDue, Comment
FROM AdventureWorks.Sales.SalesOrderHeader
go 500

-- Cria índices
CREATE INDEX ix_SalesOrderHeader_CustomerID
ON dbo.SalesOrderHeader (CustomerID)
INCLUDE (OrderDate,TotalDue)

/****************************** Leva 3 min *********************************/

Agora Script comparando o desempenho dos 3 métodos.

set statistics io on

/*********************************************
 Tabela Temporária
**********************************************/
CREATE TABLE #Vendas (
Customer nvarchar(160),
Ano int,
Mes int,
Ranking int,
TotalVendas decimal(18, 2))

INSERT #Vendas 
SELECT b.Name as Customer, year(a.OrderDate) as Ano, month(a.OrderDate) as Mes,
ROW_NUMBER() OVER (PARTITION BY year(a.OrderDate), month(a.OrderDate) ORDER BY a.TotalDue DESC) as Ranking,
a.TotalDue as TotalVendas
FROM dbo.SalesOrderHeader a
JOIN dbo.Customer b ON b.CustomerID = a.CustomerID
JOIN dbo.SalesTerritory c ON c.TerritoryID = b.TerritoryID

SELECT Ano, Mes, Customer, TotalVendas
FROM #Vendas
WHERE Ranking = 1
--ORDER BY Ano, Mes

DROP TABLE IF exists #Vendas

/* 1 min 31 seg

Table 'SalesOrderHeader'. Scan count 5, logical reads 58945
Table 'Customer'. Scan count 5, logical reads 577
Table 'SalesTerritory'. Scan count 4, logical reads 1

Table '#Vendas_0000000D4D77'. Scan count 5, logical reads 119964

Total de I/O = 179.487 x 8 KB = 1.435.896 KB = 1.402 MB
*/


/************************************
 CTE
*************************************/
;WITH CTE_Venda as (
SELECT b.Name as Customer, year(a.OrderDate) as Ano, month(a.OrderDate) as Mes,
ROW_NUMBER() OVER (PARTITION BY year(a.OrderDate), month(a.OrderDate) ORDER BY a.TotalDue DESC) as Ranking,
a.TotalDue as TotalVendas
FROM dbo.SalesOrderHeader a
JOIN dbo.Customer b ON b.CustomerID = a.CustomerID
JOIN dbo.SalesTerritory c ON c.TerritoryID = b.TerritoryID)

SELECT Ano, Mes, Customer, TotalVendas
FROM CTE_Venda
WHERE Ranking = 1
ORDER BY Ano, Mes
/* 1 min 11 seg

Table 'SalesOrderHeader'. Scan count 5, logical reads 58937
Table 'Customer'. Scan count 5, logical reads 577
Table 'SalesTerritory'. Scan count 2, logical reads 1

Volume de I/O = 59.514 x 8 KB = 476.112 KB = 464 MB
*/

/************************************
 SubQuery
*************************************/
SELECT Ano, Mes, Customer, TotalVendas
FROM (SELECT b.Name as Customer, year(a.OrderDate) as Ano, month(a.OrderDate) as Mes,
ROW_NUMBER() OVER (PARTITION BY year(a.OrderDate), month(a.OrderDate) ORDER BY a.TotalDue DESC) as Ranking,
a.TotalDue as TotalVendas
FROM dbo.SalesOrderHeader a
JOIN dbo.Customer b ON b.CustomerID = a.CustomerID
JOIN dbo.SalesTerritory c ON c.TerritoryID = b.TerritoryID) a
WHERE Ranking = 1
ORDER BY Ano, Mes

🏁 Resultado dos testes:

🔎 Conclusão:

Apesar de CTEs e subqueries terem desempenho próximo, a CTE levou uma leve vantagem, oferecendo melhor legibilidade e manutenção do código, com menor volume de I/O.

💡 Dicas Práticas

• Use CTEs para segmentar etapas de uma consulta complexa.

• Prefira CTEs recursivas ao invés de cursores para navegar em hierarquias.

• Para consultas de grande volume, avalie o plano de execução e o custo de I/O entre CTEs, subqueries e tabelas temporárias.

Fui, mas volto com mais SQL Server em breve!

✍️ Sobre o autor

O Prof. Landry é especialista em Microsoft SQL Server desde 1999, Microsoft Trainer, Professor Universitário e criador do canal SQL Server Expert no YouTube, com conteúdo técnico semanal para DBAs e profissionais de dados.

🚀 Quer aprender mais sobre SQL Server?
👉 Me acompanhe no LinkedIn e inscreva-se no canal para não perder nenhuma dica prática!