What is the value of NULL in SQL Server ?

Question

Hi,

I know NULL is not zero... nor it is empty string. But then what is the value of NULL... which the system keeps, to identify it?

Answer 1

NULL is a special element of the SQL language that is neither equal to or unequal to any value in any data type.

As you said, NULL is not zero, an empty string, or false. I.e. false = NULL returns UNKNOWN.

Some people say NULL is not a value, it's a state. The state of having no value. Sort of like a Zen Koan. :-)

I don't know specifically how MS SQL Server stores it internally, but it doesn't matter, as long as they implement it according to the SQL standard.

Answer 2

I believe that for each column that allow nulls, the rows have a null bitmap. If the row in the specified column is null the bit in the bitmap is 1 otherwise is 0.

Answer 3

I doubt the interviewers wanted you to know exactly how SQL server stores nulls, the point of such a question is to get you to think about how you'd store special values. You can't use a sentinel value (or magic number), as that would make any rows with that value in them suddenly become null.

There are quite a few ways to achieve this. The most straightforward 2 that come to mind are to have a flag stored with each nullable value that is basically an isNull flag (this is also basically how Nullable<T> works in .NET). A 2nd method is to store with each row a bitmap of null flags, one for each column.

When faced with such an interview questions the absolute worst response is to sit and stare blankly. Think out loud some, admit that you don't know how SQL Server does it, and then present some reasonable sounding ways of doing it. You should also be ready to talk a bit about why you'd pick one method over another, and what the pluses and minuses of each are.

Answer 4

Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values.

In MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1.

Two NULL values are regarded as equal in a GROUP BY.

When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC.

Answer 5

The SQL Server row format is described in MSDN, and also analyzed on various blogs, like Paul Randal's Anatomy of a Record. The important information is the record structure:

• record header
  • 4 bytes long
  • two bytes of record metadata (record type)
  • two bytes pointing forward in the record to the NULL bitmap
• fixed length portion of the record, containing the columns storing data types that have fixed lengths (e.g. bigint, char(10), datetime)
• NULL bitmap
  
  • two bytes for count of columns in the record
  • variable number of bytes to store one bit per column in the record, regardless of whether the column is nullable or not
  • this allows an optimization when reading columns that are NULL
• variable-length column offset array
  • two bytes for the count of variable-length columns
  • two bytes per variable length column, giving the offset to the end of the column value
• versioning tag
  
  • a 14-byte structure that contains a timestamp plus a pointer into the version store in tempdb

So NULL fields have a bit set in the NULL bitmap.

Answer 6

As Bill said, it's a state not a value.

In a row in a table SQL Server it's stored in the null bitmap: no value is actually stored.

One quirk of SQL Server:

SELECT TOP 1 NULL AS foo INTO dbo.bar FROM sys.columns

What datatype is foo? It has no meaning of course, but it caught me out once.

Answer 7

The value of NULL means in essence a missing value, some people will also use the term unknow

A Null value is also not equal to anything not even another NULL

Take a look at these examples

will print is equal

IF 1 = 1
PRINT 'is equaL'
ELSE
PRINT 'NOT equal'

will print is equal (an implicit conversion happens)

IF 1 = '1'
PRINT 'is equaL'
ELSE
PRINT 'NOT equal'

will print is not equal

IF NULL = NULL
PRINT 'is equaL'
ELSE
PRINT 'NOT equal'