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

Hits: 1249

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: 具有規律的值