Skip to main content
Tweeted twitter.com/#!/StackDBAs/status/541842700333756417
added 5 characters in body; edited tags; edited title
Source Link
Paul White
  • 90.3k
  • 30
  • 424
  • 663

SQL Server: use Reuse a bigcomplex non-deterministic expression in SELECT and WHERE clauses

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_pointsscore_points in the WHEREWHERE 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 fieldscolumns, I know about them but I can't use them here.

SQL Server: use a big expression in SELECT and WHERE

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 fields, I know about them but I can't use them here.

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

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.

Source Link
carlo.borreo
  • 1.5k
  • 6
  • 21
  • 36

SQL Server: use a big expression in SELECT and WHERE

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 fields, I know about them but I can't use them here.