Filter SQL query based on scalar function result

Question

In SQL Server, I can't seem to use an aliased column name in the WHERE clause

This doesn't work

SELECT *, [dbo].[myFunc] (X.Prop1) AS Width 
FROM X 
WHERE Width > 0

I have to do

SELECT *, [dbo].[myFunc] (X.Prop1) AS Width 
FROM X 
WHERE [dbo].[myFunc] (X.Prop1) > 0

My concern is that SQL is executing [dbo].[myFunc] (X.Prop1) twice.

My questions are:

• Is it executing twice or is SQL Server smart enough?
• Is there a way to use an aliased column in the WHERE clause?

Answer 1

When I try both ways, they have the exact same execution plan, so looks like the query optimizer is smart enough to find out that they are the same query and run it once... So both ways are OK I assume...

But if want to use the subquery approach and use the alias you can use a something like this:

Select * from (
SELECT *, [dbo].[myFunc] (X.Prop1) AS Width FROM X 
) T
WHERE Width > 0

Answer 2

I am not sure if it is calculated twice or not, but you can avoid this using CROSS APPLY:

SELECT *, Width  
FROM X 
CROSS APPLY (Select [dbo].[myFunc] (X.Prop1)) N(Width)
WHERE Width > 0

Look this fiddle that selects the square of values that are greater than 10.