SQL Server中的偽列

下午看QQ群有人在討論（非聚集）索引的存儲(chǔ)，說(shuō)，對(duì)于聚集索引表，非聚集索引存儲(chǔ)的是索引鍵值+聚集索引鍵值；對(duì)于非聚集索引表，索引存儲(chǔ)的是索引鍵值+RowId，這應(yīng)該是一個(gè)常識(shí)，對(duì)此不作具體詳細(xì)闡述。

這里主要是提到的RowId引起了一點(diǎn)思考。

那么，這個(gè)RowId是個(gè)什么玩意？能不能更加直觀一點(diǎn)來(lái)看看RowId的信息？代表什么含義？這個(gè)當(dāng)然也是可以的。

Oracle中的表中有一個(gè)偽列的概念，就是在查詢(xún)表的時(shí)候加上select rowid，* from Table，會(huì)查詢(xún)出來(lái)偽列。

SQL Server中同樣有這么一個(gè)偽列，在SQL Server中，這個(gè)偽列可以認(rèn)為是數(shù)據(jù)行的物理地址，下面簡(jiǎn)單來(lái)觀察一下這個(gè)RowId以及RowId的含義。

偽列的測(cè)試

建一張簡(jiǎn)單的表，下面借助這個(gè)表來(lái)查看說(shuō)明偽列

CREATE TABLE Test( id int identity(1,1), name varchar(50))GOINSERT INTO Test VALUES (NEWID())GO 100

SQL Server中有一個(gè)未公開(kāi)的偽列“%%physloc%%”，也就是在查詢(xún)的時(shí)候，對(duì)于任何一張表，可以加上這個(gè)字段，比如如下，就可以查到表中每一行的偽列。

這個(gè)偽列的類(lèi)型是binary(8) ，也就是有8個(gè)字節(jié)，參考上圖的DATALENGTH(%%physloc%%) as Len，%%physloc%%返回的記錄的物理地址，其中前四個(gè)字節(jié)表示頁(yè)號(hào)，中間兩個(gè)字節(jié)表示文件號(hào)，最后兩個(gè)字節(jié)表示槽號(hào)
為了更加方便地觀察偽列的含義，sqlserver提供了一個(gè)未公開(kāi)的系統(tǒng)函數(shù)sys.fn_PhysLocFormatter，下面借助sys.fn_PhysLocFormatter這個(gè)函數(shù)來(lái)繼續(xù)觀察這個(gè)偽列

如下圖，這里就可以清晰地看到偽列中的信息了。

比如第一行中的(1:73:0)，上面說(shuō)了，其中前四個(gè)字節(jié)表示頁(yè)號(hào)，中間兩個(gè)字節(jié)表示文件號(hào)，最后兩個(gè)字節(jié)表示槽號(hào)，(1:73:0)這種格式是經(jīng)過(guò)sys.fn_PhysLocFormatter格式化顯式之后的結(jié)果。

把文件號(hào)1放在最前面，中間的73是頁(yè)號(hào)（page number），最后一位0是槽號(hào)（sloc number）。

下面粗略地說(shuō)一下這幾個(gè)字段的含義。這里要求對(duì)SQL Server的存儲(chǔ)只是有一個(gè)基本的認(rèn)識(shí)，否則看的云里霧里。

1，首先說(shuō)什么是文件號(hào)

如截圖，文件號(hào)就是數(shù)據(jù)庫(kù)的數(shù)據(jù)文件編號(hào)，這里只有一個(gè)數(shù)據(jù)文件，文件編號(hào)為1，建表的時(shí)候默認(rèn)（這里也只能建立）建立在fileid = 1 的文件上面，fileid=2的是日志文件，就不多說(shuō)了。

2，其次是頁(yè)號(hào)，頁(yè)號(hào)就是分配給當(dāng)前這張表的數(shù)據(jù)頁(yè)面（8kb的最小分配單元）的頁(yè)號(hào)，我們看一下Test這個(gè)表的頁(yè)面情況

借助DBCC IND命令，查詢(xún)分配給這個(gè)表的頁(yè)面信息，其中77號(hào)頁(yè)面是IMA也面，至于什么事IMA頁(yè)面，不多解釋。

73號(hào)頁(yè)面才是真正存儲(chǔ)數(shù)據(jù)的頁(yè)，與上面的1:73:0中的73一樣，沒(méi)毛病。

3，最后看一下槽號(hào)，槽號(hào)的概念要對(duì)SQL Server的數(shù)據(jù)頁(yè)面有一個(gè)基本的認(rèn)識(shí)，這里盜用一張網(wǎng)友的圖。

所謂的槽號(hào)就是在數(shù)據(jù)頁(yè)面中，每個(gè)頁(yè)面存儲(chǔ)多行數(shù)據(jù)，槽號(hào)用來(lái)標(biāo)記每一行數(shù)據(jù)的偏移量，用大白話說(shuō)就是“存儲(chǔ)每一行數(shù)據(jù)的地址空間開(kāi)始的位置”，因?yàn)槊恳恍袛?shù)據(jù)的總長(zhǎng)度是不一樣的（存在可變長(zhǎng)度列的情況下），每一行的占用的存儲(chǔ)空間也是不一樣的，槽號(hào)或者行偏移量就是說(shuō)明每一行數(shù)據(jù)在頁(yè)內(nèi)的開(kāi)始位置。

