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?
Sql-server – How does a Page in SQL Server distinguishes between the data of different columns of a table
data-pagessql server
Related Question
- Sql-server – How does the transaction log record a transaction
- SQL Server – Why 512 Bytes Are Not Used from 8 KByte Data Page
- Sql-server – Data from different tables into one table
- Data Page Level Changes When Record Length Changes in SQL Server
- Sql-server – How does a integer data record store in SQL Server page
- Innodb – Can an InnoDB page contain data from different indexes
- Sql-server – Snapshot and RCSI.When does SQL Server go to VersionStore
- DBMS vs OS – Does ‘Page’ Have the Same Meaning?
Best Answer
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.columnscatalog 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.