When should I be using a CTE - Common Table Expression in SQL Server?

All topics about programming / development in T-SQL or other languages for SQL Server.
Post Reply
Allan
Posts: 7
Joined: Fri Jul 05, 2019 3:42 pm

Wed Jul 10, 2019 5:05 pm

When should I be using a CTE - Common Table Expression in SQL Server?
DaveP5
Posts: 29
Joined: Sat Jun 22, 2019 7:58 pm
Answers: 1

Wed Jul 10, 2019 5:07 pm

You should use a CTE if you need to reference/join the same data set multiple times you can do so by defining a CTE. Therefore, it can be a form of code re-use.


A CTE can be used to:

Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
Reference the resulting table multiple times in the same statement.
Post Reply

Social Media