How To Efficiently Index Using a VARCHAR Column?

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  September 19, 2009 at 3:02 am

  #1055930

  --SELECT * FROM #TestTable WHERE UniqueVarChar = 'FC6AC668-45E2-473A-9CBD-2A9126CE536E'

  --SELECT * FROM #TestTable WHERE UniqueVarChar LIKE '%BD-2A9126CE53%'

  --SELECT * FROM #TestTable WHERE UniqueVarChar LIKE '%FC6AC668-45E2-473A-9CBD-2A9126CE536E%'

  Lee

  Thanks for posting your results.

  As Grant said, sometimes indexes and LIKE queries get on, sometimes they don't.

  The two like queries you are using will never use the index because of the % at the front of the predicate. That's telling the optimiser to look for the value ANYWHERE in the varchar column, so the index is unusable for that.

  If your like queries were:-

  --SELECT * FROM #TestTable WHERE UniqueVarChar LIKE 'BD-2A9126CE53%'

  --SELECT * FROM #TestTable WHERE UniqueVarChar LIKE 'FC6AC668-45E2-473A-9CBD-2A9126CE536E%' (no % at the front), then there is a very good chance that the optimiser would use the index, as it's telling it that you want anything that STARTS with those characters. That allows the optimer to position itself a the point in the index where that combination of characters starts, and then read sequentially to find all the rows that match.

  Without the % at the front, I believe the optimser turns that part of the query into something like

  UniqueVarChar >= 'FC6AC668-45E2-473A-9CBD-2A9126CE536E' AND UniqueVarchar <

  'FC6AC668-45E2-473A-9CBD-2A9126CE536F'

  Try them, and look at the execution plan, and I think you can see exactly what it turns them into.

  Disclaimer.

  If you actually need to search for something that contains those characters anywhere in the string, then what I've just told you is complete twaddle.:-)

• Gail Wanabee

  SSCrazy Eights

  Points: 8471

  More actions

  September 19, 2009 at 11:04 am

  #1055970

  Ian,

  Regarding your comment:

  "The two like queries you are using will never use the index because of the % at the front of the predicate. That's telling the optimiser to look for the value ANYWHERE in the varchar column, so the index is unusable for that."

  Wouldn't the Optimizer scan the index instead of scanning the data records? If so, I would think that would offer some efficiency.

  LC

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  September 19, 2009 at 2:32 pm

  #1055988

  crainlee2 (9/19/2009)

  ---

  Wouldn't the Optimizer scan the index instead of scanning the data records? If so, I would think that would offer some efficiency.

  You are absolutely right Lee. It would scan the index instead, which, as you say, would be more efficient than scanning the whole table... but on a large index it may still be slow, and you are still reading a lot of data, just to discard it.

  If I was only expecting to return a few rows from my query, then I would be looking for a plan that showed an index seek.