Post reply

NULLS

  • May 10, 2012 at 8:36 pm

    #256295

    Comments posted to this topic are about the item NULLS

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

  • May 10, 2012 at 8:36 pm

    #1486079

    Nice question to end the week ....

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • May 10, 2012 at 11:38 pm

    #1486095

    Great question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • May 11, 2012 at 1:07 am

    #1486118

    Good question, but a BIG mistake in the explanation!!

    "COALESCE returns a datatype from the first non-null value in the statement depending on data type precedence"

    The datatype of the first non-null value is irrelevant. COALESCE will first consider the data types of ALL arguments, then apply data type precedence rules to them, and then use the result as the data type of the result. Here is a simple repro:

    DECLARE @number int, @text varchar(20);

    SET @number = NULL;

    SET @text = 'three';

    SELECT COALESCE(@number, @text);

    go

    DECLARE @number int, @text varchar(20);

    SET @number = NULL;

    SET @text = 'three';

    SELECT COALESCE(@text, @number);

    The integer parameter is null; the first (and only) non-null parameter is varchar. And yet, both the COALESCE statements fail because they try to convert the result to an integer - the highest precedence datatype of the two.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • May 11, 2012 at 1:55 am

    This was removed by the editor as SPAM

  • May 11, 2012 at 2:00 am

    #1486143

    Thank you all for the comments and especially to Hugo.

    I love SQLServerCentral.com - I learn something new every day and the help and assistance of other members is readily available.

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

  • May 11, 2012 at 2:07 am

    #1486146

    I got the answer right but only after removing 'Null' from the declaration of the Local Variable.

    When I parse my declaration of the Local Variable, it says "Cannot assign a default value to a local Variable"

    So, I removed the Null from my declaration and got my answers correct.

    Anyway, nice question. Knowledgeable indeed!!! 🙂

  • May 11, 2012 at 2:08 am

    #1486149

    Nice, easy question to end the week.

    @hugo: Completely agree on your point about the issue with the explanation.

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com

    Follow me on
    Twitter: @sqltwins

  • May 11, 2012 at 2:31 am

    #1486156

    Nice question Robin.

    Lucy Dickinson
    BI SQL Developer

  • May 11, 2012 at 2:36 am

    #1486158

    Prasune Verma (5/11/2012)

    I got the answer right but only after removing 'Null' from the declaration of the Local Variable.

    So I guess you are running SQL Server 2005 or older?

    The ability to assign a value to a variable on the DECLARE statement (as used in the QotD) was added in SQL Server 2008.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • May 11, 2012 at 2:39 am

    #1486159

    Yes Hugo, thats right. I am using SQL Server 2005. Thanks for the info. 🙂

  • May 11, 2012 at 2:59 am

    #1486165

    Nice question, thanks.

    Always good to know the difference in behaviour between ISNULL & COALESCE, can be a gotcha!

  • May 11, 2012 at 5:18 am

    #1486199

    Glad to have answered it right because I had some issues with COALESCE and ISNULL before so I had to research a little. Thank you for the excellent question. 🙂

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • May 11, 2012 at 5:20 am

    #1486201

    Good question!!

    thanks!!!!

    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!

  • May 11, 2012 at 5:51 am

    #1486225

    Thank you all again for your feedback - ISNULL and COALESCE befuddled me for a while!

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply