Row Size Issue

Question

Row Size Issue

sqlquest2575

SSCommitted

Points: 1802
More actions
January 20, 2012 at 1:17 pm

#267000

Hello Friends,
I am trying to figure out the row size in my table. I need a size of 1 row so that i can plan the capacity. could you guys please help me with this?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Well, what's your table design? Can't help if you don't list the columns and data types.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlquest2575 SSCommitted Points: 1802 More actions · Answer 2

Thank you for your quick reply. Please find below the table Def

Addres ----- varchar

Data ------ bit

User ------ varchar

Create_DT ------ datetime

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Varchar what? Varchar(1)? Varchar(400)? Varchar(8000)? Varchar(Max)?

Varchar columns are variable length, so what's the average length of the data that will be stored in them?

Which columns are nullable?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlquest2575 SSCommitted Points: 1802 More actions · Answer 4

I am sorry ..i did not include the complete Table Def earlier ..Please find the info below

Addres --- varchar(30) --- Not Null

Data --- bit (1) --- Null

User ---varchar(30)--- Not Null

Create_DT datetime(8)---Not Null

Below is the Sp_spaceused info on the table

TableName ---Address

NumerofRows ----464

ReservedSize ----16KB

DataSize ---8KB

IndexSize------8KB

UnusedSpace------0KB

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

All right, so 2 variable-length columns, 4 columns in total, one nullable

Record size:

tag bytes - 2 bytes

null bitmap offset - 2 bytes

fixed length columns - 9 bytes (the bit takes a full byte)

null bitmap - 1 byte (for the 4 columns)

Variable length column array - 4 bytes (2 per variable length column)

Variable length columns - between 0 and 60 bytes

Plus there's the slot array at 2 bytes.

(that is a little simplified)

So the minimum size that one of your rows can be is 18 bytes and the maximum is 78. What you populate into the varchar columns will affect whether the row is closer to 18 or to 78.

Does that help?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlquest2575 SSCommitted Points: 1802 More actions · Answer 6

Thank you so much..that was really a great explaination