Index and NULL values

Question

Post reply

Index and NULL values

Carlos Eduardo-426193

SSC Journeyman

Points: 78
More actions
May 30, 2007 at 7:26 am

#117331

Hi All,
I´m having a problem with count of null values fields and indexes...
I have a table (tb_Propose) with around 8 million lines.
A field Dt_flag -- Datetime
An index Ix_Dt_flag, nonclustered, with field Dt_flag, only
When I do the "select count(*) from tb_Propose where Dt_Flag is null", the results comes so different from real... When I do "reindex", on first time, the select works fine. However, from the second execution the results coming wrong. The database is with option "Auto Update Stats" enabled.
Results:
select count(*) from tb_Propose where Dt_flag is null
select count(*) from tb_Propose where Dt_flag is not null
select count(*) from tb_Propose

-----------
8405710
(1 row(s) affected)

-----------
3818428
(1 row(s) affected)

-----------
8978255
(1 row(s) affected)

[]s
Carlos Eduardo
Bizplace
http://www.bizplace.com.br

Viewing 8 posts - 1 through 8 (of 8 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

See what happens when you do this.

select count(1) from tb_Propose where Dt_flag is null

select count(1) from tb_Propose where Dt_flag is not null

select count(1) from tb_Propose

Just curious.

Carlos Eduardo-426193 SSC Journeyman Points: 78 More actions · Answer 2

Carlos Eduardo-426193

SSC Journeyman

Points: 78

May 31, 2007 at 8:24 am

#710013

I already had made this, and the results was the same

Antares686 SSC Guru Points: 125444 More actions · Answer 3

I cannot reporduce this on any of my tables but none are over 50k records now and I gave up access to the only large one I used to control. But look at the execution plan to see what is happening in each case and how they differ. If it is the index coming into play on the first then try forcing it not to use that index and see what happens.

Carlos Eduardo-426193 SSC Journeyman Points: 78 More actions · Answer 4

When I do the query without index, the results are ok ...

When I do the query with other index, such as PK (Ex: Select count(*) from tb_Propose where Dt_flag is null and Id_propose > 0), the results are OK too

The problem only happens when the index by Dt_flag is used

Antares686 SSC Guru Points: 125444 More actions · Answer 5

I still cannot duplicate even when creating an index on a nullable column. Have you tried to drop and recreate the index to see what would happen. Not ideal but might fix??? Also what datatype is the column, may hold the answer as to why I cannot duplicate.

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 6

I created an 8 million row table and have tried all the scenarios you have... I cannot get it to fail in the manner which you speak of. I think Antares is correct... drop and recreate the index would be the next logical step.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Carlos Eduardo-426193 SSC Journeyman Points: 78 More actions · Answer 7

Drop and recreate the index, was my second try... The first was force the update statiscs...

When I re-index, on the first select, it was ok. From the second, it was wrong again.

Yesterday, I installed SP4 and now appear to be ok. I´m doing the table load again to do the final tests. I think that the error is with data. Perhaps, 1 single row lost in 8 million row table... :-S

Thanks !