不過(guò)sys.fn_PhysLocFormatter格式化顯式的槽號(hào)并不是如下截圖的偏移量，而是第N個(gè)數(shù)據(jù)行的這個(gè)N的信息，因此第1行的槽號(hào)就是1，第2行的槽號(hào)就是2，以此類(lèi)推，當(dāng)?shù)谝粋€(gè)page存儲(chǔ)滿(mǎn)之后，從第二個(gè)page開(kāi)始存儲(chǔ)，槽號(hào)又從0開(kāi)始編號(hào)且累加

至此，對(duì)SQL Server的偽列，也就說(shuō)經(jīng)常說(shuō)的RowId有了一個(gè)簡(jiǎn)單的認(rèn)識(shí)。

這里可以認(rèn)為，在SQL Server數(shù)據(jù)庫(kù)中，偽列RowId就是數(shù)據(jù)行的物理地址，至于別的數(shù)據(jù)庫(kù)中的偽列（RowId）是不是物理地址倒是不確定（很有可能也是的）

這里簡(jiǎn)單提一下一開(kāi)始說(shuō)的一個(gè)問(wèn)題：
為什么SQL Server的聚集表（有聚集索引的表）存儲(chǔ)數(shù)據(jù)的時(shí)候存儲(chǔ)的是“索引鍵值+聚集索引鍵值”,對(duì)于非聚集索引表，索引存儲(chǔ)的是索引鍵值+RowId？

或者反過(guò)來(lái)說(shuō)，為什么聚集索引表的非聚集索引存儲(chǔ)的是“索引鍵值+聚集索引鍵值”而不是“索引存儲(chǔ)的是索引鍵值+RowId”

作為一個(gè)常識(shí)，聚集索引要按照聚集索引的順序存放，這就意味著聚集索引表的行數(shù)據(jù)物理位置有可能發(fā)生變化，比如在眾所周知的“頁(yè)拆分（page split）”中發(fā)生變化，在數(shù)據(jù)行的物理位置發(fā)生了變化的時(shí)候，如果非聚集索引存儲(chǔ)的是索引鍵值+RowId，那么這個(gè)RowId也勢(shì)必要發(fā)生變化，這個(gè)變化當(dāng)然要耗費(fèi)一定的性能，為了防止此種情況的發(fā)生，聚集表中的非聚集索引存儲(chǔ)成相對(duì)不變的索引鍵值+聚集索引鍵值，因?yàn)樵跀?shù)據(jù)行的物理位置發(fā)生變化的時(shí)候，聚集索引鍵值是相對(duì)不變的，這一點(diǎn)也不難理解。

當(dāng)然有一種例外，當(dāng)對(duì)聚集索引表做更新的時(shí)候，直接更新聚集索引的鍵值，這樣的話，也有可能造成聚集索引表中當(dāng)前數(shù)據(jù)行的物理位置發(fā)生變化，這一點(diǎn)也比較有意思，就不展開(kāi)敘述了。

這一點(diǎn)跟繞口令一樣，這里要求對(duì)SQL Server中的聚集索引和非聚集索引，以及存儲(chǔ)結(jié)構(gòu)有一個(gè)基礎(chǔ)的認(rèn)識(shí)才容易理解。

最后高能預(yù)警

高能預(yù)警，別說(shuō)我瞎比比誤導(dǎo)人，上述解析偽列的函數(shù)sys.fn_PhysLocFormatter是一個(gè)未公開(kāi)的函數(shù)，未公開(kāi)的函數(shù)就有可能潛在一些問(wèn)題，事實(shí)上這個(gè)函數(shù)有一個(gè)非常嚴(yán)重的bug。

該bug就是在解析物理存儲(chǔ)位置的時(shí)候有一定的邏輯錯(cuò)誤，這個(gè)問(wèn)題早有細(xì)心的人分析過(guò)了

目前測(cè)試來(lái)看，在SQL Server 2014中仍然存在bug，N前年啃書(shū)的時(shí)候就了解到有這么一個(gè)函數(shù)，但是一直不想提及sys.fn_PhysLocFormatter這個(gè)函數(shù)的原因，因此對(duì)于未公開(kāi)的函數(shù)，請(qǐng)不要做驗(yàn)證性測(cè)試，再次聲明：該函數(shù)有bug，請(qǐng)謹(jǐn)慎使用。

附上這個(gè)函數(shù)的源代碼，并參考原文的結(jié)論

create function sys.fn_PhysLocFormatter (@physical_locator binary (8)) returns varchar (128)as begin declare @page_id binary (4) declare @file_id binary (2) declare @slot_id binary (2) -- Page ID is the first four bytes, then 2 bytes of page ID, then 2 bytes of slot -- select @page_id = convert (binary (4), reverse (substring (@physical_locator, 1, 4))) select @file_id = convert (binary (2), reverse (substring (@physical_locator, 5, 2))) select @slot_id = convert (binary (2), reverse (substring (@physical_locator, 7, 2))) return '(' + cast (cast (@file_id as int) as varchar) + ':' + cast (cast (@page_id as int) as varchar) + ':' + cast (cast (@slot_id as int) as varchar) + ')' end

問(wèn)題出在reverse函數(shù)上。

reverse函數(shù)的作用是字符反轉(zhuǎn)，而不是字節(jié)反轉(zhuǎn)，當(dāng)遇到81-FE之間的字節(jié)時(shí)，被認(rèn)為是雙字節(jié)字符而組合在一起參與反轉(zhuǎn)操作，造成了錯(cuò)誤。

總結(jié)

本文簡(jiǎn)單闡述了SQL Server中的偽列，以及偽列的含義，通過(guò)偽列對(duì)非聚集索引以及數(shù)據(jù)行的存儲(chǔ)結(jié)構(gòu)有一個(gè)簡(jiǎn)單的了解。

好了，以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)VeVb武林網(wǎng)的支持。