Thursday, December 18, 2008

Difference between ISNULL and COALESCE in SqlServer2005

COALESCE() and ISNULL() is more or less but the difference is

1)Coalesce() accepts more than 2 parameter and Isnull() accepts only two parameter

ex: Select ISNULL('test',null, 'test1')
Select COALESCE('test',null,'test1')

Results:
IsNull() returns error as The isnull function requires 2 argument(s).
COALESCE() returns test

2)COALESCE() Comapres with any type of datatype Where ISNUL() expression should be of same DataType

ex: Declare @test nvarchar(5)
Declare @test1 int
Set @test= null
Set @test1 =1234567890
Select Isnull(@test,@test1)as nullcolumn
Select COALESCE(@test,@test1,'testingvalue')as coalescecolumn

Results:
ISNULL() returns error as Arithmetic overflow error converting expression to data type nvarchar.
COALESCE() returns result as 1234567890

3)Eventhough if the ISNULL() is of same datatype it takes the first datatype.Let Me explain Clearly

ex: Declare @test nvarchar(5)
Declare @test1 nvarchar(10)
set @test = null
set @test1 = '0123456789'
Select Isnull(@test,@test1)as nullcolumn
Select COALESCE(@test,@test1,'testingvalue')as coalescecolumn

Results:
ISNULL() return as 01234
COALESCE() return as 0123456789

4)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.

CREATE TABLE dbo.Try
(
col1 INT,
col2 AS COALESCE(col1, 0)
PRIMARY KEY
)
GO

Results:
Cannot define PRIMARY KEY constraint on nullable column in table 'Try'. Could not create constraint. See previous errors.



No comments: