THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences the Microsoft Data Platform

When To Break Down Complex Queries


Some days ago my SolidQ colleague Luca  pointed me to the followin SQLCAT article

When To Break Down Complex Queries

that, as part of the reorganization of SQL CAt website, has emerged again from the depth of Internet.

The article is very interesting and also allows me to underline again the importance of having a way to tell the optimizer when it should *not* expand subqueries into the main query. In this way the usage of #temp tables could be avoided in many cases, allowing for the usage of views or inline UDF instead of stored procedures.

I opened the connect item back in 2010, so it’s time to bring more attention to it in order to have it in the product ASAP, and this article give me the option to do that.

Please vote for it:

Now that data volumes are increasing every day, more and more I find myself fighting with bad query plans due to bad cardinality estimation. When you are joining several big tables, you can be sure you’ll have to break your query in several pieces, to be sure to have good performance. Last time I had to do this was….the day before yesterday!

I think it’s really time for an extended NOEXPAND hint. If you’re with me, vote vote and vote!

Published Thursday, September 19, 2013 5:13 PM by Davide Mauri

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



jchang said:

Our guiding principles should be cause and effect, not rules to be blindly followed. For example there is no fundamental reason to avoid temp tables over CTE. That said, I do like the cleaner style of CTE over that of CREATE, insert, use, DROP temp table. So back to cause and effect, our objective is to balance the overhead of creating a temp table versus the implicit expansion with a CTE.

There are two reasons for having a temp table. One is if the CTE expression is used repeatedly, both requiring repeated evaluation and also the extra cost of query optimization. The other is to get a good execution plan. If the row estimate of the CTE could is wrong, and this could happen for perfectly valid reasons, such that there are serious consequences (not necessarily just the magnitude of the mis-estimate) the execution plan could also be horribly wrong. So here the true value of having a temp table is the opportunity to get true statistics, if more than 6 rows.

As I said earlier, I do like the clean syntax of the CTE, so if by no expand, you mean implicit temp table, that would be useful for this reasons you stated.

On the matter of the SQL CAT article of when to breakdown complex queries, there is an ISV application called Relativity used in the legal industry for document discovery that just seems to gravitate to expressions that the SQL Server query optimizer cannot handle. I talk about here

September 19, 2013 1:17 PM

abx said:


May 31, 2018 11:01 PM

dongdong8 said:



June 29, 2018 3:27 AM

qqq said:

August 16, 2018 12:19 AM

lpasteyc said:

I’ve always slept with such and enjoyed high-quality emotions with them. Visit our website for know more.

August 18, 2018 8:23 PM

chenjinyan said:



August 22, 2018 11:26 PM

blizqnsl said:

You have been dwelling with the women of Chandigarh  offer into unforgettable due to the fact that is really a pleasant and remarkable experience.

September 3, 2018 3:44 AM

kakakaoo said:

October 8, 2018 2:24 AM

kakakaoo said:


November 8, 2018 2:07 AM

Leave a Comment


About Davide Mauri

Director of Software Development & Cloud Infrastructure @ Sensoria, an innovative smart garments and wearable company. After more than 15 year playing with the Microsoft Data Platform, with a specific focus on High Performance databases, Business Intelligence, Data Science and Data Architectures, he's now applying all his skills to IoT, defining architectures to crunch numbers, create nice user experiences and provide meaningful insights, all leveraging Microsoft Azure cloud. MVP on Data Platform since 2006 he has a very strong background development and love both the ER model and OO principles. He is also a fan of Agile Methodology and Automation, which he tries to apply everywhere he can, to make sure that "people think, machines do".

This Blog


Privacy Statement