CODEWITHSUNDEEP
sql-serverdatabase-design
marcob
marcob

Reputation: 1013

Calculate storage space used by sql server sql_variant data type to store fixed length data types

I'm trying to calculate the storage space used by sql_variant to store fixed length data types.

For my test I created a table with two columns:

  Key int identitiy(1,1) primary key
  Value sql_variant

I added one row with Value 1 of type int and I used DBCC PAGE to check the size of the row, that turned out being 21 bytes.

Using Estimate the Size of a Clustered Index I have:

  Null_bitmap = 3
  Fixed_Data_Size = 4 (Key column int)
  Variable_Data_Size = 2 + 2 + 4 (Value column with an int)

  Row_Size = 4 + 8 + 3 + 4 = 19 bytes

Why does the row take 21 bytes? What am I missing in my calculation?

I tried the same analysis with a table using an int column instead of the sql_variant and the used byte count reported by DBCC PAGE is 15, which match my calculation:

  Null_bitmap = 3
  Fixed_Data_Size = 8 (Key column int, Value column int)
  Variable_Data_Size = 0

  Row_Size = 4 + 8 + 3 = 15 bytes

Upvotes: 0

Views: 1287

Answers (2)

Jonathan Kehayias
Jonathan Kehayias

Reputation: 3412

The extra space is the sql_variant metadata information. From the BOL:

http://msdn.microsoft.com/en-us/library/ms173829.aspx

*Each instance of a sql_variant column records the data value and the metadata information. This includes the base data type, maximum size, scale, precision, and collation.

For compatibility with other data types, the catalog objects, such as the DATALENGTH function, that report the length of sql_variant objects report the length of the data. The length of the metadata that is contained in a sql_variant object is not returned.*

Upvotes: 1

Robert McKee
Robert McKee

Reputation: 21487

You missed part 7.

7 . Calculate the number of rows per page (8096 free bytes per page):

Rows_Per_Page = 8096 / (Row_Size + 2)

Because rows do not span pages, the number of rows per page should be rounded down to the nearest whole row. The value 2 in the formula is for the row's entry in the slot array of the page.

Upvotes: 1

Related Questions