SQL SERVER – Common Gotcha’s Associated with Common Table Expressions (CTE) – Quiz – Puzzle – 26 of 31

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

Year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, has received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to the next level. Here is an article which discusses the Author’s Perspective.

Beyond Relational has come up with a very interesting concept – they have converted a few of the questions from my book into the SQL Quiz. The quiz is indeed focused on my two goals. In addition, it’s going to put the learning of the book to a higher stage. Looking at this novel concept, Vinod Kumar (Co-author) and I have decided to help every participant and reader by giving a few hints and suggestions to solve the quiz.

SQL Quiz

As part of performance tuning exercises, our developer always suggest to use SET based operations rather than procedural languages. One of the suggestions given as a substitute for hierarchical data structure was to use CTE. What are CTEs inside SQL Server, what are their advantages and how can you rewrite a subquery using CTEs?

Link to participate in SQL Quiz

Notes of Vinod Kumar

Common Table Expressions a.k.a CTEs are an important addition to SQL Server. Though CTE is available as an alternative for Views, Subqueries, there is a special case scenario for using CTEs. Tell us, how innovatively have you used CTEs? Can there be recursive CTEs? If yes, have you ever tried them?

Notes of Pinal Dave

CTEs are often confused with Temp Table and Subqueries, but there are more facts about this subject. While working with recursive CTEs, one has to make sure that they do not go into an infinite loop and stop after certain numbers of fixed iteration. SQL Server does have an option to limit the numbers of iteration; however, there is a default number which is associated with number of loops. CTEs are also very useful in terms of readability. I’ve started to use CTEs in many places, but before I began to practice CTEs, I made sure that I’d spend a good amount of time to learn about them, and so I did.

Additional Notes

SQL Server Interview Questions and Answers ISBN: 1466405643 Page#109-112
Common Table Expression (CTE) and Few Observation
Multiple CTE in One SELECT Statement Query
Delete Duplicate Rows
Simple Example of Recursive CTE
SQL SERVER – Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop
T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table


There are exciting prizes awaiting the winners. Click here for Prizes and Frequently Asked Questions.

Link to participate in SQL Quiz

Note: SQL Quiz, winners and prizes are administrated by Beyond Relational. The goal of this blog post is to provide additional learning pointers only.

Reference: Pinal Dave (http://blog.sqlauthority.com)

4 thoughts on “SQL SERVER – Common Gotcha’s Associated with Common Table Expressions (CTE) – Quiz – Puzzle – 26 of 31

  1. Pingback: SQL SERVER – Weekly Series – Memory Lane – #014 « SQL Server Journey with SQL Authority

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