[SQL Server][T-SQL]新增資料表欄位(Default Value加上Not Null會不會自動將舊資料給預設值)

2017-11-29

12048
0
T-SQL
2017-12-07

今晚的程式過版中，有一部分是大型資料表要加開10幾個欄位來跟上行動支付潮流，到家後接到客戶電話，DBA大人執行DDL Alter Table欄位新增語法(Add Column)時，執行了1小時(....)。印象中同事的語法沒有加With Values，~~應該只修改到定義，沒有改到原始的資料分頁~~，另外，測試環境類似的交易量跑起來也很快，呼~~~

卡住的時候就是該重新測試填補SQL知識了，這篇先確認SQL是否會將舊資料給預設值，下一篇來查效能問題。

建立環境

USE TEMPDB
 
--建立臨時資料表
CREATE TABLE T1
(RID INT IDENTITY,
  NAME NVARCHAR(20)
PRIMARY KEY(RID))

INSERT INTO T1 VALUES('Visual Studio難波萬')
SELECT * FROM T1

我們有了Visual Studio 難波萬的資料了~

測試是否會修改舊資料給預設值

分別用5種設定欄位是否可NULL、預設值及WITH VALUES的組合

ALTER TABLE T1
ADD 
          C1 DECIMAL       DEFAULT        0 NOT NULL,
          C2 DECIMAL       DEFAULT        0,
          C3 DECIMAL,
          C4 DECIMAL DEFAULT 0 WITH VALUES,
          C5 DECIMAL DEFAULT 0 WITH VALUES NOT NULL
SELECT * FROM T1

一直以為，只有C4和C5有WITH VALUES會設定預設值0，但是多了C1(DEFAULT + NOT NULL)

然後這次上線的語法就是C1了(DEFAULT 0 NOT NULL)，三個黑人問號..

後來想想，將舊資料給預設值很合理，因為我們設定了C1 NOT NULL及Default Value的條件約束，這時若C1沒有預設值而出現NULL，這將表示ACID特性的"C"一致性(Consistency)沒辦法確保，將會破壞了原先對資料表設定的條件約束。

只是壞就壞在資料表是超大型資料表，只好先採用C2或C3設定好欄位，再找時間分批更新舊資料列的值了。

msdn對ACID一致性的中文說明:

交易是整合性的單位，因為它保留資料的一致性，轉換資料的一個一致狀態到資料的另一個一致狀態。

一致性要求交易繫結的資料在語意上完整保留。維護一致性的某些責任落在應用程式開發人員身上，其必須確認應用程式強制執行所有已知的完整性條件約束 (Constraint)。例如，開發移轉金錢的應用程式時，您應該避免在移轉期間任意移動小數點。

NULL、空字串及空格之空間比較

驚嚇之餘，想起上禮拜參加SQL Pass，厲害的Colin大師講到了NULL(鬧)、空字串及空格之空間比較，趁女兒睡著，待會可能還要接電話，練習。

use tempdb;
go

--建立資料表, 分別存放NULL, 空字串, 空格
create table tempdb.dbo.tbl_Null (id int identity(1,1) primary key, string varchar(5) NULL);
create table tempdb.dbo.tbl_Empty (id int identity(1,1) primary key, string varchar(5) NULL);
create table tempdb.dbo.tbl_Space (id int identity(1,1) primary key, string varchar(5) NULL);
go

--大量寫入資料以做比對
;WITH string_(insert_) AS (SELECT 0 UNION ALL SELECT insert_+1 FROM string_ WHERE insert_ < 1000000)
INSERT tempdb.dbo.tbl_Null(string) SELECT NULL FROM string_ OPTION (MAXRECURSION 0);
INSERT tempdb.dbo.tbl_Empty(string) SELECT '' FROM tempdb.dbo.tbl_Null;
INSERT tempdb.dbo.tbl_Space(string) SELECT ' ' FROM tempdb.dbo.tbl_Null;

--查看資料及內碼
select top 1 *,CONVERT(VARBINARY,string) from tempdb.dbo.tbl_Null;
select top 1 *,CONVERT(VARBINARY,string) from tempdb.dbo.tbl_Empty;
select top 1 *,CONVERT(VARBINARY,string) from tempdb.dbo.tbl_Space;

EXEC sp_spaceused 'tbl_Null'
EXEC sp_spaceused 'tbl_Empty'
EXEC sp_spaceused 'tbl_Space'

練習結果: