Hi The below query as i am using , it fast or slow for performace , The "Resident_First_Name" column have allow null column
select Resident_First_Name from RC_RESIDENT where ISNULL(Resident_First_Name,'') like '%%'
in where clause isnull() is reduce any 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?
But apart from that: It can't be said if your query is efficient or not.
can be reconstructed as:
That's more likely to use an index on column col
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.
answered Dec 29 '11 at 01:28 AM
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.
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.
answered Dec 29 '11 at 12:53 AM
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.
answered Dec 29 '11 at 01:12 AM