T-SQL Column alias on computed column - Invalid column name

Question

I'm using an alias to refer to a computed column. Here is a snippet from the actual code I'm trying to make work, to compute similarity and return matches where the similarity score is 3 or higher.

select [FirstName], difference([FirstName], 'mitch') as similarity from [Dev].[dbo].[Name] where similarity > 2 order by similarity desc 

Exception Message:

Invalid column name 'similarity'.

As similarity is not a real column, how would I make this work?

Answer 1

Column aliases and computations are performed in the projection (SELECT) phase of the query, which occurs after the selection (WHERE and JOIN) phase. Because of this, they can't be referenced in the WHERE clause or in a JOIN condition because they do not yet exist. You can either use your query with the SELECT clause as a subquery or you can duplicate the computation in the WHERE clause:

select *   from (select [FirstName], difference([FirstName], 'mitch') as similarity from [Dev].[dbo].[Name]) src  where similarity > 2 order by similarity desc 

or

select [FirstName], difference([FirstName], 'mitch') as similarity from [Dev].[dbo].[Name] where difference([FirstName], 'mitch') > 2 order by similarity desc 

Answer 2

All answers can solve your problem but for complicated situation you just can't duplicate your query.

The correct way is by using CROSS and APPLY

select [FirstName], similarity from [Dev].[dbo].[Name] cross apply   (      select similarity =      difference([FirstName], 'mitch')     )computed_column where similarity > 2 order by similarity desc 

whit CROSS and APPLY you can use your computed column everywhere on the query