1

I wanted to know the command to find the size of a row in a table in my database. Say I've a database db and a table table.

How can i find the size of a row in that database (which includes all the columns)?

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
Invictus
  • 4,028
  • 10
  • 50
  • 80

2 Answers2

2

For Displaying the expected row size for a table See here

Java
  • 2,451
  • 10
  • 48
  • 85
  • select object_name(id), exp_rowsize from sysindexes where id > 100 and (indid = 0 or indid = 1) is `id` here one of the coloumn in that table , sysindexed is `table` What does object_name and exp_rowsize represent ? – Invictus Feb 27 '12 at 12:20
  • select object_name(id), exp_rowsize from sysindexes where id > 100 and (indid = 0 or indid = 1) is id here one of the coloumn in that table , sysindexed is table What does object_name and exp_rowsize represent ? – Invictus Feb 27 '12 at 12:21
0

In ASE, I use a combination of sp_estspace and sp_spaceused for total Data / index sizing that I wrote myself. You can grab the source for each inside sybsystemprocs.

sp_estspace tends to overestimate (by a lot) the size of the data (not indexes) and sp_spaceused divided by rowcount tends to not be a good indicator of how big a row could potentially be today (imagine you added 20 nullable columns yesterday and decide to use them, spaceused is unchanged).

  1. reasonable expected data size = ((spaceused / rowcount) + estspace(1) ) / 2
  2. I haven't done any analysis about the accuracy of either index commands, but I would imagine (spaceused / rowcount) would be very accurate for forward looking items.

It's not perfect by ANY means, but it's been a fairly reliable estimate for my purpose. I wouldn't write any code that would break if it exceeded any of these estimates, though.

Hotel
  • 1,361
  • 11
  • 13