Category Archives: SQL NULL

What’s in a NULL ?

Let’s take a Sneak Peek inside SQL Server 2012 (this hasn’t changed in versions 2008 & 2005 as well) & find out How SQL Server stores a NULL Value!

 

Step 1: Create a Table

CREATE TABLE TestforNULLs

(Id int null)

Step 2: Insert the below values

insert into TestforNULLs
values
(NULL),
(”),
(1)

Step 3: Verify to see what you have inserted

select * from TestforNULLs

NULL_1

Hmmm… We’ve already found out that SQL treats ‘’ {empty value inserted as ‘0’ (zero)}

Step 4: Next enable a SQL Trace for the current session

DBCC TRACEON(3604)

GO

Output:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Step 5: Verify as to what traces have been enabled

DBCC TRACESTATUS

Go

Output:

NULL_2

Step 6: Next, find out where your Data pages are located:

DBCC IND(testbed, TestforNULLs, -1)

GO

NULL_3

Step 7: Copy the First Page ID, here it is ‘328’ & paste it in the below command:

This command will help you see what’s in a SQL Data page.

DBCC PAGE (testbed, 1, 328, 1)

GO

NULL_4

Step 8: Scroll down to the “DATA” section below:

NULL_5

  • Notice that the NULL Bitmap Value has been enabled for the NULL Value with Binary 1 indicating that the first & the only column contains a NULL value.
  • For the Second and Third records which are NOT NULLs, the NULL Bitmap Value is 0.
  • This is how SQL Server identifies a NULL Value.

Hope this was useful piece of Information for you & answers the questions such as –

“How does SQL Server store NULL values?” 

“What’s in a NULL, bro?”

“hmmm… NULL huh?”

 

Key Learning:

  1.  SQL Server marks NULL Values via its NULL BITMAP
  2. SQL Server stores Integer value 0 (zero) when you pass an empty value
  3. Empty value is NOT equal to NULL Value

 

Related Reading:

Deep Dive on to Paul Randall’s article on Misconceptions around null bitmap size

 

 

 

Advertisements

3 Comments

Filed under SQL NULL, sql server 2005, sql server 2008, sql server 2012