[SQL server 2K] What is the impact coming from wildcard %

Question

Hi guys:

I need to add a new SELECT statement whose search condition include something like leagueCode LIKE 'nba%'.

I would like to know if the index against leagueCode is still exploitable or introduce any overhead after % is included in the target column.

Answer 1

Yes, the index can still be used because you have a constant prefix (nba in this case).

Answer 2

it depends 100% on the position of the wildcard.

• At the beginning: no index. Some servers have inverse indices (starting at the end of the string) and would use them - SQL Server does not.
• In the middle: Partial index (beginning part), then seeking within that range.
• in the end: Index (Acutally an INDEX SEEK will be shown in the query analyzer).

Overhead? Sure - but it is not exactly high (no table scan) and there is not exactly a way to work around it.