What is the cost in bytes for the overhead of a sql_variant column in SQL Server?

Question

I have a table, which contains many columns of float data type with 15 digit precision. Each column consumes 8 bytes of storage. Most of the time the data does not require this amount of precision and could be stored as a real data type. In many cases the value can be 0, in which case I could get away with storing a single byte.

My goal here is to optimize space storage requirements, which is an issue I am facing working with a SQL Express 4GB database size limit.

If byte, real and float data types are stored in a sql_variant column there is obviously some overhead involved in storing these values. What is the cost of this overhead?

I would then need to evaluate whether I would actually end up in significant space savings (or not) switching to using sql_variant column data types.

Thanks, Elan

Answer 1

You won't save space using sql_variant and you'll simply introduce a whole level of complexity

Each column should have the correct datatype to store all possible values in this field. Do you need float across the board? Could you use tinyint? etc etc

If you're concerned about the 4GB limit then upgrade to the new Express versions (10GB) or move up an edition.

Answer 2

I found the folowing article very useful in shedding light on the cost of storage as far as sql_variant is concerned:

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