COUNT NULLs

Question

COUNT NULLs

Steve Jones - SSC Editor

SSC Guru

Points: 738347
More actions
January 20, 2015 at 8:47 pm

#73582

Comments posted to this topic are about the item COUNT NULLs

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

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

Tony.l SSC Enthusiast Points: 184 More actions · Answer 1

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

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 2

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.

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 3

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

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 4

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

Hany Helmy SSChampion Points: 13921 More actions · Answer 5

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

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Umer Akram SSC-Insane Points: 22209 More actions · Answer 6

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

RLilj33 SSCrazy Points: 2203 More actions · Answer 9

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 10

akljfhnlaflkj

SSC Guru

Points: 76202

January 21, 2015 at 8:47 am

#1771864

Easy one for me finally.

Andre Ranieri SSCrazy Points: 2819 More actions · Answer 11

Andre Ranieri

SSCrazy

Points: 2819

January 21, 2015 at 10:21 am

#1771921

+2 - thanks!

Ken SSCoach Points: 17191 More actions · Answer 12

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

g.britton SSChampion Points: 13863 More actions · Answer 13

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

TomThomson SSC Guru Points: 104773 More actions · Answer 14

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

COUNT NULLs

Cookies on SQLServerCentral