I'm watching Paul Randal's MCM video from http://technet.microsoft.com/en-us/sqlserver/gg313756.aspx
From 27:11 of the video, the author said a record must be saved in one page, you can't have half of a record on one page and half of it on another page. This opinion is not what I learned. However I'm not confident about it, since I'm not an English native speaker and may misunderstood Paul's opinion. Anyone can help to explain a little?
Thanks in advance.
You are splitting hairs over semantics. A physical record cannot span pages. A physical record has to fit in a page.
But a index (or heap) row consists potentially from more than one physical records. An index row will consist from exactly one record in the IN_ROW_DATA allocation unit and zero, one or more records in ROW_OVEFLOW_DATA and/or LOB_DATA. The IN_ROW_DATA record contains pointers to overflow or LOB records. These off-row records themselves may contain more pointers to form a chain or a tree (this is how a VARCHAR(MAX) column can have values that are obviously much larger than 8060 bytes). Such a large value will consist from many individual records, each of 8060 bytes or less. None of these record will ever span multiple pages.
And I did not even touch columnstore index rows, which are completely different.
I think I found the answer. The following paragraph is from SQL Server documentation. Note the first sentence, "Rows cannot span pages, however portions of the row may be moved off the row's page so that the row can actually be very large". What I think is that if portions of the row may be moved off the row's page, it already means the row spanned pages, right?
Rows cannot span pages, however portions of the row may be moved off the row's page so that the row can actually be very large. The maximum amount of data and overhead that is contained in a single row on a page is 8,060 bytes (8 KB). However, this does not include the data stored in the Text/Image page type. This restriction is relaxed for tables that contain varchar, nvarchar, varbinary, or sql_variant columns. When the total row size of all fixed and variable columns in a table exceeds the 8,060 byte limitation, SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit, starting with the column with the largest width. This is done whenever an insert or update operation increases the total size of the row beyond the 8060 byte limit. When a column is moved to a page in the ROW_OVERFLOW_DATA allocation unit, a 24-byte pointer on the original page in the IN_ROW_DATA allocation unit is maintained. If a subsequent operation reduces the row size, SQL Server dynamically moves the columns back to the original data page. For more information, see Row-Overflow Data Exceeding 8 KB.