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.