Thursday, November 10, 2005

COALESCE vs. ISNULL

I found a really good article that describes the differences between COALESCE and ISNULL on SQL Server. You can read this article at http://www.aspfaq.com/show.asp?id=2532. The following is a summary of the article.

COALESCE

ISNULL

ISO SQL Standard Command

Yes

No

Number of Arguments

Unlimited

2

Return value datatype

Based on all arguments

Datatype of first argument

Computed Column Nullability

Nullable

Not Nullable



A lot of situations only require two arguments, so you will have to decide which function is best for your situation. The return value’s datatype is often the deciding factor. For example:

DECLARE @SmallString NVARCHAR(3)
SET @SmallString = NULL

SELECT ISNULL(@SmallString, N'1234567') AS [ISNULL]

SELECT COALESCE(@SmallString, N'1234567') AS [COALESCE]

Results:

ISNULL
------
123

COALESCE
--------
1234567

Another factor to consider is ISNULL will perform better in certain situations. The article listed above contains more details on this.

No comments: