淺析SQL Server資料庫中的偽列以及偽列的含義

標籤：height   target   存在   ora   淺析   不能   sys   ace   ide   

 

SQL Server中的偽列

下午看QQ群有人在討論（非聚集）索引的儲存，
說，對於叢集索引表，非叢集索引儲存的是索引索引值+叢集索引索引值；對於非叢集索引表，索引儲存的是索引索引值+RowId，這應該是一個常識，對此不作具體詳細闡述。
這裡主要是提到的RowId引起了一點思考。
那麼，這個RowId是個什麼玩意？能不能更加直觀一點來看看RowId的資訊？代表什麼含義？這個當然也是可以的。
Oracle中的表中有一個偽列的概念，就是在查詢表的時候加上select rowid，* from Table，會查詢出來偽列。
SQL Server中同樣有這麼一個偽列，在SQL Server中，這個偽列的可以認為是資料行的物理地址，下面簡單來觀察一下這個RowId以及RowId的含義。

 

偽列的測試

　　建一張簡單的表，下面藉助這個表來查看說明偽列

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

SQL Server中有一個未公開的偽列“%%physloc%%”，也就是在查詢的時候，對於任何一張表，可以加上這個欄位，比如如下，就可以查到表中每一行的偽列。

這個偽列的類型是binary(8)，也就是有8個位元組，參考的DATALENGTH(%%physloc%%) as Len，
%%physloc%%返回的記錄的物理地址，其中前四個位元組表示頁號，中間兩個位元組表示檔案號，最後兩個位元組表示槽號
為了更加方便地觀察偽列的含義，sqlserver提供了一個未公開的系統函數sys.fn_PhysLocFormatter，下面藉助sys.fn_PhysLocFormatter這個函數來繼續觀察這個偽列
如，這裡就可以清晰地看到偽列中的資訊了。

　　比如第一行中的(1:73:0)，上面說了，其中前四個位元組表示頁號，中間兩個位元組表示檔案號，最後兩個位元組表示槽號，
　　(1:73:0)這種格式是經過sys.fn_PhysLocFormatter格式化顯式之後的結果。
　　把檔案號1放在最前面，中間的73是頁號（page number），最後一位0是槽號（sloc number）。
　　下面粗略地說一下這幾個欄位的含義。這裡要求對SQL Server的儲存只是有一個基本的認識，否則看的雲裡霧裡。

 

　　1，首先說什麼是檔案號

　　如，檔案號就是資料庫的資料檔案編號，這裡只有一個資料檔案，檔案編號為1，
　　建表的時候預設（這裡也只能建立）建立在fileid = 1 的檔案上面，fileid=2的是記錄檔，就不多說了。

　　2，其次是頁號，頁號就是分配給當前這張表的資料頁面（8kb的最小配置單位）的頁號，我們看一下Test這個表的頁面情況

　　藉助DBCC IND命令，查詢分配給這個表的頁面資訊，其中77號頁面是IMA也面，至於什麼事IMA頁面，不多解釋。
　　73號頁面才是真正儲存資料的頁，與上面的1:73:0中的73一樣，沒毛病。

　　

　　3，最後看一下槽號，槽號的概念要對SQL Server的資料頁面有一個基本的認識，這裡盜用一張網友的圖。

　　所謂的槽號就是在資料頁面中，每個頁面儲存多行資料，槽號用來標記每一行資料的位移量，用大白話說就是“儲存每一行資料的地址空間開始的位置”，
　　因為每一行資料的總長度是不一樣的（存在可變長度列的情況下），每一行的佔用的儲存空間也是不一樣的，
　　槽號或者行位移量就是說明每一行資料在頁內的開始位置。
　　不過sys.fn_PhysLocFormatter格式化顯式的槽號並不是如下的位移量，而是第N個資料行的這個N的資訊，
　　因此第1行的槽號就是1，第2行的槽號就是2，以此類推，當第一個page儲存滿之後，從第二個page開始儲存，槽號又從0開始編號且累加

　　

 　　

　　至此，對SQL Server的偽列，也就說經常說的RowId有了一個簡單的認識。
　　這裡可以認為，在SQL Server資料庫中，偽列RowId就是資料行的物理地址，至於別的資料庫中的偽列（RowId）是不是物理地址倒是不確定（很有可能也是的）

這裡簡單提一下一開始說的一個問題：
為什麼SQL Server的聚集表（有叢集索引的表）儲存資料的時候儲存的是“索引索引值+叢集索引索引值”,對於非叢集索引表，索引儲存的是索引索引值+RowId？
或者反過來說，為什麼叢集索引表的非叢集索引儲存的是“索引索引值+叢集索引索引值”而不是“索引儲存的是索引索引值+RowId”
作為一個常識，叢集索引要按照叢集索引的順序存放，這就意味著叢集索引表的行資料物理位置有可能發生變化，比如在眾所周知的“頁面分割（page split）”中發生變化，
在資料行的物理位置發生了變化的時候，如果非叢集索引儲存的是索引索引值+RowId，那麼這個RowId也勢必要發生變化，這個變化當然要耗費一定的效能，
為了防止此種情況的發生，聚集表中的非叢集索引儲存成相對不變的索引索引值+叢集索引索引值，因為在資料行的物理位置發生變化的時候，叢集索引索引值是相對不變的，這一點也不難理解。
當然有一種例外，當對叢集索引表做更新的時候，直接更新叢集索引的索引值，這樣的話，也有可能造成叢集索引表中當前資料行的物理位置發生變化，這一點也比較有意思，就不展開敘述了。
這一點跟繞口令一樣，這裡要求對SQL Server中的叢集索引和非叢集索引，以及儲存結構有一個

 

最後高能預警

　　高能預警，別說我瞎比比誤導人，上述解析偽列的函數sys.fn_PhysLocFormatter是一個未公開的函數，
　　未公開的函數就有可能潛在一些問題，事實上這個函數有一個非常嚴重的bug。
　　該bug就是在解析實體儲存體位置的時候有一定的邏輯錯誤，這個問題早有細心的人分析過了
　　參考：http://blog.itpub.net/81227/viewspace-751898/
　　目前測試來看，在SQL Server中仍然存在bug，N前年啃書的時候就瞭解到有這麼一個函數，
　　但是一直不想提及sys.fn_PhysLocFormatter這個函數的原因，因此對於未公開的函數，請不要做驗證性測試，
　　再次聲明：該函數有bug，請謹慎使用。

 

總結

　　本文簡單闡述了SQL Server中的偽列，以及偽列的含義，通過偽列對非叢集索引以及資料行的儲存結構有一個簡單的瞭解。

 

淺析SQL Server資料庫中的偽列以及偽列的含義