Is COALESCE sargable now?
No COALESCE is not sargable.

Your own test demonstrates this well.

An exception would be if you created a computed column with the COALESCE expression and indexed that.

CREATE TABLE Test 
(
    ID int primary key clustered, 
    Mod6 int null, 
    Foo AS COALESCE(Mod6, 0),
    INDEX IX_Mod6 NONCLUSTERED (Mod6),
    INDEX IX2_Mod6 NONCLUSTERED (Foo),
);


You could end up with a seek on that

SELECT Mod6
FROM Test WITH (INDEX = IX2_Mod6, FORCESEEK)
WHERE COALESCE(Mod6, 0) = 0;


ISNULL is marginally more sargable in that if it is totally redundant it can be optimised out and not prevent a seek.

i.e. if the column Mod6 is defined as NOT NULL then the following can produce a seek.

SELECT Mod6
FROM Test 
WHERE ISNULL(Mod6, 0) = 0;


But this of course doesn't supply any benefit over just doing

WHERE Mod6 = 0