SQL Server :: How many 8K pages are taken by this record?

I would like to see how many 8K pages are taken by a specific record.

If you run this query on AdventureWorks2019:

SELECT ProductID
    ,Name
    ,DATALENGTH(Name) AS SizeInBytes
    ,LEN(Name) AS NumberOfCharacters
FROM Production.Product
ORDER BY 1

You cna have a list of bites taken by each record:

ProductID
Name
SizeInBytes
NumberOfCharacters

1
Adjustable Race
30
15

2
Bearing Ball
24
12

3
BB Ball Bearing
30
15

4
Headset Ball Bearings
42
21

316
Blade
10
5

How can I add a column with the number of pages taken by that record?

Of course I can do DATALENGTH(Name) / 8 AS PagesTaken but I wonder if there is some command that allows me to find the number of pages through a SELECT.

I think you have something backwards here. A page is 8 KB (8,192 bytes). The rows in that table have a maximum size of the sum of the data type sizes of all of the columns:

I'm not actually going to add all of those up, but let's imagine the sum is 500 bytes.

You can look up the size of each data type by going through this page in the docs, and clicking through to each type you want to find size information on: Data types (Transact-SQL)

Because of a fixed size header on each page, and other per-row overhead, the amount of space available for rows on an 8KB page is 8,096 (see here for details: What Other Information Is Stored in The Page Header).

That means that, in this table, you could have up to 8096 / 500 = 16 rows per page. It might be possible to fit slightly more rows than that, depending on the size of the variable length (nvarchar) columns.

You can see how many pages are in a specific index by using a query like this: SQL Server: Number of 8K Pages Used by a Table and/or Database

From there you could derive an average of how many rows per page you're getting (total rows in the table / total pages in the table).

You can also use DBCC PAGE (as described in the "page header" link above) to view detailed information about each page, including how many rows there are on the page.

Note that all of the above applies to "in row data." If the table has LOB data types, then those will be stored off-row, potentially on multiple pages, if their size causes the record to exceed the maximum record size of 8,060 bytes.

StackExchange Database Administrators Q#307162, answer score: 12