Introduction
SQL Server,
undoubtedly my favourite Microsoft product, but a seasoned baseball pitcher
will be impressed by the curve balls it can produce.
Using the code
Execute the
following script:
declare @var1 VARCHAR(1)
SET @var1 = '2'
SELECT ISNULL(@var1, 1)
SELECT ISNULL(@var1, -1)
SELECT COALESCE(@var1, -1)
SET @var1 = NULL
SELECT ISNULL(@var1, 1)
SELECT ISNULL(@var1, -1)
SELECT COALESCE(@var1, -1)
Sure, it is
easy to see the culprit, but now read this thinking that @var1 is a column
defined in a table, so it is not so obvious what the length of the
column/variable is.
ISNULL –
replaces the NULL value with the given replacement.COALESCE –
returns the first not NULL expression in the argument list.
When
reading the MSDN topic on T-SQL ISNULL it does say that it returns the SAME
type as the checked expression, the value is IMPLICITLY converted to the
checked expression’s type when the two types differ.
The
interesting part is that the conversion does not procedure a String or binary
data will be truncated error?
This does
not prove that COALESCE is better than ISNULL, all it is saying: know the
animal you are working with.
