Is there is any Performance issue while using ISNULL() in SQL Server?

Yes it can. For optimizer is better rewrite the query (if possible) to form

(Field = @x OR @x IS NULL)

Because using functions in certain cases prevents from optimizer to use statistics and sometimes forced implicit datatype conversions


ISNULL() in the select-clause has neglible influence on the performance. In the where-clause on the other hand it can have a very huge impact on performance, since it prevents the optimizer for using an index on that column.

where isnull(col1, 0) = 0 -- unable to use index, because every 
                          -- row has to be evaluated

where col1 = isnull(@myVar, 0) -- index will be used, since isnull(@myVar, 0) 
                               -- returns the same static value for every row and 
                               -- not every row has to be evaluated by the function.

So, when using isnull() in a where-clause, evaluate if it prevents the query optimizer from using an index. If so, consider creating a computed column with the result if isnull(col1, 0) and index the computed column and use it in your where-clause.