We often use CTEs in SQL queries to simplify complex logic.
But there are a few mistakes that we should avoid when defining a CTE.
1)
When we define a CTE we write the WITH Clause followed by the CTE name and the CTE definition within round brackets.
But should you or should you not terminate the CTE definition with a semicolon ?
We are not supposed to use a semicolon after the CTE definition so that SQL considers all these
statements as a single batch and is able to use your CTE in the select query.
2)
There might be requirement in which we need to order the data by a particular
column in our data set.
When we define a CTE and add an order by column is that going to work?
Let's try to execute this query
We can see that there is an error message saying that the
Order by Clause is invalid in the use of a CTE
So when you define a CTE keep in mind that we do not use the order by Clause.
But there's an exception. We can use Order by Clause with a top statement
So if we want to sort by the department name and then want to select the top two rows then we can use an Order By in the CTE.
So the rule is that the order by can only be included in a CTE definition only when using a TOP command in the CTE definition.
3)
If we are defining multiple CTEs we just need to separate them by a comma.
We do not need to use the WITH Clause again for defining the second CTE and so on.
4)
In certain scenarios we need to use variables for defining a SQL query .
Here we have declared some variables as part of the CTE definition.
But on execution, this SQL code throws an error message.
In a CTE, the first statement should be a select query.
5)
After defining a CTE, you can perform a single Select, Insert, Update, Delete operation on the CTE.
But keep in mind, that all these operations can manipulate the data from the base table.
For example if you perform a Delete from CTE, SQL will go and delete the data from the base table as per the CTE definition.
Very well written
ReplyDelete