I have a sequence of 20 stored procedures executed to produce the final output. In almost all procedures, i have a table lookup. The query is something similar to shown below:
ISNULL(col1, @col1) = @col1 AND
ISNULL(col2, @col2) = @col2 AND
ISNULL(col3, @col3) = @col3 AND
ISNULL(col4, @col4) = @col4 AND
@col5 BETWEEN ISNULL(col5, @col5) AND ISNULL(col5, @col5) AND
@coldate BETWEEN ISNULL(effDate, @coldate) AND ISNULL(expDate, @coldate)
ORDER BY colrk DESC
The idea here is, tables have a default value will null in the column. if the given values does not match, it has to return the null valued record.
Is this query optimized? Does using ISNULL() function affects the performance.
We have non clustered index on the table, including all columns in where clause.