Geek Answers Handbook

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

If I have mssql varchar [1024] that is always empty in the table, how many actual bytes will be wasted in the db file on disk in line?

Please let me know the size for both:

  • NULL allowed to store ''
    • NULL is not allowed to store. ''
    • NULL allowed to store NULL

Since the maximum varchar size is> 2 ^ 1 and <2 ^ 3 I would assume 2 bytes for the length. But there may be a few more overheads, for example, if there is a link that points to the actual data.

+3
source share
3 answers

I believe varchar uses only the minimal storage needed to store the string. MSDN and online books seem to confirm this. However, the amount of stored data (including other fields) cannot exceed a certain length (which, I think, is 8K). I don’t think you have this problem, because I think it is flagged at creation time.

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

+6
source

On every row that allows null, there is a null bitmap object that determines whether a particular column value is null or not. The byte size of this bitmap will be NumberOfNullableColumns / 8, rounded to the next integer byte. In addition, the overhead for the varchar length is 2 bytes.

With that in mind:

  • Nullable column preserving an empty row = 2 bytes (plus 1 bit for a bitmap)
  • A column with a null value preserving an empty row = 2 bytes
  • Zero column storing zero = 0 bytes (plus 1 bit for a bitmap)
+3
source

Depending on whether "null" means "NULL" or "". In any case, it is impossible to determine without knowing the DDL of your table (are they NOT NOT NULL columns? Are some null columns? What do you mean by empty columns?), But these are 2 bytes per row to calculate the field offset length for the next column. Nothing to worry about if you don't have a few billion rows. Even then it is not.

+1
source

More articles:


All Articles