Common Table Expressions
Common Table Expressions are a feature of the SQL language that has grown on me over time. They are mandatory for some forms of query recursion, at least in SQL Server. However, I don’t have a lot of need for recursive queries.
I find I use them most often for simplifying complicated queries. If you ever do a showplan on a complex SQL statement you will notice that it breaks things down into steps, starting with one table, integrating results from other tables, etc. There comes a point where you sometimes have more information about the distribution of the data and are able to optimize the query more than what the SQL Server query optimizer will do on its own. I have to admit, it is getting harder and harder to beat the query optimizer for performance.
One technique I will often do is break down work into steps, especially if there is aggregation being performed on the final results. For example, I might aggregate everything grouping on unique record ids, and then join the subject tables on those ids to get the descriptive data, so I don’t have to group all the descriptive data while aggregating.
In the past I would have used temporary tables for the aggregation step. Today I first try one or more Common Table Expressions and then join the results to the subject tables for final results. Often this is more efficient than using temp tables altogether for a number of reasons.
Are you using Common Table Expressions? If not, they are really worth your time to learn. What’s your favorite SQL technique? Share it with us by sending an email to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
SSWUGtv
With Stephen Wynkoop
In this episode watch this informative interview with Devin Knight – SQL Server 2012 BI Guru. Steve and Devin talk about the concept of Self-Service Business Intelligence – including the Microsoft approach to self help in the form of a new product, PowerView.
Watch the Show
Featured White Paper(s)
SharePoint 2010 Enables the Enterprise
Written by KnowledgeLake
read more)
Featured Script
Convert alphanumeric string to numeric only result
This user function will convert an alphanumeric input string and convert it to an integer equivalent value to allow for bette… (read more)