今晚的程式過版中,有一部分是大型資料表要加開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設定好欄位,再找時間分批更新舊資料列的值了。
交易是整合性的單位,因為它保留資料的一致性,轉換資料的一個一致狀態到資料的另一個一致狀態。
一致性要求交易繫結的資料在語意上完整保留。維護一致性的某些責任落在應用程式開發人員身上,其必須確認應用程式強制執行所有已知的完整性條件約束 (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萬筆胖資料..
- 測試環境比正式快太多的問題待查,結果可以參考這篇。