Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Multiple CTEs

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.


Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...