Be careful using ISNULL()

Recently I found quite unpredictable (for me) behavior of SQL Server ISNULL() function. If you use it in Select, both arguments are nvarchar and length of first is less then second – then value of second field will be truncated to the length of first. So, if you run this SQL:

SELECT Address,
ISNULL(Region, Address)
FROM Suppliers

on sample Northwind database – result will be:

9-8 Sekimai Musashino-shi|9-8 Sekimai Mus
92 Setsuko Chuo-ku 92|Setsuko Chuo
Lyngbysild Fiskebakken 10|Lyngbysild Fisk

Any idea why this happens? Fortunately – COALESCE() works correct in this case, so I would recommend using it instead of ISNULL().

 

Technorati tags: SQL Server, T-SQL