CODEWITHSUNDEEP
sql-servert-sqlindexing
Niels Bosma
Niels Bosma

Reputation: 11508

SQL Server: Index columns used in like?

Is it a good idea to index varchar columns only used in LIKE opertations? From what I can read from query analytics I get from the following query:

SELECT * FROM ClientUsers WHERE Email LIKE '%niels@bosmainter%'

I get an "Estimated subtree cost" of 0.38 without any index and 0.14 with an index. Is this a good metric to use for anlayzing if a query has been optimized with an index?

Upvotes: 62

Views: 46743

Answers (3)

MicSim
MicSim

Reputation: 26816

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.

Upvotes: 10

KM.
KM.

Reputation: 103667

Given the data 'abcdefg'

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

WHERE Column1 LIKE 'abc%' --can use an 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

Upvotes: 112

idstam
idstam

Reputation: 2878

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

Upvotes: 15

Related Questions