Comparison of NULLIF and ISNULL Sql Server

NULLIF

Returns a null value if the two specified expressions are equal.
Syntax :
NULLIF ( expression , expression )
Arguments :
expression :Is any valid scalar expression.

ISNULL

Replaces NULL with the specified replacement value.
Syntax :
ISNULL ( check_expression , replacement_value )
Arguments :
check_expression : Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value : Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.

Comparison

[sql]
DECLARE
@budgets TABLE
(
current_year DECIMAL NULL
,previous_year DECIMAL NULL
)
INSERT INTO @budgets
VALUES
(NULL
,40
)
INSERT INTO @budgets
VALUES
(50
,50
)
INSERT INTO @budgets
VALUES
(50
,60
)
INSERT INTO @budgets
VALUES
(50
,70
)

SELECT
NULLIF(current_year,previous_year) AS 'Average Budget'
,isnull(current_year,0)
FROM @budgets
[/sql]

Exemple 2
[sql]
SELECT
NULLIF(doc_totalitem,0)
,ISNULL(doc_discvalue * (trans_total / NULLIF(doc_totalitem,0)),0)
FROM Sales.[Invoice.Header] a
INNER JOIN Sales.[Invoice.Detail] b ON a.doc_id = b.doc_id
[/sql]