Reuse a complex non-deterministic expression in SELECT and WHERE clauses

Question

I have a query like this:

-- This is inefficient, and may even be wrong if expression is nondeterministic
SELECT TOP 10 team_name,score_points=<big expression>
  FROM teams
 WHERE <big expression> >= 100
 ORDER BY 2 DESC

-- This gives error
SELECT TOP 10 team_name,score_points=<big expression>
  FROM teams
 WHERE score_points >= 100
 ORDER BY 2 DESC

What I want to do is clear: list the teams with the highest score_points. List all teams with at least 100 score_points, but no more than 10 teams.

If I repeat the expression, it will be very ugly and inefficient. Plus, it will give wrong results when my expression is not deterministic. Using the name score_points in the WHERE clause gives an error. Is there an efficient way to have the results of my expression both returned as a column in my query, and used as a filter?

Please don't tell me about computed columns, I know about them but I can't use them here.

Answer 1

This can't help with the determinism issue, but CROSS APPLY is a good tool for big expressions that need to be reused:

SELECT TOP 10 team_name, CxA.Score_Points
FROM teams t
CROSS APPLY (SELECT Score_Points = <big Expression, refer to t>) CxA
 WHERE CxA.Score_Points >= 100
 ORDER BY CxA.Score_Points DESC

CROSS APPLY expressions get calculated for every row and can be referred to in any part of the query.

Answer 2

You can solve the determinism issue using a CTE or a derived table, as follows:

SELECT TOP 10 team_name, score_points
 FROM 
    (SELECT team_name, score_points=<big expression>
     FROM teams) T
 WHERE score_points >= 100
 ORDER BY score_points DESC

Should you still encounter performance problems, probably there could be something to revise in your calculation