ISNULL vs. COALESCE

SQL Server has two coalescence functions, ISNULL and COALESCE. Ostensibly, the difference is quite trivial, 2 parameters vs. 2 or more. However, there's a subtle difference in behavior that has very specific consequences for code generated for LINQ to SQL and Entity Framework entities.

It pertains to the following excerpt from Books Online's description of COALESCE:

ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.

I admit that it took me a while to understand what it meant. It refers to the nullability property of the resulting value's type. What that means for LINQ to SQL and Entity Framework entities is that the choice of ISNULL vs. COALESCE comes down to what data types and validation metadata you want generated for those entities' properties.

Demonstration

I'll start with a simple table that has two nullable columns.

CREATE TABLE [dbo].[Things]

(

   [Int32] [int] NULL,

   [String] [varchar] (10) NULL

);

I selected the [int] and [varchar] types because the associated .NET types are a value type and a reference type, respectively, so we can see the results of ISNULL and COALESCE on each.

Now, I'll create a view on this table, using both ISNULL and COALESCE on both columns.

CREATE VIEW [dbo].[ViewOfThings]

AS

SELECT

   ISNULL([Int32], 0) AS [RequiredInt32],

   COALESCE([Int32], 0) AS [OptionalInt32],

   ISNULL([String], '') AS [RequiredString],

   COALESCE([String], '') AS [OptionalString]

FROM [dbo].[Things];

And finally, when I add the view to a LINQ to SQL diagram, it generates the following properties (abbreviated for clarity):

[Column(Storage="_RequiredInt32", DbType="Int NOT NULL")]

public int RequiredInt32

{

   // ...

}

[Column(Storage="_OptionalInt32", DbType="Int")]

public System.Nullable<int> OptionalInt32

{

   // ...

}

[Column(Storage="_RequiredString", DbType="VarChar(10) NOT NULL", CanBeNull=false)]

public string RequiredString

{

   // ...

}

[Column(Storage="_OptionalString", DbType="VarChar(10)")]

public string OptionalString

{

   // ...

}

RequiredInt32 is an int while OptionalInt32 is a Nullable<int> (int?). RequiredString has CanBeNull=false specified on the Column attribute, while OptionalString does not. Selecting the right coalescence function ensures that the generated code has the correct types and validation metadata.

The same applies for result sets from stored procedures and table-valued functions.