Menu
Given the following:
SELECT ISNULL('XY' + NULL, 'ABCDEFGHIJ') -- Outputs ABC (Why?) SELECT COALESCE('XY' + NULL, 'ABCDEFGHIJ') -- Outputs ABCDEFGHIJ Why are these statements returning different results?
582
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.
advantage that COALESCE has over ISNULL is that it supports more than two inputs, whereas ISNULL supports only two. Another advantage of COALESCE is that it's a standard function (namely, defined by the ISO/ANSI SQL standards), whereas ISNULL is T-SQL–specific.
ISNULL. Reported result: COALESCE is faster.
Coalesce or IsNull can still work, since the variable/parameter will have no value assigned. The problem with this method, or similar ones, is that it tends to kill performance because of non-SARGability of the query. Dynamic SQL is often the best answer for this.
According to Microsoft documentation, for function:
ISNULL(check_expression, replacement_value) replacement_value must be of a type that is implicitly convertible to the type of check_expression. Note that type for 'xy'+NULL is VARCHAR(3). Because of this your string 'ABCDEFGHIJ' is cast to VARCHAR(3) and thus trimmed.
It sounds strange why it is not VARCHAR(2), but this is the way it is - one character longer than 'xy'. You can play with this SQLFiddle and see for yourself that type for 'xy'+NULL is the same as for expression CASE WHEN 1=2 THEN 'XYZ' ELSE NULL END, which is NULL but is implicitly compatible to VARCHAR(3).
It seems that for expression 'xy'+NULL perceived length can be computed as 'xy' string length (2) plus 1 for every NULL added. For example, type of 'xy'+NULL+NULL is VARCHAR(4), type for 'xy'+NULL+NULL+NULL is VARCHAR(5) and so on - check out this SQLFiddle. This is extremely weird, but that is how MS SQL Server 2008 and 2012 work.
92
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
