Published on

September 22, 2012

Explorando Múltiplas Expressões de Tabela Comum (CTEs) no SQL Server

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.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.