[DB Design] Primary Key: Identity No. vs GUID

接觸不同application, 會見到部份DB使用increment number或者GUID做Primary Key (PK). 自己也有點好奇, 故記錄如下:

Indexing

寫program都知道, 比較一個numeric value 與比較string, 通常前者會較快, 亦因此index creation / rebuild 亦會較快. 在SQL server 2005 後, 提供了clustered index, 令其存取加快.

Online / Offline

當寫mobile app, 須要將data sync時, 若使用GUID, 其comparison 會相對較方便, 相反, 若用numeric 的話, 可能須要比較內容, 除了效能會較慢, 出錯機會亦相對較高.

Uniqueness

GUID其獨立性使其獨立於不同DB, 即是在不同table中亦不會有相同PK; 在migration時有其好處.

Storage

GUID 始終都是一串string, 與numeric value 比較其size亦較大. 若data size 越來越大, 其PK / FK Storage cost亦越來越高;

About C.H. Ling 262 Articles
a .net / Java developer from Hong Kong and currently located in United Kingdom. Thanks for Google because it solve many technical problems so I build this blog as return. Besides coding and trying advance technology, hiking and traveling is other favorite to me, so I will write down something what I see and what I feel during it. Happy reading!!!

Be the first to comment

Leave a Reply

Your email address will not be published.


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.