Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This blog incorporates articles about ASP.Net 3.5 and 4.0. Also good amount of concentration is laid on .net projects,book reviews,C# articles,jobs in present industry and some general topics.
Browse by Tags · View All
ARTICLE 61
SQL Server 31
TSQL 30
C# 16
ASP.Net 11
JumpStart 8
GENERAL 8
ASP.Net MVC 7
C# 4.0 6
ASP.Net 4.5 5

Archive · View All
December 2012 22
July 2012 12
May 2012 8
January 2013 7
April 2013 4
March 2013 4
February 2013 4
January 2012 4
August 2012 2
May 2013 1

Rami Vemula's Blog

Comparison of ISNULL and COALESCE in TSQL

Dec 27 2012 12:00AM by rami   

Check out the following sample code to understand the basic differences between ISNULL and COALESCE functions in SQL Server. Code is self explanatory and comments are provided for ease of understanding.

/****************************************************************************
 ISNULL function replaces NULL with the specified value. COALESCE function
 returns the first non-NULL value among its arguments.
****************************************************************************/
DECLARE @var VARCHAR(20)
SELECT ISNULL(@var, 'Rami Vemula') AS 'ISNULL';
SELECT COALESCE(@var, NULL, 2) AS 'COALESCE';

/****************************************************************************
 ISNULL takes only 2 parameters whereas COALESCE takes variable number of
 parameters.
****************************************************************************/
DECLARE @var1 INT
SELECT ISNULL(@var1, 2) AS 'ISNULL1';
SELECT COALESCE(@var1, NULL, 2) AS 'COALESCE1';

/****************************************************************************
ISNULL takes the data type length of the first argument, while COALESCE takes
all parameters and uses max preceded parameters's length.
****************************************************************************/
DECLARE @var2 VARCHAR(4)
SELECT ISNULL(@var2, 'Rami Vemula') AS 'ISNULL2';
SELECT COALESCE(@var2, 'Rami Vemula') AS 'COALESCE2';

/****************************************************************************
ISNULL uses the first parameter data type. COALESCE uses highest precedence
data type. ISNULL and COALESCE throws invalid cast exception when it encounters
invalid casts because of precedence.
****************************************************************************/
DECLARE @var3 INT
SELECT ISNULL(@var3, 0) AS 'ISNULL3';
-- SELECT ISNULL(@var3, 'Rami Vemula') AS 'ISNULL'; -- Throw Error
SELECT COALESCE(@var3, 0, GETDATE()) AS 'COALESCE3';
-- SELECT COALESCE(@var3, 'Rami Vemula', GETDATE()) AS 'COALESCE'; -- Throw Error

/****************************************************************************
If the data types of both parameters are not determined in ISNULL, the data
type returned is INT. In COALESCE at least one of the NULL values must be a
typed NULL or else it will throw error.
****************************************************************************/
DECLARE @var4 INT
SELECT ISNULL(NULL, NULL) AS 'ISNULL4';
SELECT COALESCE(CAST(NULL as NVARCHAR), NULL) AS 'COALESCE4';
-- SELECT COALESCE(NULL, NULL) AS 'ISNULL'; -- Throw Error

/****************************************************************************
 ISNULL is an internal to TSQL Engine. COALESCE is an ANSI standard function.
****************************************************************************/

/****************************************************************************
When it comes to performance both ISNULL and COALESCE are same to a maximum
extent. But query execution plan differs for COALESCE because it will get
evaluated using a CASE statement. So one needs to be careful with the
arguments of COALESCE.
****************************************************************************/

 

Output –

IsnullandCoalesce1


Republished from Rami Vemula [15 clicks].  Read the original version here [1 clicks].

rami
538 · 0% · 70
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Comparison of ISNULL and COALESCE in TSQL" rated 5 out of 5 by 1 readers
Comparison of ISNULL and COALESCE in TSQL , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]