In other words, if my key is lastname + firstname, my query would need to be SELECT * FROM Names WHERE LastName + FirstName LIKE 'Smith%'. Is that right?
If you have an index defined on Names (LastName, FirstName) then queries of either of these forms would be able to seek
WHERE LastName = 'Smith' AND FirstName = 'John'
WHERE LastName = 'Smith'
Queries of this form would not be able to seel
WHERE FirstName = 'John'
because FirstName is not the leading column of an index.
As for concatenating columns together, doing that prevents index seeks at all, regardless of what indexes exist.
WHERE LastName + FirstName LIKE 'Smith%' is not a SARGable predicate.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass