Constant Function in SQL Server (slow view that uses a function)

Question

Hi,

I have coded a view that relies on a scalar function value for one if its join conditions. The query plan is conservatively expecting that the function result could change between executions when the function is constant (at least for each complete query). The result changes once a day or so.

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

We tried coding a table valued function which did not seem to help significantly. It's not an ideal solution as I would rather use a normal view.

Thanks Regards Craig.

Answer 1

Maybe you should consider placing the "constant" into a single-column table, which gets updated on a regular basis using a scheduled stored procedure. If you have a primary key defined on the table column, then the query plan should work out just fine.