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
No comments:
Post a Comment