Geek Answers Handbook

Filter SQL query based on scalar function result

In SQL Server, I cannot use an alias column name in a statement WHERE

This does not work

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

I need to do

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

I am worried that SQL is executing [dbo].[myFunc] (X.Prop1)twice.

My questions:

  • Is it running twice or is SQL Server smart enough?
  • Is there a way to use an alias column in a sentence WHERE?
+4
source share
3 answers

When I try in both directions, they have the same execution plan, so it seems that the query optimizer is smart enough to know that they are the same request and run it once ... So, both ways in okay i guess ...

, - :

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

, , , CROSS APPLY:

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

fiddle, , 10.

+2

You can use Common Table Expression (CTE)

;WITH cteMyData AS
(
   SELECT *, [dbo].[myFunc] (X.Prop1) AS Width 
   FROM X
)
SELECT * FROM cteMyData WHERE Width > 0
+2
source

More articles:


All Articles