#sql server interview question

If you are new to CTE please see (What is CTE ) SQL Server interview question first. CTE can be used only once in the same execution. You can see the below code snippet where we have created a simple CTE called as “MyTemp”. In the same execution I have tried to use it 2 times and you can see how did not identify the “MyTemp” in the second select execution.  

                      Some of the interviewer’s question further asking about in what scenarios we can use CTE.Below are some points.

  CTE can be used in the following scenarios:-  

• Complex SQL queries can be broken down using CTE which will make your      code more readable. Note it does have side effect of performance. • Recursive query.

• Replacement for views if you do not want to store the metadata.

• You want use aggregate functions in WHERE clause.

• You can group by scalar values which are derived from a result set.  

You can also read our 10 SQL Server interview questions from here.

You can also see video on SQL Server Interview questions CTE (Common table expression):-