SQL Server – NULL

There is a bit of inconsistency in the way SQL Server treats (read it as implements) NULLs. Following are some noteworthy points related to implementation and usage of NULL values in SQL Server:

  • 3 possible values of logical expression
    • TRUE
    • FALSE
    • UNKNOWN
  • UNKNOWN occurs when a logical expression involves NULL
    • NULL > 2 is UNKNOWN
    • NULL = NULL is UNKNOWN
    • X + NULL is UNKNOWN
    • (NOT (UNKNOWN)) IS UNKNOWN
  • Query filters (ON, WHERE, HAVING) treat UNKNOWN as FALSE
  • CHECK constraint treat UNKNOWN as TRUE
  • During comparison NULL = NULL is treated as not equal
  • UNIQUE constraint, UNION, EXCEPT, SORTING and GROUPING treats NULLs as equal

This information is quite handy when you are writing TSQL queries targeting data with NULL values.

Cheers!
Currently Playing – Not Afraid by Eminem

Technorati Tags: ,,

del.icio.us Tags: ,,
Advertisements

2 comments

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