Why to use COALESCE instead of ISNULL

As of this writing, Books Online isn't completely clear about how ISNULL works. I submitted suggestion 778704 on Connect asking Microsoft to explicitly state that if the replacement_value is longer than the check_expression, it will be truncated to the length of the check_expression. A code sample that demonstrates the problem is shown below. To prevent this kind of truncation, use COALESCE instead of ISNULL as shown in the last step. Additionally, COALESCE is ANSI standard and ISNULL is not. Read Aaron's post if you want to know more about the performance implications of COALESCE vs. ISNULL. My primary point is to make people aware of how things actually work so that they can write more robust code. UPDATE: On February 12, 2013, Microsoft indicated that they would be clarifying the documentation on ISNULL.

DECLARE @msg NVARCHAR(4000);
DECLARE @str1 NCHAR(1);
DECLARE @str2 NCHAR(2);
DECLARE @str3 NCHAR(3);
DECLARE @str5 NCHAR(5);
DECLARE @str11 NCHAR(11);

SET @msg = N'@str1 is ' + ISNULL (@str1, N'not defined');
SELECT N'str1 ISNULL', @msg;

SET @msg = N'@str2 is ' + ISNULL (@str2, N'not defined');
SELECT N'str2 ISNULL', @msg;

SET @msg = N'@str3 is ' + ISNULL (@str3, N'not defined');
SELECT N'str3 ISNULL', @msg;

SET @msg = N'@str5 is ' + ISNULL (@str5, N'not defined');
SELECT N'str5 ISNULL', @msg;

SET @msg = N'@str11 is ' + ISNULL (@str11, N'not defined');
SELECT N'str11 ISNULL', @msg;

SET @msg = N'@str1 is ' + COALESCE (@str1, N'not defined');
SELECT N'str1 COALESCE', @msg;