Where is the item in the column that is the result of UDF

Question

I have a custom function (e.g. myUDF(a,b) ) that returns an integer.

I am trying to ensure that this function is called only once, and its results can be used as a condition in the WHERE :

 SELECT col1, col2, col3, myUDF(col1,col2) AS X From myTable WHERE x>0 

SQL Server tries to detect column x as a column, but it is indeed an alias for the computed value.

How can you rewrite this query so that filtering is performed on a computed value without having to execute UDF more than once?

Answer 1

If you are using SQL Server 2005 and later, you can use Cross Apply:

 Select T.col1, T.col2, FuncResult.X From Table As T Cross Apply ( Select myUdf(T.col1, T.col2) As X ) As FuncResult Where FuncResult.X > 0 

Answer 2

 With Tbl AS (SELECT col1, col2, col3, myUDF(col1,col2) AS X From table myTable ) SELECT * FROM Tbl WHERE X > 0 

Answer 3

to try

 SELECT col1, col2, col3, dbo.myUDF(col1,col2) AS X From myTable WHERE dbo.myUDF(col1,col2) >0 

but keep in mind that this will cause a scan since it is not SARGable

Here is another way

 select * from( SELECT col1, col2, col3, dbo.myUDF(col1,col2) AS X From myTable ) as y WHERE x>0 

Answer 4

SQL Server does not allow column references by alias. You must either write the column twice:

 SELECT col1, col2, col3, myUDF(col1,col2) AS X From table myTable WHERE myUDF(col1,col2) > 0 

Or use a subquery:

 SELECT * FROM ( SELECT col1, col2, col3, myUDF(col1,col2) AS X From table myTable ) as subq WHERE x > 0 

Answer 5

Depending on udf and how useful or often used it is, you can add it to the table as a computed column . Then you can filter the column as usual and should not write this function at all in queries.

Answer 6

I'm not 100% sure what you are doing, but since x is not a column, I would remove it from your SQL query, so you have:

 SELECT col1, col2, col3, myUDF(col1,col2) AS X From myTable 

And then add the condition to your code so that you only name it when x > 0

Answer 7

The best answer to your question is "C" (CTE, I think, in MSSS).

Actually the best question is: should I store this calculated value or recalculate it for each row, every time I query the table.

Are there 10 rows in the table and always 10 rows?

Are strings constantly added?

Do you have a cleaning strategy in place or just let it grow?

Request this table only once a month?

If this is a "long time" function (even after you have optimized it), why do you want to execute it more than once?

You asked once, but you really ask once per line, per request.

Storing a response in an index or "virtual column"

Pros:

Compute exactly once per line. Request time does not grow linearly.

Cons: Increases insert / update time

Calculation every time

Pros:

Insert / Update Time Optimized

Cons: Request time increases with row counting. (not scalable)

If you ask once a month why you care about how poor the performance is, set up something that actually has a big impact on your operations (very little).

If you do not insert a bunch of (depending on your hardware) rows per second, does it spend time on the front to make a big difference?