Sql-server – store half of a record on one page and half of the record on another page

sql serversql-server-2005sql-server-2008

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.

Best Answer

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.

Related Question