What's the ultimate way to index name and surname of the person? Allows think that customers can search name, surname and each of them together. Should i must create multiple column index or seperate indexes? Should i must use index prefixes? Also we are able to think that individuals are from different nations. Would using prefixes be unnecessary for the reason that situation?
Situation is simply obvious.
Create index on surname, and optionally add name towards the same index on second position.
This is actually the consequence of frequent used business rule, that searching is performed by surname. Surname is easily the most selective, and then any searching form bases on surname. (obviously if you're not creating application for Icelandic company).
Adding name is optional, and is dependent on size table
You need to produce a Name/Surname multi-column index.
You need to produce a Surname/Name multi-column index.
Observe that card inserts increase is going to be reduced (you do not get something for free).
I am unsure I am understanding your question properly, however your best choice is always to produce a nonclustered index around the most queried fields. For example, you may create a nonclustered index on firstname and lastname. You need to see performance gains using that.