Best Way to Tune a Predicate, Column = dbo.ScalarValueFunction()

kamingbb 2016-03-23 04:35:39

Recently, I have to tune a SQL something like the below (a simplified version)

SELECT colA, colB
WHERE ColB < dbo.ScalarValueFunction(10) There is a non clustered index on ColB, but SQL Server full table scan the TableA because of the non clustered index did not cover the ColA. The only way I can think to solve this without create a new index or modify the existing index is to assign the return value of ScalarValueFunction() to a variable, then rewrite the WHERE clause to ColB < @variable and add the OPTIMIZE FOR UNKNOWN query hint. But when I get the estimate plan, SQL server still full table scan the TableA! Any other bright idea? Thanks in advance. Thanks

SQLkiwi 2016-03-23 11:24:33
If you want to force a seek using the nonclustered index, use a FORCESEEK hint, or an INDEX hint. OPTION (FAST 1) would also likely work, as would OPTION (OPTIMIZE FOR (@variable = some_unlikely_value)).
kamingbb 2016-03-24 02:15:22
Thank you Paul 🙂