Using functions in WHERE clauses can cause query performance problems. The query optimizer attempts to create a cardinality estimate. If the Optimizer does not know the value to look up – it will base the cardinality estimate on the average per distinct value.
It is more than likely using the average of every distinct value – will lead to a less than optimized plan.
There is a benefit for developers using functions. Quite often data may not be clean and a function may be used to deal with formatting or NULL .A developer may develop a function – to be used across multiple stored procedures.
The Optimizer doesn’t know the value in advance – and as you can see from the example below, a new value is passed in.
Designing queries to exploit the Statistics is critical for sql performance. There are many workarounds to using functions in the WHERE clause. Deciding on which workaround is defined by the function complexity.
An example workaround , would be to place the logic as an INNER JOIN . If the function was using a date lookup table – then creating an INNER JOIN to the date lookup table, would create the data set once , place in memory
An simple example of a function used in a WHERE clause and a workaround. Make sure you check the Execution before and after, to see the difference. Read this post to test properly - SQL Server - Query baseline testing - SQL Server DBA
Declare @period datetime SET @period = avalue Select t1.col1,t1.col2,t1.mydate FROM myTable t1 ON anotherTable T2 ON T2.main_id = t1.main_id WHERE MYDB.dbo.fn_(t1.usedDate) = @period --an example workaround Declare @period datetime SET @period = avalue Select t1.col1,t1.col2,t1.mydate FROM myTable t1 INNER JOIN anotherTable T2 ON T2.main_id = t1.main_id INNER JOIN dateLookUpTable dl ON dl.adate = t1.mydate