NULL & Count()

Question

Post reply

NULL & Count()

Viewing 10 posts - 16 through 25 (of 25 total)

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

karthik M One Orange Chip Points: 29139 More actions · Answer 1

Gila,

it's simply going to evaluate it as a scalar value.

what it means ?

karthik

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Scalar = single value

Hence, the where clause you had (@Nm IS NULL) is a scalar (single value) evaluation, because it's a single value (the contents of the variable) been compared with a single value (NULL)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 3

Yes.

what will happen internally is

Declare @Nm varchar(25)

select @Nm = 'Age'

select @Null = count(*) from #t1 where @Nm is null

it means select @Null = count(*) from #t1 where Age is null. do u agree till this point ?

But after that, why it is not storing 3 in @Null variable. Please correct me if i am wrong.

My question is:

count(*) from #t1 where Age is null whether it will be executed by sql analyzer or not. If not why ?

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 4

I would like to add some more things.

if i execute the below one, it is giving 3 as the output.

Declare @Nm varchar(25),@Null int

--select @Nm = 'Age'

select @Null = count(*) from #t1 where age is null

select @Null

karthik

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

karthikeyan (4/24/2008)
I would like to add some more things.
if i execute the below one, it is giving 3 as the output.
Declare @Nm varchar(25),@Null int
--select @Nm = 'Age'
select @Null = count(*) from #t1 where age is null
select @Null

So - it sounds like you have your answer, or not?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

mrpolecat SSCertifiable Points: 7014 More actions · Answer 6

karthikeyan (4/24/2008)
Yes.
what will happen internally is
Declare @Nm varchar(25)
select @Nm = 'Age'
select @Null = count(*) from #t1 where @Nm is null
it means select @Null = count(*) from #t1 where Age is null. do u agree till this point ?
But after that, why it is not storing 3 in @Null variable. Please correct me if i am wrong.
My question is:
count(*) from #t1 where Age is null whether it will be executed by sql analyzer or not. If not why ?

this

Declare @Nm varchar(25)

select @Nm = 'Age'

select @Null = count(*) from #t1 where @Nm is null

becomes

select @Null = count(*) from #t1 where 'Age' is null

'Age' is not null, it is 'Age'

you can't pass a column as a variable unless you use dynamic SQL

Sorry if this has already been discussed, I only read the last few entries:)

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

karthikeyan (4/24/2008)
it means select @Null = count(*) from #t1 where Age is null. do u agree till this point ?

No!

It means

select @Null = count(*) from #t1 where 'Age' is null

Not Age as a column name, 'Age' as a string literal. Which IS NOT NULL

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 8

It means
select @Null = count(*) from #t1 where 'Age' is null
Not Age as a column name, 'Age' as a string literal. Which IS NOT NULL

why and How ? Can you explain me ?

karthik

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 9

What part don't you understand?

When SQL uses a variable in a where clause it uses the contents of the variable as a literal value. Not as a reference to a table/column/database/any other object.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

karthik M One Orange Chip Points: 29139 More actions · Answer 10

When SQL uses a variable in a where clause it uses the contents of the variable as a literal value. Not as a reference to a table/column/database/any other object.

This is what i expect. Now i am clear.

Thanks a lot Gila !

karthik