>>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.
Thank You
Rollin Burr
Politicians and diapers have one thing in common. They should both be changed regularly, and for the same reason.