Does SQL server share data pages?

Posted on

Question :

I am trying to understand more about how data is allocated to the 8KB data page:

  1. Can the same page be shared with rows from other databases?
  2. 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:

  1. You have the actual data in the table, which can be as a heap or a clustered index.

  2. You have x number of non-clustered indexes.

  3. Both 1 and 2 above can have LOB columns with off-row data (varbinary(max), for
    instance).

  4. 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.

  5. All above (the ones that you have) has their own allocation units. They do not share pages.

  6. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *