NULLIF - ISNULL - COALESCE

One thing which I always need more information is the NULL, because it is very tricky. Here is just a simple notes….

NULLIF (expr1, expr2)

Returns null value when both expression are equal; if not it returns the first expression

NULLIF is equivalent to the case statement.

Example 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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ISNULL (expr_check, expr_replace)

Replaces null value with the replacement value.

USE AdventureWorks;
GO
SELECT ProductID,(ISNULL(Weight, 10))
FROM Production.Product;
GO
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COALESCE (expr1...exprn)

Returns first non-null expression among its arguments


USE AdventureWorks;
GO
SELECT ProductID,(COALESCE(Weight, 10))
FROM Production.Product;
GO


Difference between ISNULL and COALESCE

ISNULL takes only 2 parameters. COALESCE can take any number of parameters.

ISNULL returns the datatype of first parameter whereas the return type of COALESCE is determined
by the data precedence rules; explicit casting should be done to get the desired data type.

For example the following two statements give different results

DECLARE @var varchar(2) -- Please use meaningful variable names in real code
SET @var = null
SELECT ISNULL( @var, 'Test' )
SELECT COALESCE( @var, 'Test' )

Checking fragmentation in SQL Server 2005

SQL Server 2005 provides sys.dm_db_index_physical_stats DMV (dynamic management view) which can be used to check index fragmentation.

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID('YourDatabaseName'), NULL, NULL, NULL , NULL);

If the avg_fragmentation_in_percent > 30 then Rebuild the index

If the avg_fragmentation_in_percent >5 and <30 then Reorganize the index

In a nutshell, rebuilding an index takes more server resources than reorganizing.