Gail,

Excellent point. I should have mentioned that a missing propert for the first column of a composite index results in an index scan. Glad you did.

The technique doesn't work as well when the missing column is the first item in the key. It will indeed do a complete index scan.

The technique works better the deeper into the composite index the missing column value is found.

Even with a complete index scan, given that no better index exist, this may be more desireable than a table scan.

As a note, if the scenario discussed was a reality, I would probably create two indexes. One composite index without the last name, and an index on Last Name only.

Then SQL Server can return the intersection of the two indexes before going to the base data if Last name is required. If not, it will simply use the appropriate index.

Ben