Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
staylor
1 Post |
 Posted - 2005-12-11 : 17:10:46
|
| I have to perform a search to look for telephone numbers that are either:- Assigned to a specific area code (e.g. 1-206-xxx-xxxx)- Assigned to a specific area/exchange code (e.g. 1-206-555-xxxx)- Assigned to a specific portion of a number (e.g. 1-206-555-1xxx)With each of these requirements, I can see that this could be accomplished with both a LIKE statement and a BETWEEN statement.My question is, does anybody have any suggestions as to which is the more efficient approach?I understand that BETWEEN would allow me more felixibility than LIKE (e.g I could search for numbers between 1-206-555-1990 and 1-206-555-2010) using BETWEEN but not LIKE (without multiple statements in the WHERE clause) but the likelihood of these kinds of searches are low. Given this statement, are there significant benefits to using LIKE over BETWEEN?The phone numbers are stored as varchar data and an index exists on the column.Thanks,Sean. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-12-11 : 17:50:37
|
BETWEEN will be much faster generally because it can utilize the range optimization of indexes. This is especially true if you're dealing with a clustered index. The LIKE is more flexible. A combination of the two will kill your server.  You might want to consider having three seperate procedures if you are wanting to fill each of the requirements in your original post. That way, you will get the most optimized plan per search.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-12 : 02:11:52
|
| "BETWEEN will be much faster generally because it can utilize the range optimization of indexes"Derrick, wouldn'tBETWEEN '1-206-555-0000' AND '1-206-555-9999'perform identically toLIKE '1-206-555-%'with a suitable clustered index?Kristen |
|
|
|
|
|
|
LIKE vs BETWEEN