CODEWITHSUNDEEP
sqlsql-server-2008
한명훈 陳祖亮
한명훈 陳祖亮

Reputation: 57

coalesce VS isnull

can anyone give me explain about difference coalesce and isnull in sql server ?

I was query with isnull for get a value in my localhost database and work fine but not worked on my server with isnull I use coalesce and it's worked in my server

This is on my database localhost

set @QQ = ISNULL( (select max(qq) from ods4..dbmassignsalescustprod where salesname = '{UserID}'),
              (select max(qq) from ods4..dbmassignsalescustprod))

This is on my database server

set @QQ = COALESCE( (select max(qq) from ods4..dbmassignsalescustprod where salesname = '{UserID}'),
              (select max(qq) from ods4..dbmassignsalescustprod))

On my database server I use isnull and that value cannot be get to QQ variable 谢谢 고맙습니다

Upvotes: 0

Views: 3654

Answers (2)

Paul
Paul

Reputation: 275

This is from https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql

Comparing COALESCE and ISNULL The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times.

Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1), although equivalent, have different nullability values. This makes a difference if you are using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269593

They should both work -- isnull() or coalesce(). You can also do:

set @QQ = (select top 1 qq
           from ods4..dbmassignsalescustprod
           order by (case when salesname = '{UserID}' then 1 else 2 end),
                    qq desc
          );

Upvotes: 0

Related Questions