Index on a column with like and where pattern?

It does a scan in the first case for the same reason as in the third case: the index searches based on data from the start of the string. With a wildcard at the start of your pattern, there's no way to intelligently search the index for a match. The index does a lexographical (alphabetical) comparison for strings, which is a less than/greater than comparison.

The tree might look something like this:

        /nadejda
    mark
   /    \krishna
cab
   \    /bob
    anna
        \allen

So, searching for a% can be done, as the index will know where to go at each branch. E.g., C > A, so go left. Searching for %a isn't efficiently possible. The index would have to read all the data to figure out if each node ended with A. Having to read all the data means using the index is just wasted overhead.

This is, indeed, the way that SQL Server works. You are asking why.

Think about the index. Functionally, you can think of it as storing the contactname in alphabetical order -- much like a dictionary or telephone book. (Yes, it is usually a more complicated data structure, typically a B-tree, but the result is the items in order).

When you say contactname like 'a%', then the query optimizer knows that it only needs to look at entries that start with the letter "a". The index knows exactly where those are, so the optimizer can use a seek to get to them. SQL Server implements this optimization for like (not all databases do this).

When you say contactname like '%a', you are saying "find me the entries that end in 'a'". That is a lot like looking through a dictionary to get all the words that end in "a". The ordering is not of any help. There might be entries that start with "a" and end with "a'. There might be entries that start with "z" and end with "a". So, these types of expressions require a scan instead of a seek.

Ok so to break this down for you when you create your index on your character string column. It will store them by the first letter so your data is stored like this.

allen
anna
bob
cab
krishna
mark
nadejda

So when you are doing your LIKE 'a%' Sql server can use the index to pare down the results by looking through the order for everything with an 'A' in the begining. However once you have placed your wildcard at the end LIKE '%A' you have now forced the engine to scan through the entire table to look for anything ending with the letter A.

Of course this is an incredibly simplified explanation.