There is a myth bound to COALESCE function that it is just a replacement of ISNULL. Many candidate in interview also agree to this statement that COALESCE is just replacement of ISNULL but most of them were not aware of advantage of using COALESCE in place of ISNULL.
So Lets See ISNULL VS COALESCE
Test 1 : Similar Functionality Of ISNULL and COALESCE
Use of ISNULL
Declare @Id1 Int
Declare @Id2 Int
Set @Id1=10
Select ISNULL(@Id2,@Id1) as Value
Returns 10 as Expected
Use of COLESCE
Declare @Id1 Int
Declare @Id2 Int
Set @Id1=10
Select COALESCE(@Id2,@Id1) as Value
Returns 10 as well.
So Its Proved That Both Provided Same Functionality
Test 2 : Mixing DataType
Use ISNULL with Mixed DataType
Declare @Var1 Int
Declare @Var2 DateTime
Set @Var2=GETDATE()
Select ISNULL(@Var1,@Var2) as Value
Output : Throws Error
Msg 257, Level 16, State 3, Line 4
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
Use COALESCE with Mixed DataType
Declare @Var1 Int
Declare @Var2 DateTime
Set @Var2=GETDATE()
Select ISNULL(@Var1,@Var2) as Value
Output : Return CurrentDate
So its clear that ISNULL only support checking of similar DataType whereas COALESCE can be used with different data type as you saw in example above.
Test 3 : Nested ISNULL VS NESTED COALESCE
To perform TEST 3 create table and populate data with following script.
Create Table NextedNull
(
Id Int Identity(1,1),
Slot1 Varchar(10),
Slot2 Varchar(10),
Slot3 Varchar(10),
Slot4 Varchar(10),
Slot5 Varchar(10)
)
Go
Insert Into NextedNull Values
(null,null,null,null,'Active'),
(null,'Active',null,null,null),
('Active',null,null,null,null),
(null,null,'Active',null,null),
(null,null,null,null,null)
Go
Select * From NextedNull
Go
Output :
Now the task is to check Active slot against each Id in this table and if any slot is active then result should be Active else it should return ‘No Active Slot’.
Using ISNULL
Select Id,ISNULL(Slot1,Isnull(Slot2,ISnull(Slot3,Isnull(Slot4,Isnull(Slot5,'No Active Slot'))))) IsActive
from NextedNull
Go
Ouptut :
lets try to achieve above task with COALESCE.
Select Id,COALESCE(Slot1,Slot2,Slot3,Slot4,Slot5,'No Active Slot') IsActive
From NextedNull
Go
Output :
So we have seen the flexibility and benefits of using COALESCE over ISNULL and we found that COALESCE provides more functionality then just replacing role of ISNULL.
No comments:
Post a Comment