Jeff, thanks for that great test script. I like your code for building 1,000,000 row table. I'm saving this code for reference.

Thanks for pointing out how much more efficient LEFT is than LIKE or CHARINDEX. (I knew that, but wasn't thinking about it.)

My original WHERE clause:

WHERE Address LIKE 'XX%' would be much faster as

WHERE LEFT(Address,2) = 'XX'

It's easy to get caught up in

WHERE Col1 LIKE '%aa%'

OR Col1 LIKE '%bb%

OR Col1 LIKE 'xx%'

Maybe this is where LIKE gets the bad rap - when the code should be using LEFT() instead.