How does a Page in SQL Server distinguishes between the data of different columns of a table?

Question

I know that row offset array at the end of page stores information about the starting index of any row. Does it also store information about column data? And where does it store? If not, how does it identify the different column data?

Answer 1

Column meta-data are stored in system catalog tables. These are held separately from the physical record. The meta-data can be queried using the sys.columns catalog view. Records within a data page are a structure that includes a null bitmap, fixed-length fields, variable-length fields, and a column offset array entry for each variable length field present in the record. See Paul Randal's Anatomy of a Record for specifics of the internal data record structure. The physical record structure is significantly different when PAGE or ROW compression is used, for BLOBs (varchar(max) et al), and when a clustered columnstore index exists.

SQL Server uses the schema defined by the column meta-data to determine how to interpret the physical record structure and return the requested column values from the fields in the record. The physical record may or may not contain fields for each column, depending on whether the data type is fixed-length or variable, a NULL value, or columns added after initial table creation.

For example, if one adds a varchar NULL column to an existing table, that is a meta-data only operation that doesn't modify existing physical records. Similarly, adding a NOT NULL column with a default constraint is a meta-data only operation in Enterprise Edition because SQL Server uses meta-data to return the default value for existing rows even though the value is not physically present in records until later modified.

Answer 2

Records are stored in pages. They're stored in a FixedVar format, variable-length columns are stored at the end. The pages themselves are tied sysallocunits which is tied to sysrowsets which is tied sysschobjs which is tied to syscolpairs for schema.

Definitely check out DBCC_PAGE and for more information, check our

• Mark S. Rasmussen - Revealing the SQL Server Magic

All of the information was grabbed from that vid. Seen it a few times now. Haven't played with it yet.