-2

My table having 80K+ records. I need to calculate each and every row size. This sum of row size must be same as table size

B.Muthamizhselvi
  • 642
  • 4
  • 13
  • 1
    Do you need to calculate, or can you just use sp_spaceused? exec sp_spaceused [TableName] – David Brunning Jun 07 '19 at 13:11
  • 1
    Possible duplicate of [Determine row size for table](https://stackoverflow.com/questions/496413/determine-row-size-for-table) –  Jun 07 '19 at 13:11
  • @DarkoMartinovic I think that answer is how to get the maximum size for a row, rather than the actual size? Maybe https://dba.stackexchange.com/questions/25531/how-can-i-get-the-actual-data-size-per-row-in-a-sql-server-table – David Brunning Jun 07 '19 at 13:13

1 Answers1

4

There is very little relationship between the size of table and the size of a row. Tables sizes are really measured in numbers of pages that the table occupies (and perhaps associated indexes as well). Page sizes can be converted to bytes.

Record sizes (and you can look into [datalength()][1]) return sizes in bytes, which is a more typical measure of size on a computer.

Why are these not related very much? Here are three reasons I can readily think of:

  • Pages have over head needed by the database.
  • Pages may not be completely filled.
  • The size of the table should probably include associated index structures, but that is not part of the records.

In particular, you might have one 10-byte record on an 8k page. Or, you might have 800 of them. Both are valid representations of the data inside the database.

So, what you want to do does not make sense given how data is structured in a database.

Nimantha
  • 6,405
  • 6
  • 28
  • 69
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for your answer. I have tried to sum datalength() of column is equal to datasize of table using sp_spaceused. But I'm getting different size. – B.Muthamizhselvi Jun 11 '19 at 08:32