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 |
|
mortalan
Starting Member
3 Posts |
 Posted - 2002-05-27 : 03:35:26
|
| At first thx to all who helped me creating an index!Bit now i have a further question:does an index really speed-up an SQL statement like this:SELECT * FROM products WHERE ProdText1 LIKE '%UserDefinded%' or ProdText2 LIKE '%UserDefined%' OR ProdText3 LIKE '%UserDefined%'I thought about it after creating my index. I've no auto-increment on that table. So is it really usefull creating an index ??That table has over 85.000 products an was daily deleted (TRUNCATE TABLE) and updated completely.thx |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-27 : 08:50:51
|
| The problem is that you are using LIKE as your comparison, and that you are preceding the search with a "%" symbol. SQL Server's optimizer cannot use an index in this case because LIKE is searching parts of the value, not the whole thing. There's not much you can do to speed up a LIKE match.You can use full-text indexing, however. It is not the same as a regular table index though. It is well documented in Books Online, also look under CONTAINS and FREETEXT for more info on how to search full-text indexed columns. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-05-27 : 09:28:31
|
| You could put a compound non-clustered index on ProdText1, ProdText2, ProdText3Then it may scan the index - but these are probably large fields so it may even cause more reads and may not be used.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Index really usefull ?