ansaurus

Question

Answer 1

+7 A:

In my experience the first %-sign will make any index useless, but one at the end will use the index.

idstam 2009-09-07 07:47:46

Close enough - LIKE will use the prefix on an index but you can't resolve a wildcard through an index so that's as far as it will go.

ConcernedOfTunbridgeWells 2009-09-07 08:00:46

So yay or nay for indexes on these columns?

Niels Bosma 2009-09-07 08:02:03

If you always query the column with a leading %, the index will never be used. The answer thus depends on how you query your data.

Lieven 2009-09-07 08:43:43

*NOT COMPLETELY TRUE* see my answer, you can hit an index on _LIKE "%abc"_ queries with a little extra work

KM 2009-09-09 12:57:27

**NOT COMPLETELY TRUE**, you _CAN_ hit an index on queries that use _LIKE '%xyz'_, but you need to do a little extra work, see my answer for an explanation

KM 2009-09-09 13:00:56

Answer 2

+1 A:

To answer the metrics part of your question: The type of index/table scan/seek being performed is a good indicator for knowing if an index is being (properly) used. It's usually shown topmost in the query plan analyzer. The following scan/seek types are sorted from worst (top) to best (bottom):

Table Scan
Clustered Index Scan
Index Scan
Clustered Index Seek
Index Seek

As a rule of thumb, you would normally try to get seeks over scans whenever possible. As always, there are exceptions depending on table size, queried columns, etc. I recommend doing a search on StackOverflow for "scan seek index", and you'll get a lot of good information about this subject.

MicSim 2009-09-08 11:56:50

Answer 3

+6 A:

Given the data 'abcdefg'

WHERE Column1 LIKE '%cde%'  --can't use an index

WHERE Column1 LIKE 'abc%' --can use and index

WHERE Column1 Like '%defg' --can't use an index, but see note below

Note: If you have important queries that require '%defg', you could use a persistent computed column where you REVERSE() the column and then index it. Your can then query on:

WHERE Column1Reverse Like REVERSE('defg')+'%' --can use the persistent computed column's index

KM 2009-09-08 19:32:43

I like your solution, though I'd call it an ugly hack ;)

idstam 2009-09-09 19:35:57

I've used this ugly hack on a system where the original designer made numbers char(x) with leading zeros and other keys letter+ leading zeros. try telling users they have to enter in the leading zeros and or letter+ leading zeros! ha. they want to enter in 203, not G000000203, so this reverse() "hack" will help there.

KM 2009-09-09 20:14:41

ansaurus

tags:

views:

answers:

SQL Server: Index columns used in like?

related questions