February 6, 2010 at 9:53 pm
GSquared (12/14/2009)
BaldingLoopMan (12/14/2009)
actually u can use isnull in all situations if u wanted to. A coalesce is a nested isnull.coalesce(custname, last, first) = isnull( isnull( custname, last ), first )
Therefore a coalesce is basically a nested isnull.
True. Would be a pain to type out, be essentially unreadable, and I'd hate to have to maintain something that nested 5 or 10 deep, but it could be done.
I'll still stick with Coalesce.
Yeah... but there's that datatype thing... the nested ISNULL could be just the ticket.
For those that believe in the myth of portability, see if you can write a portable trigger. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2010 at 3:50 am
Jeff Moden (2/6/2010)
For those that believe in the myth of portability, see if you can write a portable trigger. 😉
:laugh: Perfect :laugh:
My own view on the whole ISNULL versus COALESCE debate: it is important to know why both exist, what their various nuances are, and to use both appropriately. Neither is perfect, and I use both regularly depending on the requirement.
I could argue that NULL is in fact evil, but I suspect that might start a whole new debate 😀
Paul
(currently in Napier)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 7, 2010 at 6:14 am
Paul White (2/7/2010)
I could argue that NULL is in fact evil, but I suspect that might start a whole new debate 😀
BWAA-HAAA! NULL is not nothing to argue about. :hehe::-D
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2012 at 5:01 pm
Thanks for clear explanations, but what about the second part of the initial question?
The statement in MSDN, and I quote, is: "... while expressions involving COALESCE with non-null parameters is considered to be NULL" remains a mystery, to me at least.
Am I alone?:-D
March 13, 2012 at 8:09 pm
alexms_2001 (3/13/2012)
The statement in MSDN, and I quote, is: "... while expressions involving COALESCE with non-null parameters is considered to be NULL" remains a mystery, to me at least.
That entry is incorrect; the author seems to have confused the requirement for a PRIMARY KEY to be NOT NULL with the requirement that a non-deterministic column must be persisted to be indexed. I believe the intention was to show this difference instead:
USE tempdb;
CREATE TABLE #Demo
(
col1 integer NULL,
col2 AS COALESCE(col1, 0),
col3 AS ISNULL(col1, 0)
);
-- Only col3 is not nullable
EXECUTE sys.sp_columns
@table_name = N'#Demo';
DROP TABLE #Demo;
On the other hand, it *is* true that the column has to be NOT NULL to allow a PRIMARY KEY and COALESCE fails:
-- Error 1711
CREATE TABLE #Demo
(
col1 integer NULL,
col2 AS COALESCE(col1, 0) PRIMARY KEY,
col3 AS ISNULL(col1, 0)
);
-- Success
CREATE TABLE #Demo
(
col1 integer NULL,
col2 AS COALESCE(col1, 0),
col3 AS ISNULL(col1, 0) PRIMARY KEY
);
DROP TABLE #Demo;
-- Error message was misleading
-- Column does not need to be persisted
-- to be indexed
CREATE TABLE #Demo
(
col1 integer NULL,
col2 AS COALESCE(col1, 0) UNIQUE,
col3 AS ISNULL(col1, 0)
);
DROP TABLE #Demo;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 13, 2012 at 8:22 pm
alexms_2001 (3/13/2012)
Thanks for clear explanations, but what about the second part of the initial question?The statement in MSDN, and I quote, is: "... while expressions involving COALESCE with non-null parameters is considered to be NULL" remains a mystery, to me at least.
Am I alone?:-D
There's a grammatical error there ("expressions" should be "an expresion") but apart from that what it means is pretty clear and quite straightforward. What the sentence is meant to be saying is that for the purpose of determining whether a computed column could contain a NULL an expression like isNull(something, somethingnotnull) is known not to be null while an expression like coalesce(something,...,somethingnotnull) is not known not to be null; or, putting it slightly differently, in "is considered to be NULL" you need to take "NULL" in its sense as a column definition qualifier - ie it just indicates the absence of a not null constraint, it doesn't indicate that any value is actually null.
Of course this is absolutely stupid behaviour for the compiler since it is clear that a coalesce invocation where the last argument is not null can't deliver null, and if the compiler can handle this for isNull it should be able to do so for coalesce too; but stupid design decisions get made all the time - I know I've made more than enough, and unless you are very young I think it's a safe bet you have too - and we just have to accept that microsoft developers are no less prone to that than the rest of us.
And as Paul suggests, the BoL author may have been confused and missed the distinction between an index other than primary key (which doesn't need NOT NULL columns) and a primary key (which does).
Tom
March 13, 2012 at 8:37 pm
Added a Connect bug report for the documentation error:
https://connect.microsoft.com/SQLServer/feedback/details/731028/coalesce-documentation-bug
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 13, 2012 at 8:40 pm
L' Eomot Inversé (3/13/2012)
Of course this is absolutely stupid behaviour for the compiler since it is clear that a coalesce invocation where the last argument is not null can't deliver null...
Sadly this is not true:
http://connect.microsoft.com/SQLServer/feedback/details/546437/coalesce-subquery-1-may-return-null
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 13, 2012 at 9:38 pm
SQL Kiwi (3/13/2012)
L' Eomot Inversé (3/13/2012)
Of course this is absolutely stupid behaviour for the compiler since it is clear that a coalesce invocation where the last argument is not null can't deliver null...Sadly this is not true:
http://connect.microsoft.com/SQLServer/feedback/details/546437/coalesce-subquery-1-may-return-null
That's pretty awful. I hope they fix the bug soon.
If I've understood it correctly, the bug can't happen if isolation level is serializable or snapshot even without a fix, unless the same statement that contains the coalesce expression updates data used by one of its arguments other than the last one (and if it can, the bug has broken those isolation levels as well as the coalesce semantics).
I still regard the treatment of Coalesce(....,nonNullableExpression) as Nullable as stupid behaviour, but given there's currently a bug that makes it nullable I guess it's actually useful stupidity for the time being.
Tom
March 13, 2012 at 11:10 pm
L' Eomot Inversé (3/13/2012)
That's pretty awful. I hope they fix the bug soon.If I've understood it correctly, the bug can't happen if isolation level is serializable or snapshot even without a fix, unless the same statement that contains the coalesce expression updates data used by one of its arguments other than the last one (and if it can, the bug has broken those isolation levels as well as the coalesce semantics).
I still regard the treatment of Coalesce(....,nonNullableExpression) as Nullable as stupid behaviour, but given there's currently a bug that makes it nullable I guess it's actually useful stupidity for the time being.
Well the item is closed as Won't Fix - presumably based on the arguments UC puts forward based on the SQL 'standard'. I tend to agree that the behaviour is unfortunate - at least when a COALESCE (= CASE expression) contains no subqueries. I suppose we could ask them to handle that special case separately, but history suggests that would be unlikely to succeed. Ultimately, then I agree with you on the principles, but think we are stuck with it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 14, 2012 at 8:14 am
SQL Kiwi (3/13/2012)
L' Eomot Inversé (3/13/2012)
That's pretty awful. I hope they fix the bug soon.If I've understood it correctly, the bug can't happen if isolation level is serializable or snapshot even without a fix, unless the same statement that contains the coalesce expression updates data used by one of its arguments other than the last one (and if it can, the bug has broken those isolation levels as well as the coalesce semantics).
I still regard the treatment of Coalesce(....,nonNullableExpression) as Nullable as stupid behaviour, but given there's currently a bug that makes it nullable I guess it's actually useful stupidity for the time being.
Well the item is closed as Won't Fix - presumably based on the arguments UC puts forward based on the SQL 'standard'. I tend to agree that the behaviour is unfortunate - at least when a COALESCE (= CASE expression) contains no subqueries. I suppose we could ask them to handle that special case separately, but history suggests that would be unlikely to succeed. Ultimately, then I agree with you on the principles, but think we are stuck with it.
I guess you are right and we are stuck with it. But this is the sort of thing that makes it absolutely clear that there's no sense in which anything containing coalesce (or indeed case) can be considered declarative; and it's in sharp contrast to the usual line "the optimiser is right to screw up the semantics to improve performance" because here we have a clear case of "let's deliver worse performance and screw up the semantics too while we are about it".
Tom
March 14, 2012 at 11:57 pm
manub22 (3/14/2012)
Check link: http://sqlwithmanoj.wordpress.com/2010/12/23/isnull-vs-coalesce/%5B/quote%5D
It's a reasonable summary of what's already in BOL, but again leads the reader into thinking that computed columns need to be persisted for an index to be created on them, which is not true. The issue is the NOT NULL constraint included in a PRIMARY KEY constraint:
--Msg 8183, Level 16, State 1, Line 5
--Only UNIQUE or PRIMARY KEY constraints can be created on computed columns,
--while CHECK, FOREIGN KEY, and NOT NULL constraints require
--that computed columns be persisted.
CREATE TABLE T2
(
col1 INT,
col2 AS COALESCE(col1, 1) NOT NULL
);
-- Success
CREATE TABLE T2
(
col1 INT,
col2 AS COALESCE(col1, 1) UNIQUE
);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 15, 2012 at 8:42 am
SQL Kiwi (3/14/2012)
manub22 (3/14/2012)
The following blog post will give you differences and performance difference b/w ISNULL() and COALESCE()Check link: http://sqlwithmanoj.wordpress.com/2010/12/23/isnull-vs-coalesce/%5B/quote%5D
It's a reasonable summary of what's already in BOL, but again leads the reader into thinking that computed columns need to be persisted for an index to be created on them, which is not true. The issue is the NOT NULL constraint included in a PRIMARY KEY constraint:
--Msg 8183, Level 16, State 1, Line 5
--Only UNIQUE or PRIMARY KEY constraints can be created on computed columns,
--while CHECK, FOREIGN KEY, and NOT NULL constraints require
--that computed columns be persisted.
CREATE TABLE T2
(
col1 INT,
col2 AS COALESCE(col1, 1) NOT NULL
);
-- Success
CREATE TABLE T2
(
col1 INT,
col2 AS COALESCE(col1, 1) UNIQUE
);
But the manner of deducing nullability is defective, so that one has to jump through some loops to get a primary key constraint on a computed column. Almost any operation on a no-nullable vale results in an expression that the compiler treats as nullable - even multiplication of a non-nullable int by the constant1 results in a nullable int, as illustarted by this nasty little example:-
Create Table Test1 (
A int NULL
, B int not NULL check(-180 < B and B <= 180)
, C as B -- this is not nullable, because B is not nullable
)
-- C is Computed, int, not nullable so could be primary key
----------------------------------------------------------
Create table Test2 (
A int NULL
, B int not NULL check(-180 < B and B <= 180)
, C as B*1 -- this is nullable although B isn't
)
-- C is Computed, int, nullable so could not be primary key
----------------------------------------------------------
Create table Test3 (
A int NULL
, B int not NULL check(-180 < B and B <= 180)
, C as isNull(B/1,-180)
)
-- C is Computed, int, not nullable so could be primary key
----------------------------------------------------------
drop table Test1, Test2, Test3
It seems that trying to use a computed column in a primary key is doomed to failure (except in trivial cases like Test1 above) unless the expression giving its value has an outermost isNull.
Tom
March 15, 2012 at 10:14 pm
L' Eomot Inversé (3/15/2012)
But the manner of deducing nullability is defective, so that one has to jump through some loops to get a primary key constraint on a computed column. Almost any operation on a no-nullable vale results in an expression that the compiler treats as nullable - even multiplication of a non-nullable int by the constant1 results in a nullable int, as illustarted by this nasty little example [...] It seems that trying to use a computed column in a primary key is doomed to failure (except in trivial cases like Test1 above) unless the expression giving its value has an outermost isNull.
I hear what you are saying. For the column to be considered not nullable, we either need an explicit NOT NULL constraint (which is implicit in a PRIMARY KEY) or an outermost ISNULL. An outermost ISNULL is also the only way to create a NOT NULL column via SELECT INTO statement, as far as I know. Now for some reason, a NOT NULL constraint requires the column to be persisted - a reasonable enough requirement I guess, but not one that is entirely intuitive for me. Aside from the issue with subqueries (which can be used in place of any expression) I have no idea why <not null column> * constant should be interpreted as nullable, just that it always has been 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply