Please don't get me wrong. If SQL finds that either first name or last name with the parameters passed will give you a good plan, it will use that index.

The problem is that now your query has a plan that uses that index (let's say it used last name).

The next time you call this same procedure and supply a first name and NULL for last name, the query plan is ready and SQL will not create a new one.

So in our example SQL will read the whole table using the last name index to find people by first name, which is even worse than doing a full table scan 🙂