[MSSQL] 修正GuID會重複的問題

點閱: 362

GUID?

全局唯一識別元,簡稱GUID(Globally Unique Identifier),是一種由演算法生成的唯一標識,通常表示成32個16進位數字(0-9,A-F)組成的字串。

ISSUE

公司的etl技術棧中,有幾張表已經會寫入GUID了,但可能是因為同時有多個線程(thread)同時進行寫入,加上原本的設定並不是真的限制了GUID的不可重複性,因此在現有的產品中有幾張表的GUID是有重複的。

解決辦法

幾個步驟:

  1. 移除以GUID設計的主鍵
  2. 移除原本的GUID欄位(因為以unique identifier設計的欄位必須以新增的方式進行,無法直接修改現有欄位之屬性)
  3. 新增具unique identifier屬性的GUID欄位
  4. 因為我有多個資料庫與多張表要處理,因此設計為比較程式化的作法,加速作業流程

程式碼

--1. Set variables
USE [DB_NAME] --CHANGE DB NAME HERE
DECLARE @tbname varchar(50) = 'TABLE_NAME' --CHANGE TABLE NAME HERE

DECLARE @pkname varchar(50)
SELECT @pkname = CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = @tbname

    print('@tbname is ' + @tbname) --check the table name
    print('@pkname is ' + @pkname) --check the pk name

--2. Drop old GuID_ID pk
EXEC ('ALTER TABLE ' + @tbname + ' DROP CONSTRAINT ' + @pkname)

--3. Rename Guid_ID into GuID_ID_old, set GuID_ID_old as nullable
DECLARE @column_old varchar(30) = 'GuID_ID'
DECLARE @column_new varchar(30) = 'GuID_ID_old'
DECLARE @sql varchar(50) = '[' + @tbname + '].[' + @column_old + ']'
    print('@sql is ' + @sql) --check the sql query

EXEC sp_rename @sql, @column_new, 'COLUMN'
EXEC ('ALTER TABLE ' + @tbname + ' ALTER COLUMN ' + @column_new + ' varchar(50) NULL') -- nullable GuID_ID_old

--4. Create new one with uniqueidentifier
EXEC ('ALTER TABLE ' + @tbname + ' ADD GuID_ID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() NOT NULL PRIMARY KEY')

效率比較

為了提升GUID建置效率與降低轉換成本,今天跟公司顧問討論了該如何進行後續的GUID建置作業,顧問丟了一篇文章出出來,內文是說到使用兩種方式(直接新增 VS 新增NULL後更新)的GUID建置效率比較。

結果指出,使用新增NULL後更新的效率比直接新增快了大約10倍的效率,根據這篇文章的經驗,我來測試一下公司資料是否也有此情形。以下使用76,009,838列的測試資料,進行實驗。

測試條件1
  • 直接新增uniqueidentifier類型的GuID_ID欄位,以newsequentialid填入且設為PK(所需時間為16min)
alter table @table_name ADD GuID_ID_new UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() NOT NULL PRIMARY KEY
測試條件2
  • 新增uniqueidentifier類型的GuID_ID欄位,以NULL填入(所需時間為 10min)
  • 以newsequentialid去UPDATE欄位(所需時間為 2min )
  • 將GuID_ID設為pk(所需時間為 3min )
alter table @table_name2 ADD GuID_ID_new UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() NULL --填入NULL值
UPDATE @table_name2
SET  GuID_ID_new = DEFAULT --使用DEFAULT來填入NEWSEQUENTIALID
  --10min
alter table @table_name2 alter column GuID_ID_new uniqueidentifier not null
  --2min
ALTER TABLE @table_name2 ADD CONSTRAINT PK_name PRIMARY KEY (GuID_ID_new)
  --3min
結論

兩種方法比較起來,時間差不多(16min vs 15min)

文獻研讀

NEWID vs NEWSEQUENTIALID

  • NEWID: 不固定的產生隨機的值
  • NEWSEQUENTIALID: 具有規律的值

About the Author

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

Related Posts