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 |
|
seware
Starting Member
11 Posts |
 Posted - 2012-01-23 : 10:27:33
|
| I have a need to search multiple columns of a table for a given search term...currently I am doing it like this:SELECT ig.blah FROM ig WHERE (ig.Level1 LIKE @SearchTerm OR ig.Level2 LIKE @SearchTerm OR ig.Level3 LIKE @SearchTerm OR ig.Level4 LIKE @SearchTerm OR ig.Level5 LIKE @SearchTerm OR ig.Level6 LIKE @SearchTerm OR ig.Level7 LIKE @SearchTerm OR ig.Level8 LIKE @SearchTerm) Surely there is a better way...I thought about concatenating the columns and doing one LIKE search but that seems messy. As it is now, is the optimizer smart enough to bypass the remaining ORs if a satisfying case is found earlier in the mess of 'OR's? (I'm a programmer, where this makes sense... don't know much about SQL internals yet)I researched full-text indexing, but found that this was not a possibility because the table contains 3 primary keys (Why?... before my time here)Using SQL Server 2008 R2Thanks for any direction you can provide. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-23 : 14:09:14
|
| While I never want to say never, I think what you are doing is as good as any. Attempting to concatenate the columns and search is not really going to help you. If you are running into performance problems with the query, it may be that you need proper indexes, or may be that your search string is not sargable etc. If that is the case, can you post sample data and table DDL's (see Brett's blog here for some guidance: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx ) |
 |
|
|
|
|
|
Multiple column text search optimization