Tuesday, June 30, 2009

VARCHAR(n) - Storage Size

When you consider whether to use CHAR or VARCHAR data type
you should consider the following:

1) The storage size of the VARCHAR is the actual length
of data entered + 2 bytes.Meaning it includes a two-byte binary
prefix that contains the effective length of the string.

For example: the storage size of VARCHAR(100) column
will be 102 bytes (in case you enter 100 characters into the column).

In large tables this can be significant in aspect of space.

So CHAR(1) NOT NULL takes less space than VARCHAR(1) NOT NULL.

2)If you define some column as CHAR(n) NULL and it does not
contain any data (just null) than the storage will be still n bytes.


3)A char column that defined as NULL is handled by SQL Server
as varchar,when SET ANSI_PADDING is OFF.


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

No comments:

Post a Comment