Analysis of pseudo-columns in SQL Server database and the meanings of pseudo-columns

Pseudo-Columns in SQL Server

QQ Group in the afternoon to see someone in the discussion (nonclustered) index storage,
Say, for a clustered index table, the nonclustered index stores the index key value + the clustered index key value; For a nonclustered index table, the index stores the index key value +rowid, which should be a common sense, which is not elaborated in detail.
The main point here is that the ROWID raised a little thought.
So, what is this rowid? Can you look at rowID's information more intuitively? What does it mean? This, of course, is also possible.
The concept of a pseudo-column in a table in Oracle is to query for a pseudo-column by adding select rowid,* from table when querying the table.
SQL Server also has such a pseudo-column, in SQL Server, this pseudo-column can be considered as the physical address of the data row, the following simple to observe the meaning of this rowid and rowID.

Pseudo-Column Testing

　　Build a simple table with the table below to see the description pseudo-column

 create  table   Test (id  int  identity  (1 , 1   varchar  (50   go  insert  into  Test values  (newid   ())  go  100  

There is an undisclosed pseudo-column "%%physloc%%" in SQL Server, that is, when querying, you can add this field to any table, such as the following, to find the pseudo-columns of each row in the table.

The type of this pseudo-column is binary (8), which is 8 bytes, referring to Datalength (%%physloc%%) as Len,
%%physloc%% returns the physical address of the record, where the first four bytes represent the page number, the middle two bytes represent the file number, and the last two bytes represent the slot number
To make it easier to observe the meaning of a pseudo-column, SQL Server provides an undisclosed system function, Sys.fn_physlocformatter, to continue observing the pseudo-column with the help of the Sys.fn_physlocformatter function.
For example, the information in the pseudo-column can be clearly seen here.

For example, in the first line of (1:73:0), which said that the first four bytes of the page number, the middle two bytes represents the file number, the last two bytes represents the slot number,
(1:73:0) This format is the result of an explicit sys.fn_physlocformatter format.
Put the file number 1 in the front, the middle 73 is the page number, and the last 0 is the slot (Sloc number).
The meanings of these fields are briefly explained below. Here the requirement for SQL Server storage is just a basic understanding, otherwise look at the foggy.

1, first say what is the file number

For example, the file number is the data file number of the database, there is only one data file, the file number is 1,
When the table is built by default (which can only be established here) is built on Fileid = 1 of the file above, fileid=2 is a log file, not much to say.

2, followed by the page number, the page number is assigned to the current table of the data page (8kb Minimum allocation unit) page number, we look at the page test this table situation

With the DBCC IND command, query the page information assigned to this table, where page 77th is the IMA also face, as to what the IMA page, not much explanation.
Page 73rd is really a page to store data, with the above 1:73:0 731, no problem.

　　

3, the last look at the slot number, the concept of slot number to the SQL Server data page has a basic understanding, here to steal a picture of a netizen.

The so-called slot number is in the data page, each page stores multiple rows of data, the slot number is used to mark the offset of each row of data, with plain English said is "the location of the address space to store each row of data",
Because the total length of each row of data is different (in the case of variable-length columns), each row occupies a different storage space,
The slot number or line offset is the starting position of each row of data within the page.
However, the Sys.fn_physlocformatter format explicit slot number is not the following offset, but the nth data row of this n information,
So the slot number on line 1th is 1, the slot number of line 2nd is 2, and so on, when the first page is full, storage starts from the second page, and the slot number is numbered from 0 and accumulates

　　

At this point, the Pseudo-column of SQL Server, it is often said that the ROWID has a simple understanding.
It can be thought that in the SQL Server database, the pseudo-column RowId is the physical address of the data row, and the Pseudo-column (RowId) in the other database is not the physical address but is not sure (most likely)

Here is a simple question to start with:
Why does the clustered table of SQL Server (a table with clustered indexes) store data as "index key value + clustered index key value", and for nonclustered index tables, the index stores the index key value +rowid?
Or, conversely, why nonclustered indexes on clustered index tables store "index key value + clustered index key value" instead of "Index stored index key value +rowid"
As a common sense, clustered indexes are stored in the order of clustered indexes, which means that the physical location of row data for a clustered index table can change, such as in a well-known page split,
When the physical location of the data row has changed, if the nonclustered index stores the index key value +rowid, then this RowId is bound to change, of course, this change will cost a certain amount of performance,
To prevent this, nonclustered indexes in the clustered table are stored as relative index key values + clustered index key values because the clustered index key value is relatively constant when the physical location of the data row changes, which is not difficult to understand.
Of course there is an exception, when updating the clustered index table, directly update the key value of the clustered index, it is also possible to cause the current data row in the clustered index table changes in the physical location, this is more interesting, do not expand the narrative.
This is like tongue twisters, where there is a requirement for clustered and nonclustered indexes in SQL Server, and for storage structures

Last high-energy warning

High-energy warning, don't say I'm blind. The function Sys.fn_physlocformatter of the above analytic pseudo-column is an undisclosed function.
A function that is not exposed may have some potential problems, in fact this function has a very serious bug.
This bug is to parse the physical storage location when there is a certain logic error, this problem has been carefully analyzed by the people
Reference: http://blog.itpub.net/81227/viewspace-751898/
The current test, in SQL Server still exists bug,n the year before the book to learn that there is such a function,
But I don't want to mention the reason why the function is Sys.fn_physlocformatter, so for the non-public function, do not do the validation test,
Again: This function has a bug, please use it carefully.

Summarize

This article simply describes the pseudo-columns in SQL Server, and the meaning of pseudo-columns, with a simple understanding of the nonclustered index and the storage structure of the data rows through pseudo-columns.

Analysis of pseudo-columns in SQL Server database and the meanings of pseudo-columns