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.