Question :
I am trying to understand more about how data is allocated to the 8KB data page:
- Can the same page be shared with rows from other databases?
- Can the same page be shared with rows from other tables?
Answer :
1 – No
2 – No
The Microsoft documentation on pages and extents is very comprehensive
https://docs.microsoft.com/en-us/sql/relational-databases/pages-and-extents-architecture-guide?view=sql-server-ver15
1: No
2: No
A page is owned by an allocation unit:
-
You have the actual data in the table, which can be as a heap or a clustered index.
-
You have x number of non-clustered indexes.
-
Both 1 and 2 above can have LOB columns with off-row data (varbinary(max), for
instance). -
Both 1 and 2 above can of row overflow data, where for instance 2 varchar(8000) columns fully populated won’t fit on a page meaning one or more columns are stored off-row. Similar to 3 above, but for a limited variable length data type instead of max types.
-
All above (the ones that you have) has their own allocation units. They do not share pages.
-
Furthermore, 1 and 2 above can be partitioned, in that case you would multiply 1-4 above with home many partitions you have – giving you your allocation units. again, data isn’t page-shared across allocation units.