拿GUIDs當Primary Key

最近維護個專案，發現是拿GUID當做DB的Primary Key用，同事還說最近大家都這麼用...

一直想不到好處在哪，只好上網看看評論

• 主要還是建議用integer做pk

因為用integer做auto increment達到不重覆有額外的優點

• 連續，在排序上能做到partial scan，不會用到table scan讓搜尋效能較好
  這點我是不瞭解，平常的專案可能沒什麼問題
  不過最近幾個project是電子商務，常遇到效能的issue
  發現有table scan的sql statment都會被吊起打
  
  不過後來有問"專業"的DBA
  他說pk都有index啦，誰管他連不連續... 這... Orz
  
  
• guid的index資料較大
  在搜尋上，也會因guid比integer大多了，要比對更大的資料(25MB vs 106MB)
  
• 讓別人看不出銷售情況
  例如訂單編號如果很單純(天真?)的利用流水號給客戶使用
  容易被人看出最近的銷售狀況

所以...
目前看的好處是分散式的DB好運作
也有同事提出如果常有table需合併時，就很適合guid
所以還是得評估自己需要的solution

以下截錄，推薦的三篇po文也滿精采的 可以看一下
Ref: Identity Column as Primary Key

Yes, using a INT (or BIGINT) IDENTITY is very good practice for SQL Server.
SQL Server uses the primary key as its default clustering key, and the clustering key should always have these properties:

• narrow

• static

• unique

• ever-increasing

INT IDENTITY fits the bill perfectly!
For more background info, and especially some info why a GUID as your primary (and thus clustering key) is a bad idea, see Kimberly Tripp's excellent posts:

• GUIDs as PRIMARY KEYs and/or the clustering key

• The Clustered Index Debate Continues...

• Ever-increasing clustering key - the Clustered Index Debate..........again!

If you have reasons to use a GUID as primary key (e.g. replication), then by all means make sure to have a INT IDENTITY as your clustering key on those tables!
Marc