Query Performance

Using ISNULL (or any function) around the field value in the WHERE clause will force a table or index scan and will have to scan all records. If you are expecting this table to never contain more than a few records, (or if you expect a table scan for other reasons) then don't worry. Otherwise you need to find a different way of expressing the query.

The %% you have in your example, is this indicating that you intend to use a pattern along the lines of %p% as your search critiera? If so then this will also always cause a complete table or index scan and should be avoided.

Why do you think you need the ISNULL? Do you really want NULL or Blank names to be returned?

The WHERE condition which is written as you have it in the question will return all rows so you do not need it at all.

If you are using different patterns in the LIKE clause then I would try to remove the ISNULL from the WHERE to make the condition sargable.

WHERE Resident_First_Name LIKE 'pattern' OR ResidentFirstName IS NULL if you want also return fields with NULL values
WHERE Resident_First_Name LIKE 'pattern' in case you do not want the NULLs to be returned.

Aslso as @pipthegeek mentioned, try to avoid % wildcard on the beginning of the LIKE pattern if possible as it will utilize an INDEX or TABLE SCAN. When the % is used only at the end of the pattern then you will probably receive a better query plan as probably INDEX seek will be done if there is an index on the Resident_First_Name field.

Anyway if you do not have an index on that field, a table scan (or eventual other covering index scan) will be always done by such query.

I would like to give your question answer that Using ISNULL in where clause will decrease the SQL query performance because If you apply a function to a column used in the WHERE clause of your SQL statement, it is unlikely that the SQL statement will be able to make use of any indexes applied to that column so do not apply any function to a column used in the WHERE clause.

If you can post your expected requirement of SQL query result with example of table data then I could help you to make SQL with more efficiency way.

Like both @pipthegeek and @Pavel Pawlowski pointed out - your query will return all rows because LIKE '%%' isn't excluding anything but NULL, and ISNULL takes care of those.

But apart from that: It can't be said if your query is efficient or not.

ISNULL(col,'value') LIKE '%pattern%' 

can be reconstructed as:

col LIKE '%pattern%' OR (col IS NULL AND 'value' LIKE '%pattern%')

That's more likely to use an index on column col

LIKE '%pattern%'

is inefficient, but if sometimes that's the search criteria you want.

So your answer is: It depends on your data and your requirements. My only general advice is that you probably want to reconstruct ISNULL.