CODEWITHSUNDEEP
sqlsql-servercoalesce
Hotlansy Httlandy
Hotlansy Httlandy

Reputation: 349

SQL Server Coalesce condition

I have this piece of code which I am not sure how would it work:

UPDATE Data
    SET Processed = 1
FROM Data
JOIN Meters
    ON Meters.ServiceAccount = serv_acct
where COALESCE(Processed, 0) = 0

My question is about the last line! Would that line ever be true in this case? Since I am setting Processed to 1 then how would that work: where COALESCE(Processed, 0) = 0?

Can anybody explain the logic of using Coalesce in this way?

This code is not written by me. Thank you

Upvotes: 1

Views: 527

Answers (2)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

The COALESCE function is described here: http://technet.microsoft.com/en-us/library/ms190349.aspx

I think the reason behind using this predicate where COALESCE(Processed, 0) = 0 was to filter all rows which have column Processed IS NULL or equal to 0.

Instead, I would use use predicates:

UPDATE Data
SET Processed = 1
FROM Data JOIN
    Meters 
    ON Meters.ServiceAccount = serv_acct
where Processed IS NULL OR Processed = 0;

because they are SARGable. This means Index Seek.

Applying an expression on Processed column will force SQL Server to choose an [Clustered] Index Scan.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Your query is:

UPDATE Data
    SET Processed = 1
    FROM Data JOIN
         Meters
         ON Meters.ServiceAccount = serv_acct
    where COALESCE(Processed, 0) = 0;

An update query determines the population of rows it is acting on before any of the changes are made. So, the final line is taking rows where Processed is either NULL or 0. The update is then setting Processed to 1 for those rows. In other words, the where clause is acting as a filter on the rows to modify. The specific statement is to keep the rows where the value of Processed is NULL or 0.

Upvotes: 2

Related Questions