There are a some minor differences between COALESCE() and ISNULL():
* COALESCE() is ANSI standard, so that is an advantage for the purists out there.
* Many consider ISNULL()'s readability and common sense naming to be an advantage. While I will agree that it easier to spell and pronounce, I disagree that its naming is intuitive. In other languages such as VB/VBA/VBScript, ISNULL() accepts a single input and returns a single boolean output.
* ISNULL() accepts exactly two parameters. If you want to take the first non-NULL among more than two values, you will need to nest your ISNULL() statements. COALESCE(), on the other hand, can take multiple inputs:
SELECT ISNULL(NULL, NULL, 'foo')
Server: Msg 174, Level 15, State 1, Line 1
The isnull function requires 2 arguments.
SELECT COALESCE(NULL, NULL, 'foo')
In order to make this work with ISNULL(), you would have to say:
SELECT ISNULL(NULL, ISNULL(NULL, 'foo'))
* The result of ISNULL() always takes on the datatype of the first parameter (regardless of whether it is NULL or NOT NULL). COALESCE works more like a CASE expression, which returns a single datatype depending on precendence and accommodating all possible outcomes. For example:
DECLARE @foo VARCHAR(5)
SET @foo = NULL
SELECT ISNULL(@foo, '123456789')
SELECT COALESCE(@foo, '123456789')
This gets more complicated if you start mixing incompatible datatypes, e.g.:
DECLARE @foo VARCHAR(5), @bar INT
SET @foo = 'foo'
SET @bar = NULL
SELECT ISNULL(@foo, @bar)
SELECT COALESCE(@foo, @bar)
Server: Msg 245, Level 16, State 1, Line 6
Syntax error converting the varchar value 'foo' to a column of data type int.
* A relatively scarce difference is the ability to apply constraints to computed columns that use COALESCE() or ISNULL(). SQL Server views a column created by COALESCE() as nullable, whereas one using ISNULL() is not. So:
CREATE TABLE dbo.Try
col2 AS COALESCE(col1, 0)
Server: Msg 8111, Level 16, State 2, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'Try'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
Whereas the following works successfully:
CREATE TABLE dbo.Try
col2 AS ISNULL(col1, 0)
* Finally, COALESCE() can generate a less efficient plan in some situations.
Things borrower must know about Pre Closing Home loan - Most people tend to take a home loan for 15 to 20 years as this usually offers the lowest home loan interest rates. Now to prepay the loan, you have to pa...