Search code examples
sql-serverquery-optimization

How do I avoid writing a SQL Server query twice to avoid duplication?

I have an sql query that requires repeating the same SELECT statement a number of times within the overall query as input to other calculations.

How can I write the query so that I only define the repeated SELECT SQL statement once? Is there a method for doing this to avoid making mistakes when it is repeated through the query?

UPDATE: in this instance the data returned from the SELECT is expected to be < 100 rows in 80% of cases and max of 100-200 rows in the other 20% of cases

CTE and table variables both look like valid answers - but how do you know which one is the more appropriate option in any situation?

Solution

  • If you want to reuse the same SELECT as a subquery, multiple times in the same query, then you can use Common Table Expressions (CTE).