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.