Multiple CTEs

8

October 9, 2012 by Kenneth Fisher

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.

About these ads

8 thoughts on “Multiple CTEs

  1. Regena says:

    Very nice post. I just stumbled upon your blog and wanted to say that I have truly enjoyed browsing your blog posts. After all I will be subscribing to your rss feed and I hope you write again very soon!

  2. Robert Willsie says:

    Great information. I know it will come in useful regularly.

  3. Robert Young says:

    The syntax is pretty much the same, so you can check DB2/LUW sites for examples of multiple CTE. Birchall’s “DB2 Cookbook” — http://mysite.verizon.net/Graeme_Birchall/cookbook/DB2V97CK.PDF on page 300 ff has examples of multiple.

    • Actually we have DB2 in our shop and that is where I saw multiple CTEs used the first time. One of my co-workers wrote a query with a stack of 10 CTEs. I hadn’t ever seen it done in SQL Server before so after some testing to make sure the syntax worked there as well I thought I would share.

      • Robert Young says:

        Now, if we can just get you to migrate to DB2/LUW on linux, all will be well! :)

      • My team actually does support DB2/LUW on linux, along with DB2/zOS, SQL Server (of course), Sybase and although we don’t like to admit it Access (unofficially). My team members have learned though, that if they need SQL help I’m their man, anything else and they may be better off asking the tree out front. They have a better chance of getting a useful answer. Fortunately about 80% of the work we do is SQL Server with the percentage moving more that way over time. Not that I have anything against DB2, it’s just not in my skill set currently.

  4. Richard says:

    Did you look at the syntax and example J on the “WITH common_table_expression” topic [1] in BOL?

    The BNF syntax “WITH [ ,…n ]” tells you that you can have multiple “” elements separated by commas.

    Example J [2] shows two CTEs in the same query.

    [1] http://msdn.microsoft.com/en-us/library/ms175972.aspx
    [2] http://msdn.microsoft.com/en-us/library/ms175972.aspx#bkmkUsingAnalyticalFunctionsInARecursiveCTE

    • I appreciate you pointing out the example since I hadn’t seen it before. Unfortunately the version of BOL I was looking at was 2008 R2. Example J is different and doesn’t contain multiple CTEs. Actually at first glance it’s the same as example I in the 2012 BOL. I do note though that even though they are using multiple CTEs it isn’t actually the point of the example.

      I did read the BNF and that’s why I put that it’s there but somewhat obscure. Of course that’s obscure for me. I can read a BNF but don’t consider [,..n] to be all that obvious. Clear yes, obvious no. :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 527 other followers

%d bloggers like this: