延續上週新增資料表欄位Default Value + Not Null是否會給舊資料預設值的題目。上一篇確認Default Value + Not Null會給也必須要給舊資料預設值，但為何相同資料量測試環境飛快但正式環境跑1小時?這種飛快，感覺不太真實，資料分頁真的有修改到?還是只有改定義?
SQL版本差異(SQL 2008 vs 2014)
因為客戶專案要升級SQL，正式環境是SQL 2008 R2，測試環境是SQL 2014，會不會是DB版本造成效能的明顯差異?!
經過簡單的實驗，發現我們又踩了DB版本差異的坑，2012之後，她真的沒有去改資料分頁，她靠一種冰魔法來維持ACID的一致性，筆記實驗的過程:
對照組(SQL2008 R2)
建立資料表，然後新增4筆冰雪奇緣的人物；然後新增欄位C3，用預設值'Frozen' + NOT NULL
SELECT @@version
use tempdb
CREATE TABLE T1(
C1 INT IDENTITY,
C2 VARCHAR(30)
)
--冰雪奇緣(安娜,艾莎,雪寶,阿克)
INSERT INTO T1
VALUES ('Anna'), ('Elsa'), ('Olaf'), ('Kristoff')
--新增冰雪奇緣Frozen
ALTER TABLE T1
ADD C3 VARCHAR(30) DEFAULT 'Frozen' NOT NULL
SQL 版本
查看資料列所在的分頁
SELECT
%%lockres%% as lockres
,%%physloc%% as physloc
,b.file_id
,b.page_id
,b.slot_id
,a.* from T1 a
CROSS APPLY fn_PhysLocCracker(%%physloc%%) b
查看資料列對應的分頁資訊
查看分頁
dbcc traceon (3604,-1)
dbcc page(2,1,114,2)
dbcc page參數說明
dbcc page ( {‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
- tempdb id固定為2
- file id=1
- page id=114
- 2=page header plus whole page hex dump
Page Demp
的確有新增Frozen，而且是整筆重新新增，可以注意Anna、Elsa、olfa、kristoff..都有兩個，分別是新增欄位前與後的資料列內容，我想這也是正式環境執行超過1小時的原因。
實驗組(SQL 2014)
建立資料表，然後新增4筆冰雪奇緣的人物；然後新增欄位C3，用預設值'Frozen' + NOT NULL
SELECT @@version
USE tempdb
CREATE TABLE T1(
C1 INT IDENTITY,
C2 VARCHAR(30)
)
--冰雪奇緣(安娜,艾莎,雪寶,阿克)
INSERT INTO T1
VALUES ('Anna'), ('Elsa'), ('Olaf'), ('Kristoff')
--新增冰雪奇緣Frozen
ALTER TABLE T1
ADD C3 VARCHAR(30) DEFAULT 'Frozen' NOT NULL
查看資料列所在的分頁
SELECT
%%lockres%% as lockres
,%%physloc%% as physloc
,b.file_id
,b.page_id
,b.slot_id
,a.* from T1 a
CROSS APPLY fn_PhysLocCracker(%%physloc%%) b
查看資料列對應的分頁資訊
查看分頁
dbcc traceon (3604,-1)
dbcc page(2,1,118,2)
沒有Frozen也!這代表資料分頁沒有修改。
不過，查詢資料有Frozen。
select * from t1
查詢結果集:
這是一種SQL Server的冰魔法! 資料分頁雖然沒有實際給了預設值，但查詢有額外的預設值定義處理，帶了我們設定的預設值。
難怪測環境試很快(SQL2014只修改定義)，正式慢(SQL2008要一筆一筆新增)。
新增欄位後新的資料
再試試新增一筆壞漢斯王子，然後查看Page Dump
INSERT into T1 VALUES('Hans','Frozen')
dbcc traceon (3604,-1)
dbcc page(2,1,118,2)
Page Demp
漢斯的尾巴有出來了，有Frozen! 不過舊資料(Elsa、Anna..)後面還是沒有Frozen。
哪一個版本開始出現
相同魔法在SQL2012就出現了!
檢查Page Header是否有修改(用SQL 2012測)
新增欄位前
最後的lsn=(45:56721:291)
新增欄位後
新增欄位時，選擇Default Value + Not Null，實際的Data Page最後的lsn=(45:56721:291)總長度也沒改變，真的沒改Data Page。
小結:
- 發現是自己智慧未開，原來SQL2012 優化了Alter Table Default Value Not Null的作法，新增欄位時不需要再對舊資料一筆一筆給預設值，她直接修改了內部partition column的定義，select list回傳結果集時自動幫我們用default value替代掉沒有值(Null)的欄位。
- Default Value + Not Null會給值，但是2012之後有冰魔法，以邏輯取代物理的方式確保ACID的一致性，她更快了。
- 版本坑+1，測試和正式的DB版本要一樣。
- SQL Server 2008 R2 SP3 主流支援2014年7月結束，預計2019年7月將停止延伸性(包含安全性)的支援。
最愛雪寶的summer歌曲
2017.02 東京迪士尼
參考:
How to use DBCC PAGE
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2006/06/10/how-to-use-dbcc-page/
UNDOCUMENTED VIRTUAL COLUMN: %%LOCKRES%
https://www.scarydba.com/2010/03/18/undocumented-virtual-column-lockres/