While I strongly approve of the use of surrogate primary keys instead of
natural primary keys, I disagree somewhat with your assertions.
> Because the index associated with it does not need to be
> updated when an update is made.
This is true, but within a well-designed database schema, you'd typically
have one or more indexes on the fields that would have been part of a
natural primary key, which may need to be updated when the record is
changed.
> Also, an auto increment unique field is best since the key is
> always added at the end of the index.
This can actually cause problems under heavy load, if you're using a
clustered index in which the records are actually physically ordered
according to the index. Since all changes need to be made at the end of the
index, the database may lock the leaf, page or extent containing those
records. I've heard this called an index "hotspot" before.
> Also, the shortest the unique key, the better it is, because
> the index is smaller and faster.
I suspect that the difference in performance between an integer and a
GUID/UUID is relatively minor.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
phone: 202-797-5496
fax: 202-797-5444
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
