>>Is there a significant difference in using an auto-integer index key field than in using a real-world field as a key field?
>>
>>My reason for using the real world is that this table has many relations, including several many-to-many reconciliation tables, where many joins may be avoided (in procedures, views, etc.) by having the real data already there instead of having to go and get it.
>>
>>My colleague feels that the best advantage is in using auto indexes.
>>
>>I have tried to look into best practices, but do not find any clear indications.
>>
>>All input is appreciated.
>
>If the real field is unique and you are very sure that it is unique, there is no advatages to use Identity fields. The only one advantage I can see is that you don't worry about the uniqueness of the Key field when you use Identity, SQL Server does :-)
>But this is only my oppinion :-)

Thanks for your reply Borislav. My colleague feels that smaller clustered indexes are faster than ones a little larger (not overly large), and that joins using integers are faster than ones using strings. I think that by avoiding a significant number of needed joins, the efficiency gained is better than any losses. I am trying to gain a better understanding of it all. The real key would definitely be unique and constant.