As Expressões de Tabela Comum (CTEs) são um recurso poderoso no SQL Server que permite definir conjuntos de resultados temporários que podem ser referenciados várias vezes dentro de uma consulta. Embora seja bem conhecido que você pode usar uma única CTE em uma consulta, é menos conhecido que você pode realmente usar várias CTEs em uma única consulta.
Vamos dar uma olhada em um exemplo para entender como várias CTEs podem ser usadas de forma eficaz. Considere um cenário em que precisamos calcular a memória total disponível e o tamanho máximo livre em kilobytes (KB) a partir de um despejo de endereço virtual na exibição do sistema sys.dm_os_virtual_address_dump.
Aqui está a consulta original usando uma única CTE:
WITH VASummary(Size, Reserved, Free) AS ( SELECT Size = VaDump.Size, Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 0 ELSE 1 END), Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 1 ELSE 0 END) FROM ( SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) AS Size, region_allocation_base_address AS Base FROM sys.dm_os_virtual_address_dump WHERE region_allocation_base_address <> 0x0 GROUP BY region_allocation_base_address UNION SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address FROM sys.dm_os_virtual_address_dump WHERE region_allocation_base_address = 0x0 ) AS VaDump GROUP BY Size ) SELECT SUM(CONVERT(BIGINT, Size) * Free) / 1024 AS [Total avail mem, KB], CAST(MAX(Size) AS BIGINT) / 1024 AS [Max free size, KB] FROM VASummary WHERE Free <> 0
Agora, vamos reescrever a consulta usando várias CTEs:
WITH VADump(Size, Base) AS ( SELECT CONVERT(VARBINARY, SUM(region_size_in_bytes)) AS Size, region_allocation_base_address AS Base FROM sys.dm_os_virtual_address_dump WHERE region_allocation_base_address <> 0x0 GROUP BY region_allocation_base_address UNION SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address FROM sys.dm_os_virtual_address_dump WHERE region_allocation_base_address = 0x0 ), VASummary(Size, Reserved, Free) AS ( SELECT Size = VaDump.Size, Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 0 ELSE 1 END), Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 1 ELSE 0 END) FROM VADump GROUP BY Size ) SELECT SUM(CONVERT(BIGINT, Size) * Free) / 1024 AS [Total avail mem, KB], CAST(MAX(Size) AS BIGINT) / 1024 AS [Max free size, KB] FROM VASummary WHERE Free <> 0
Na consulta reescrita, dividimos a CTE original em duas CTEs separadas – VADump e VASummary. A CTE VADump calcula as colunas Size e Base a partir da exibição sys.dm_os_virtual_address_dump, enquanto a CTE VASummary realiza a agregação e calcula as colunas Size, Reserved e Free com base nos resultados da CTE VADump.
Ao usar várias CTEs, podemos dividir consultas complexas em partes menores e mais gerenciáveis, tornando o código mais fácil de ler e entender. Também nos permite reutilizar os conjuntos de resultados intermediários em vários locais dentro da consulta, melhorando o desempenho e reduzindo a redundância.
Vale ressaltar que pode haver um limite para o número de CTEs que você pode usar em uma única consulta, mas esse limite não foi documentado explicitamente. Na maioria dos casos, o uso de algumas CTEs deve ser suficiente para a maioria dos cenários.
Da próxima vez que você encontrar uma consulta complexa que requer vários conjuntos de resultados temporários, considere usar várias CTEs para simplificar e otimizar seu código.