Multiple CTEs
10October 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.
SQL Studies 
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!
Great information. I know it will come in useful regularly.
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.
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.
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. 🙂
[…] CTEs (Common Table Expressions) are one of the most interesting and useful tools added to T-SQL in the last decade. But even though they have been around for that decade and are widely used I still find that they confuse people somewhat. Among other things this confusion leads to difficulty understanding how to use them with queries other than SELECT or how to use them beyond a single CTE at a time. […]
[…] are cool things. You can essentially create one or more in-line view(s) within your query. One thing that isn’t overly well known is that you can […]