SQL Server Performance Forum – Threads Archive

CharIndex and BETWEEN Function

Is there any performance issuee, if Charindex/Between function used in the WHERE condition. If any one knows, which one better. Thanks & Regards

---

As far as I know, both functions are porviding different functionalities. So How can we say whcih one is better??
Can you tell us your requirment

---

It depends on the number of records in the table and the type of index used
Madhivanan Failing to plan is Planning to fail

---

Hi if im not wrong we use between operator to specify search criteria in where clause so that it will fetch those records which will fall in that range.
How can this operator be replace with charindex function as the function returns integer value.

---

Please be more specific in your question.
Without knowing what you’re really want it’s hard to suggest something useful. —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

---

Also, normally a BETWEEN will be used as a range between column names so no function is actually being performed on the columns. If you use CHARINDEX (again I’m at a loss as to how you get the same functionality as BETWEEN using this) CHARINDEX has to operate on something, meaning you will almost certainly operate on 1 if not 2 columns. When you perform a function of any type on a column, that column can no longer be used in an index, and if this was your only WHERE clause you’d be guaranteeing a table scan every time. So, in a general, non-committal kind of way, I’d say for sure 100% maybe CHARINDEX is going to definitely maybe be slower. David Hilditch Small Businesses
www.matiogi.com

---

]]>