2

I'm figuring out the best way to estimate the size of a table, for which, I have gone through so much and unable to identify the theoretical space being used for a single row in bytes as the data is saved across pages which are at least 8KB per page and the ROW which we insert is stored across many pages.

Help me to calculate the theoretical space being used for below table

+-------------+--------------------+------+-----+---------+
| Field       | Type               | Null | Key | Default | 
+-------------+--------------------+------+-----+---------+
| Id          | int                | NO   | PRI |         |
| Idx         | [datetimeoffset](7)| NO   | PRI |         |
| Val1        | float              | YES  |     |         |
| Val2        | float              | YES  |     |         |
+-------------+--------------------+------+-----+---------+
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dani Mathew
  • 808
  • 10
  • 18
  • Does this answer your question? [Determine row size for table](https://stackoverflow.com/questions/496413/determine-row-size-for-table) . Note that "the exact size of a row" tends to not be as insightful as the table gets used, since page fragmentation and indexes will start playing a role quickly. Getting a ballpark figure by multiplying column lengths in bytes and adding the null bitmap tends to be "good enough" for purposes of estimating required space for X rows. – Jeroen Mostert Nov 26 '19 at 15:14
  • @JeroenMostert, rather than the actual disk usage, I would like to know the split up for the space utilized by each column, indexes, and null bitmap if any and etc. – Dani Mathew Nov 26 '19 at 16:04

1 Answers1

2

you may use the stored procedure sp_estimate_data_compression_savings This is a way diverted from the way it is used, but perhaps the easiest way to get a result when it comes to the size of an object on disk

use TEST

EXEC sp_estimate_data_compression_savings 'dbo','client',NULL,NULL,'row'

Result -->

object_name schema_name index_id    partition_number    size_with_current_compression_setting(KB)
client          dbo        0            1                             16 <-- Size of your table

As you asked 'calculate the theoretical space being used for below table' This is it

vincent PHILIPPE
  • 975
  • 11
  • 26