FIXED length character storage and NULL in SQL Server.

Most of us must be aware of CHAR datatype and its fixed length storage pattern.
If we declare CHAR(32) and store only 4 bytes character ‘ABCD’, It will pads up remaining bytes (28 bytes).
Ultimately, whole bytes is consume for serving only 4 length data.

lets understand it better with field work.

CREATE TABLE TESTCHAR
(
COL1 CHAR(32) NULL
);

INSERT INTO TESTCHAR VALUES ('ABCD');
SELECT COL1 , DATALENGTH(COL1) AS SIZE FROM TESTCHAR

COL1 SIZE
------ -----------
ABCD 32

but what happen for NULL data, will it still consume allocated bytes of 32 or follow some other storage pattern.

lets create tables with single column having CHAR and VARCHAR datatype respectively.

DROP TABLE TESTCHAR
CREATE TABLE TESTCHAR
(
COL1 CHAR(32) NULL
);

CREATE TABLE TESTVARCHAR
(
COL1 VARCHAR(32) NULL
);

Now let insert 1,00,000 null records in both the tables.

;WITH ALIAS1(COL1,COL2)
AS
(SELECT NULL COL1 , 1 COL2
UNION ALL
SELECT NULL , COL2+1 FROM ALIAS1 WHERE COL2 <=100000)
INSERT INTO TESTCHAR
SELECT COL1 FROM ALIAS1 Option (maxrecursion 0)

;WITH ALIAS1(COL1,COL2)
AS
(SELECT NULL COL1 , 1 COL2
UNION ALL
SELECT NULL , COL2+1 FROM ALIAS1 WHERE COL2 <=100000)
INSERT INTO TESTVARCHAR
SELECT COL1 FROM ALIAS1 Option (maxrecursion 0)

lets check size for a column having null as data for both tables.

SELECT TOP 1 COL1 , DATALENGTH(COL1) AS SIZE FROM TESTCHAR;
COL1 SIZE
------- -----------
NULL NULL

SELECT TOP 1 EVENT_GROUP_ID AS COL1 , DATALENGTH(EVENT_GROUP_ID) AS SIZE FROM TESTVARCHAR;
COL1 SIZE
-------- -----------
NULL NULL

DATALENGTH funtion does not shows any bytes for NULL storage for both datatype CHAR and VARCHAR.
Lets check table size for both data type using sp_spaceused.

exec sp_spaceused 'TESTCHAR'
exec sp_spaceused 'TESTVARCHAR'

name rows reserved data index_size unused
------------- -------- ---------- -------- ----------- -------
TESTCHAR 100001 4104 KB 4064 KB 8 KB 32 KB

name rows reserved data index_size unused
------------- -------- ---------- -------- ----------- -------
TESTVARCHAR 100001 1096 KB 1088 KB 8 KB 0 KB

clearly table size for only char datatype having null is almost 4 times more then table size having only varchar for null data.
DATALENGTH function was not able to incept same at row level, but definitely overall table size for CHAR is more then VARCHAR.

So, having column with datatype as CHAR and having most of data as NULL, will impose more storage then VARCHAR datatype.
NULL in char consume more disk then NULL in varchar datatype.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in SQL Server Learning and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s