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

今晚的程式過版中,有一部分是大型資料表要加開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'

 

練習結果:

  • Null(鬧)就是沒有值,想看內碼也不行,再還我們NULL(鬧)
  • Null和空字串的空間用量相同,空白多了一些

 


小結:

  • 資料表若有部分欄位值是NULL,AP程式會困擾到,這和放空白要TRIM有類似。
  • 望DBA大人拯救黎明蒼生
  • Default Value + Not Null = 團結力量大 = 給值
  • 5000萬筆胖資料..
  • 測試環境比正式快太多的問題待查,結果可以參考這篇

 


參考

Docs 指定資料行的預設值

Docs ISNULL (Transact-SQL)

ACID 屬性