If I have an mssql varchar[1024] that is always empty in a table, how much actual bytes will be wasted?

Question

If I have an mssql varchar[1024] that is always empty in a table, how much actual bytes will be wasted in the db file on disk per row?

Please let me know the size for both:

1. NULLs allowed storing ''
2. NULLs not allowed storing ''
3. NULLs allowed storing NULL

Since the max varchar size is > 2^1 and < 2^3 I would assume 2 bytes for the length. But perhaps there is some more overhead, for example if there is a reference that points to the actual data.

Answer 1

I believe that a varchar uses only the minimum storage required to hold the string. MSDN and books online seem to confirm this. However, the sum of stored data (including other fields) cannot exceed a certain length (which I think is 8K). I don't think you've got this problem, as I think it is flagged at creation time.

more details are here: http://doc.ddart.net/mssql/sql70/ca-co_3.htm

Answer 2

Depends on whether or not "empty" means NULL or ''. In any rate, it's impossible to determine exactly without knowing the DDL of your table (are they all NOT NULL columns? Some null columns? What do you mean by empty?) but it's 2 bytes per row to calculate the length of the field offset for the next column. Nothing to worry about unless you have a few billion rows. Even then, it aint much.

Answer 3

In each row that allows nulls, there is a null bitmap object that controls whether a particular column's value is null or not. The size in bytes of this bitmap will be NumberOfNullableColumns/8, rounded up to the next whole byte Additionally, the overhead for the length of a varchar is 2 bytes.

With that in mind:

1. Nullable column storing an empty string = 2 bytes (plus 1 bit for the bitmap)
2. Non-nullable column storing an empty string = 2 bytes
3. Nullable column storing null = 0 bytes (plus 1 bit for the bitmap)