Transformando colunas em linhas sem UNPIVOT

Olá pessoas,

Após conhecer um pouco do UNPIVOT, percebe-se que este comando não é muito flexível, mesmo sendo um pouco mais maleável que o comando PIVOT. Por desconhecer este comando, ou pela falta de flexibilidade, é comum encontrar outras alternativas para ‘transformar colunas em linhas’, como o exemplo abaixo:

-- Criando uma tabela de teste:

DECLARE @CONTAS TABLE (
    [BANCO] VARCHAR(100),
    [ANO] SMALLINT,
    [INVESTIMENTOS] MONEY,
    [DESPESAS] MONEY
)

INSERT INTO @CONTAS VALUES
('BANCO ALVORADA S/A', 2010, 9613906084.01, 8102644.84),
('BANCO ALVORADA S/A', 2011, 174343.35, 7935411.15),
('BANCO ARBI S/A', 2010, 8202652.29, 114215.13),
('BANCO ARBI S/A', 2011, 8407843.72, 81746.25)

-- Uso do UNION ALL para transformar colunas em linhas:

SELECT [BANCO], [ANO], 'INVESTIMENTOS' AS [TIPO], C.[INVESTIMENTOS] AS [VALOR] FROM @CONTAS C
UNION ALL
SELECT [BANCO], [ANO], 'DESPESAS' AS [TIPO], C.[DESPESAS] AS [VALOR] FROM @CONTAS C
ORDER BY [BANCO], [ANO], [TIPO]

Esta estratégia com UNION ALL requer uma leitura da tabela a mais para cada coluna transformada em linha, ou seja, a tabela no exemplo acima foi lida duas vezes para obter o mesmo resultado que poderia ser obtido lendo a tabela uma única vez com UNPIVOT:

SELECT [BANCO], [ANO], [TIPO], [VALOR]
FROM @CONTAS C
UNPIVOT (
    [VALOR] FOR [TIPO] IN (
        [INVESTIMENTOS],
        [DESPESAS]
    )
) AS U
ORDER BY [BANCO], [ANO], [TIPO]

Com o STATISTICS IO, podemos obter as seguintes informações:

Consulta com UNION ALL:

(8 row(s) affected)
Table ‘#A255182D’. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Consulta com UNPIVOT:

(8 row(s) affected)
Table ‘#A255182D’. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Mas como obter flexibilidade, tendo um número de leituras semelhante ao UNPIVOT, e até mesmo ter um plano de execução um pouco melhor?

A resposta pode ser encontrada no CROSS APPLY – que para quem não conhece, este comando funciona de forma um pouco semelhante ao JOIN, executando uma determinada subquery para cada registro da tabela com a qual ele faz o ‘JOIN’ –, que pode ser utilizado com UNION ALL para transformar colunas em linhas:

SELECT [BANCO], [ANO], [TIPO], [VALOR]
FROM @CONTAS C

CROSS APPLY (
    SELECT 'INVESTIMENTOS' AS [TIPO], C.[INVESTIMENTOS] AS [VALOR]
    UNION ALL
    SELECT 'DESPESAS' AS [TIPO], C.[DESPESAS] AS [VALOR]
)
AS U

ORDER BY [BANCO], [ANO], [TIPO]

Ou combinado ao VALUES:

SELECT [BANCO], [ANO], [TIPO], [VALOR]
FROM @CONTAS C

CROSS APPLY (
    VALUES
    ('INVESTIMENTOS', C.[INVESTIMENTOS]),
    ('DESPESAS', C.[DESPESAS])
)
AS U ([TIPO], [VALOR])

ORDER BY [BANCO], [ANO], [TIPO]

Em breve, vou publicar um comparativo mais detalhado entre o UNPIVOT e o CROSS APPLY!

Consultas diferentes, o mesmo plano de execução

E ai pessoas!

Neste final de semana depois de ler alguns artigos perdidos pela internet, como o sobre “SQL SERVER – Convert IN to EXISTS – Performance Talk” e “SQL SERVER – Subquery or Join – Various Options – SQL Server Engine knows the Best” do Pinal Dave, fiquei com vontade de criar um plano de execução diferente entre IN e EXISTS, mas realmente é impossível (pelos meios normais)…

Com sono, até vi um plano de execução diferente entre os dois, mas era o frio e o sono me fazendo escrever consultas erradas… #FAIL

Para demonstrar isso segue alguns exemplos de consultas diferentes que retornam o mesmo resultado e possuem o mesmo plano de execução (que surgiram no blog do Pinal Dave e algumas adaptações):

-- use of = any
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID = ANY (SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA)
GO
-- use of = some
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID = SOME (SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA)
GO
-- use of in
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID IN (SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA)
GO
-- use of exists
SELECT *
FROM HumanResources.Employee E
WHERE EXISTS (SELECT *
FROM HumanResources.EmployeeAddress EA
WHERE EA.EmployeeID = E.EmployeeID)
GO
-- use of cross apply
SELECT *
FROM HumanResources.Employee E
CROSS APPLY (SELECT *
FROM HumanResources.EmployeeAddress EA
WHERE EA.EmployeeID = E.EmployeeID) EA
GO
-- use of join
SELECT *
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeAddress EA
ON E.EmployeeID = EA.EmployeeID
GO
-- use of join with subquery
SELECT *
FROM HumanResources.Employee E
INNER JOIN (SELECT *
FROM HumanResources.EmployeeAddress EA) EA
ON EA.EmployeeID = E.EmployeeID
GO

E o plano de execução:

Lembrando que há uma pequena diferença entre os quatro primeiros planos de execução e os três últimos, visto que o algoritmo de MERGE JOIN executado pelo primeiro grupo é um “LEFT SEMI JOIN” e o do segundo grupo é um “INNER JOIN”, assim o segundo grupo somente é valido para relacionamentos 1:0..1 (a não ser que você deseje que apareça dados da tabela “filha” no resultado, podendo fazer os dados da tabela “mãe” se repetirem para cada resultado encontrado na “filha”) e o primeiro grupo é valido tanto para relacionamentos 1:0..1 e 1:0..N.

As diferenças aparecem quando mudamos um pouco as consultas:

-- segundo grupo (INNER JOIN)
SELECT COUNT(*)
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeAddress EA
ON E.EmployeeID = EA.EmployeeID
GO
-- primero grupo (LEFT SEMI JOIN)
SELECT COUNT(*)
FROM HumanResources.Employee E
WHERE E.EmployeeID IN (SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA)
GO

E o plano de execução fica bem diferente, visto o INNER JOIN será semelhante a um COUNT(*) direto na tabela “filha” por causa de influência da foreign key:

Ou quando utilizamos NOT EXISTS/IN ou LEFT JOIN, podendo criar um plano de execução um pouco diferente, mas com o mesmo custo de execução:

-- segundo grupo (LEFT OUTER JOIN + FILTER)
SELECT *
FROM HumanResources.Employee E
LEFT JOIN HumanResources.EmployeeAddress EA
ON E.EmployeeID = EA.EmployeeID
WHERE EA.EmployeeID IS NULL
GO
-- primero grupo (LEFT ANTI SEMI JOIN)
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID NOT IN (SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA)
GO

Espero que tenham gostado, por enquanto eu fico por aqui (neste frio de 3ºC de Curitiba-PR), até mais!!!