Persistent function in SQL Server (slow view using function)

Question

I have encoded a representation that relies on the value of a scalar function for one if its join conditions. The query plan conservatively expects that the result of a function may change between execution when the function is constant (at least for each complete request). The result changes once a day.

Due to the large number of rows, the query plan is inefficient. I need the SQL server to know that the value will not change. Since you cannot declare a variable in the view, I am not sure how to solve the problem. Perhaps a query hint or other design will work?

We tried to code a function with a table evaluation, which did not seem to help much. This is not an ideal solution, as I would prefer to use the normal view.

Thanks Regards Craig.

Answer 1

Perhaps you should consider placing a constant in a single-column table, which is regularly updated using a scheduled stored procedure . If you have a primary key defined in a table column, then the query plan should work fine.