A friend of mine approached me the other day and asked a great question: "When a NULL value is stored in a varchar data type, how much memory is allocated in its storage." My friend, being of the scientific mind that he is, then added "...and how can I verify it."
The slice of pepperoni pizza in my hand provided a great device to allow a pause to contemplate a response without giving the impression that I did not know the answer to his question. Unfortunately, the bite that I took was not sufficient because I was still working on my response when I was done chewing. I defaulted to the response of "Alex, that is a great question." and politely promised that I would research the answer and get back to him.
The following is the results of my research and the test I used to verify my answer:
The Sample Tables
For our test, we will create two simplified tables that contains a single column and three records. The difference between these tables is the data type that is used for our column:
-- Uses the CHAR data type
CREATE TABLE MyCharTest (testCol CHAR(10) NULL);
-- Uses the VARCHAR data type
CREATE TABLE MyVarCharTest (testCol VARCHAR(10) NULL);
The data that will be used in our test will be three values: "1234567890", "" and NULL. For later reference, we will make note of the physical sizes of these values through the DATALENGTH method. This provides us a measurement without the influence of the data type in which they are stored.
SELECT DATALENGTH('1234567890');
-- Returns a value of 10 bytes
SELECT DATALENGTH('');
-- Returns a value of 0 bytes
SELECT DATALENGTH(NULL);
-- Returns a value of NULL
The final preparation for our sample tables is to populate the with our values:
-- Inserts into MyCharTest table
INSERT INTO MyCharTest (testCol) VALUES ('1234567890');
INSERT INTO MyCharTest (testCol) VALUES ('');
INSERT INTO MyCharTest (testCol) VALUES (NULL);
GO
-- Inserts into MyVarCharTest table
INSERT INTO MyVarCharTest (testCol) VALUES ('1234567890');
INSERT INTO MyVarCharTest (testCol) VALUES ('');
INSERT INTO MyVarCharTest (testCol) VALUES (NULL);
GO
The Test Script
The following is a script to obtain a page dump of a given table. This script uses DBCC statements to obtain the page dump information. Prior to executing the script below, replace the @DB and @TBL variables with the name of your database (@DB) and table (@TBL) that you are evaluating. Also, set the output of the script to be presented in text (In the menu bar of Management Studio, click on: Query. Results To. Results To Text); otherwise the DBCC PAGE information will not be presented.
We will execute this script once for our MyCharTest table and a second time for our MyVarCharTest table:
USE MyDatabase;
-- variables to make the script flexible
DECLARE @DB varchar(50);
DECLARE @TBL varchar(50);
SET @DB = 'MyDatabase';
SET @TBL = 'MyCharTest'; --
-- enables the specified trace
-- 3604 is trace flag output is returned to the application
DBCC TRACEON(3604);
-- create table variable to capture DBCC IND results
DECLARE @Ind TABLE (
PageFID tinyint, PagePID int, IAMFID tinyint, IAMPID int,
ObjectID int, IndexID tinyint, PartitionNumber tinyint,
PartitionID bigint, iam_chain_type varchar(30), PageType tinyint,
IndexLevel tinyint, NextPageFID tinyint, NextPagePID int,
PrevPageFID tinyint, PrevPagePID int
);
-- execute DBCC IND and capture in @Ind temp table
INSERT INTO @Ind EXEC('DBCC IND(' + @DB + ', ' + @TBL + ', -1);');
-- snag the PagePID from the @Ind temp table
DECLARE @PagePID int;
SELECT @PagePID = PagePID
FROM @Ind
WHERE PageType = 1 and PrevPageFID = 0 and PrevPagePID = 0;
-- execute DBCC PAGE to see the memory dump
DBCC PAGE (@DB, 1, @PagePID, 3)
-- disables the specified trace.
DBCC TRACEOFF(3604);
GO
The CHAR Test
The first execution of the script above is against our MyCharTest sample table that contains a column data type of CHAR(10). The results were:
For the first row, containing the value "1234567890", the page dump appears as follows:
Slot 0, Offset 0x60, Length 17
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP
Record Size = 17
Memory Dump @0x6198C060
00000000: 10000e00 31323334 35363738 39300100 ?....1234567890..
00000010: 00???????????????????????????????????.
Slot 0 Column 1 Offset 0x4 Length 10 Length (physical) 10
For the second row, containing the value "", the page dump appears as follows:
Slot 1, Offset 0x71, Length 17
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP
Record Size = 17
Memory Dump @0x6198C071
00000000: 10000e00 20202020 20202020 20200100 ?.... ..
00000010: 00???????????????????????????????????.
Slot 1 Column 1 Offset 0x4 Length 10 Length (physical) 10
For the third row, containing the NULL value, the page dump appears as follows:
Slot 2, Offset 0x82, Length 17
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP
Record Size = 17
Memory Dump @0x6198C082
00000000: 10000e00 88e18308 00000000 00000100 ?.....á.........
00000010: 01???????????????????????????????????.
Slot 2 Column 1 Offset 0x0 Length 0 Length (physical) 0
The VARCHAR Test
The second execution of the script above is against our MyVarCharTest sample table that contains a column data type of VARCHAR(10). The results were:
For the first row, containing the value "1234567890", the page dump appears as follows:
Slot 0, Offset 0x60 Length 21
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 21
Memory Dump @0x61EEC060
00000000: 30000400 01000001 00150031 32333435 ?0..........12345
00000010: 36373839 30??????????????????????????67890
Slot 0 Column 1 Offset 0xb Length 10 Length (physical) 10
For the second row, containing the value "", the page dump appears as follows:
Slot 1, Offset 0x75, Length 9
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP
Record Size = 9
Memory Dump @0x61EEC075
00000000: 10000400 01000000 00?????????????????.........
Slot 1 Column 1 Offset 0x0 Length 0 Length (physical) 0
For the third row, containing the NULL value, the page dump appears as follows:
Slot 2, Offset 0x7e, Length 9
Record Type = PRIMARY_RECORD
Record Attributes = NULL_BITMAP
Record Size = 9
Memory Dump @0x61EEC07E
00000000: 10000400 01000108 00?????????????????.........
Slot 2 Column 1 Offset 0x0 Length 0 Length (physical) 0
The items highlighted in red represent the plain text values as well as the bytes allocated for its storage. The "record size" value in orange identifies the allocated storage space for the entire row (all columns plus metadata) in which the value is stored. In our case our row consists of only a single column. The "length (physical)" value, also in orange, identifies the physical length, in bytes, of the plain text as it is being stored.
The Evaluation
With the MyCharTest table we see that in all three rows the record size remains the same; while the MyVarCharTest table varies. This dynamic shows that the MyCharTest is allocating the full amount of physical storage for the value regardless of the physical length of the value being stored.
Another interesting observation was how the "" value was handled in these tests. In our setup for this test we evaluated the length of our values without the influence of the table column data type. The "" value returned 0 bytes in length in this evaluation. The page dump for the MyCharTest table shows that the physical length for the "" value is 10 bytes; while the MyVarCharTest table page dump shows the physical length for the "" value as 0 bytes. The physical length of the "1234567890" and NULL values did not vary in physical length. interesting.
The Answer
After all of the above, both aspects of my friend's question was finally answered.
"When a NULL value is stored in a varchar data type, how much memory is allocated in its storage.":
No memory is allocated for the storage of a NULL value. (Beyond the row's metadata)
"...and how can I verify it.":
Check out my blog.
... and now it's time for a second slice of pizza. ;D