NOT NULL is integrity CONSTRAINT. It does not allow creating of the row where column contains NULL value. Most discussed question about NULL is what is NULL? I will not go in depth analysis it. Simply put NULL is unknown or missing data. When NULL is present in database columns, it can affect the integrity of the database. I really do not prefer NULL in database unless they are absolutely necessary. (Please make sure it is just my preference, and I use NULL it is absolutely needed).
To prevent nulls to be inserted in the database, table should have NOT NULL constraint. Two ways NOT NULL constraint can be implemented on database.
1) Implement Constraint when Table is created
2) Implement Constraint after Table is created
Following example demonstrates both the way to create NOT NULL constraints. USE AdventureWorks
GO -- NOT NULL Constraint when Table is created CREATE TABLE ConstraintTable (ID INT, ColSecond INT NOT NULL) GO -- NOT NULL Constraint after Table is created ALTER TABLE ConstraintTable ALTER COLUMN ID INT NOT NULL GO --Clean Up DROP TABLE ConstraintTable
Explanation of NULLIF Syntax:
NULLIF ( expression , expression )
Returns a null value if the two specified expressions are equal. NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression. NULLIF is equivalent to a searched CASE function in which the two expressions are equal and the resulting expression is NULL.
Following is good example of NULLIF and CASE from BOL: USE AdventureWorks; GO SELECT ProductID, MakeFlag, FinishedGoodsFlag, NULLIF(MakeFlag,FinishedGoodsFlag)AS 'Null if Equal' FROM Production.Product WHERE ProductID < 10; GO SELECT ProductID, MakeFlag, FinishedGoodsFlag,'Null if Equal' = CASE WHEN MakeFlag = FinishedGoodsFlag THEN NULL ELSE MakeFlag END
FROM Production.Product WHERE ProductID < 10; GO
Explanation of ISNULL Syntax: ISNULL ( check_expression , replacement_value )
Replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.
Following is good example of ISNULL from BOL: USE AdventureWorks; GO SELECT AVG(ISNULL(Weight, 50)) FROM Production.Product; GO
Interesting observation is NULLIF returns null if it comparison is successful, where as ISNULL returns not null if its comparison is successful. In one way they are opposite to each other.
How to create infinite loop using NULLIF and ISNULL? If this is even possible?
Simple but interesting – In recent survey I found that many developers making this generic mistake. I have seen following code in periodic code review. (The code below is not actual code, it is simple sample code) DECLARE @MyVar VARCHAR(10) SET @MyVar = NULL IF (LEN(@MyVar) = 0)
I decided to send following code to them. After running the following sample code it was clear that LEN of NULL values is not 0 (Zero) but it is NULL. Similarly, the result for DATALENGTH function is the same. DATALENGTH of NULL is NULL.
Sample Test Version: DECLARE @MyVar VARCHAR(10) SET @MyVar = NULL IF (LEN(@MyVar) = 0) PRINT 'LEN of NULL is 0' ELSE
PRINT 'LEN of NULL is NULL'
Result Set: LEN of NULL is NULL