SSMS操作資料表欄位效能問題

使用Microsoft SQL Server Management Studio(本文之後都簡稱為SSMS)圖形介面操作帶給我們許多方便，但有些時候應該直接以T-SQL語法下達，以下是以資料表新增欄位來說明效能問題

原始資料表schema

CREATE TABLE [dbo].[tbl]
(
    [col1] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
    [col2] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL
) ON [PRIMARY]

我們針對上述的資料表tbl新增欄位col3，分別以SSMS和T-SQL操作並以SQL Server Profiler錄製以了解背後運作過程

SSMS操作畫面


SSMS錄製結果

CREATE TABLE dbo.Tmp_tbl
(
    col1 nvarchar(50) NOT NULL,
    col2 nvarchar(50) NOT NULL,
    col3 nvarchar(50) NOT NULL
)  ON [PRIMARY]

IF EXISTS(SELECT * FROM dbo.tbl)
    EXEC('INSERT INTO dbo.Tmp_tbl (col1, col2)
        SELECT col1, col2 FROM dbo.tbl WITH (HOLDLOCK TABLOCKX)')

DROP TABLE dbo.tbl

EXECUTE sp_rename N'dbo.Tmp_tbl', N'tbl', 'OBJECT'

T-SQL操作畫面


T-SQL錄製結果

alter table tbl
    add col3 nvarchar(50) not null

由上面的結果我們可以發現利用SSMS新增欄位時會執行四個步驟
1.建立以Tmp_開頭的資料表
2.將原本的資料表資料寫入Tmp_資料表
3.drop原本資料表
4.將Tmp_資料表改名

如果要新增欄位的資料表本身含有大量資料，此時就不適合利用SSMS應該改以直接下達T-SQL以減輕系統負載，另外變更資料表欄位類型也跟新增欄位執行相同的步驟。