How to determine how much disc space a table is using in ASE

I have 开发者_JAVA百科a table which I think might be better off if it uses different data types for many of the columns. I wish to design some tests to determine the pay off in disc space if these columns are switched to better data types. How can I determine how much disc space a table is taking up in ASE 15.0?

1. Use sp_spaceused, table, 1. That reports the table and each index separately. DIviding the data space used by the rowtotal will give you one value for the actual row length (not counting fragmentation, which is based on the lock scheme and activity).

2. Use sp_help table_name. That will give you another value , the intended or average row length. Using the info provided, do the simple arithmetic with the column lengths; then estimate what they will be re the Datatype changes you intend.

3. Note the variable length columns require 4 additional bytes each.

   • If a column is Nullable, it is stored as Var Len.

4. Now create the new table (even temporarily), with the same columns, with the new Datatypes, and repeat (2). This will confirm your estimates.

5. sp_estspace has a different purpose.

1> sp_spaceused TableName
2> go
 name                 rowtotal    reserved        data            index_size      unused
 -------------------- ----------- --------------- --------------- --------------- ---------------
 TableName            5530288     5975116 KB      5537552 KB      392292 KB       45272 KB

I'm not aware of anything that will give you a breakdown by column though. Using sp_help against the table does give you a list of all the columns, and their Length. I think that indicates the amount of storage the column could use.

There are methods of estimating table size using sp_estspace, but I've never tried these.