Multiple CTEs

It’s somewhat obscure in BOL and I wasn’t able to find any examples but it turns out you can use multiple CTEs in a single query.

I couldn’t come up with a good example on my own, but while researching a memtoleave problem I found a good candidate. Jonathan Kehayias mentioned a query by Christian Bolton. Link to Jonathan.  Link to Christian.  Both quite good reading if you are trying understand how SQL uses memory.

Here is the original query.

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

Here is the version using multiple 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

Put a comma after the close parenthesis of the first CTE, then the name for the next one. There is no additional WITH clause.

WITH CTEName1(field1, field2) AS (query),
CTEName2(field1, field2, field3) AS (query)

There may be a limit to the number of CTEs you can put in a query but I haven’t found it yet.