COUNT NULLs

  • Comments posted to this topic are about the item COUNT NULLs

  • What does database indicate about nulls being allowed in this table if it is not specified as part of the create table - the insert would fail thus not giving 4 🙂

  • Thank you for the post, Steve, nice one.

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Nice question. Pretty straightforward after the discussion of the previous COUNT question 😉

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

  • Tony.l (1/20/2015)


    What does database indicate about nulls being allowed in this table if it is not specified as part of the create table - the insert would fail thus not giving 4 🙂

    Unless explicitly specified, you need to assume SQL Server defaults, which means NULLs are allowed.

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

  • Thanx 4 the ez question, which is on the contrary of the 1st Count question.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • Nice basic question. thanks for sharing. I assume SQL SERVER default setting discussion wont come in for this QotD but who knows. 🙂

  • Koen Verbeeck (1/21/2015)


    Tony.l (1/20/2015)


    What does database indicate about nulls being allowed in this table if it is not specified as part of the create table - the insert would fail thus not giving 4 🙂

    Unless explicitly specified, you need to assume SQL Server defaults, which means NULLs are allowed.

    Exactly right. Given the previous debate, I think this was a very timely question.

  • Hany Helmy (1/21/2015)


    Thanx 4 the ez question, which is on the contrary of the 1st Count question.

    They aren't contradictory to each other because they were different questions. The debate over the first question is probably why this question was asked. Since the QOTD is about learning, the debate showed a need.

    I thought it was easy, but at this moment in time, 76% of the respondents got it right, which means that 24% got it wrong.

  • A good (incorrect) alternate answer would be 5. Just to make one think a little harder. 😎

  • Easy one for me finally.

  • +2 - thanks!

  • Nice question. I've seen some junior developers tripped up by this before.

  • Just though I'd dispel the notion that only count(*) counts nulls:

    select count('null') as count_null from (select null n union all select null) countnull

    returns:

    count_null

    2

    Gerald Britton, Pluralsight courses

  • g.britton (1/21/2015)


    Just though I'd dispel the notion that only count(*) counts nulls:

    select count('null') as count_null from (select null n union all select null) countnull

    returns:

    count_null

    2

    We can have even more fun:

    with thing(I) as (

    select top 65536 NULL

    from master.sys.all_columns cross join master.sys.all_objects

    )

    select count(1/0) as NULLs from thing;

    NULLs

    65536

    Tom

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

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