Regarding indexes (I think)

I have an issue where i can do the following
Select count(*) from table and it returns 181,000
Select count(*) from table where ncolumn is not null and it returns 174,000
Select count(*) from table where ncolumn is null and it returns 153,000
Now, those numbers should at up to the count of the top one,
I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
dbreindex with the same problem.
Anyone have any suggestions?Try,
DBCC UPDATEUSAGE ('db_name', 'table_name')
go
select count(*), count(case when ncolumn is not null then 1 end),
count(ncolumn is null then 1 end)
from table
go
AMB
"Gary" wrote:

> I have an issue where i can do the following
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns 174,00
0
> Select count(*) from table where ncolumn is null and it returns 153,000
> Now, those numbers should at up to the count of the top one,
> I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
> dbreindex with the same problem.
> Anyone have any suggestions?
>
>|||Correction,
DBCC UPDATEUSAGE ('db_name', 'table_name')
go
select count(*), count(case when ncolumn is not null then 1 end),
count(case when ncolumn is null then 1 end)
from table
go
AMB
"Alejandro Mesa" wrote:
> Try,
> DBCC UPDATEUSAGE ('db_name', 'table_name')
> go
> select count(*), count(case when ncolumn is not null then 1 end),
> count(ncolumn is null then 1 end)
> from table
> go
>
> AMB
> "Gary" wrote:
>|||Are there inserts into the table between each select?
"Gary" <clgary@.yahoo.com> wrote in message
news:unQeCtRGFHA.1188@.tk2msftngp13.phx.gbl...
> I have an issue where i can do the following
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns
174,000
> Select count(*) from table where ncolumn is null and it returns 153,000
> Now, those numbers should at up to the count of the top one,
> I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
> dbreindex with the same problem.
> Anyone have any suggestions?
>|||Thanks for the help, it sort of worked
Select count(*) from table and it returns 181,000
Select count(*) from table where ncolumn is not null and it returns 174,000
Select count(*) from table where ncolumn is null and it returns 153,000
Same results there, however,
select count(*), count(case when ncolumn is not null then 1 end),
count(ncolumn is null then 1 end)
from table
returns
181,000 159,000 22,000
which is correct,.but I'm still not sure whats going on there.
Another poster I saw asked if there were inserts happening during the
selects, and its not.
So, I'm still baffled. Something else to note, even if i run them
individually, instead of all 3 as a batch in QA, it still returns the wrong
count.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:61BE7216-D2F7-435D-B1E4-656E4D683C5E@.microsoft.com...
> Try,
> DBCC UPDATEUSAGE ('db_name', 'table_name')
> go
> select count(*), count(case when ncolumn is not null then 1 end),
> count(ncolumn is null then 1 end)
> from table
> go
>
> AMB
> "Gary" wrote:
>
174,000|||If you are running parallel queries:
http://support.microsoft.com/kb/814509/en-us
http://support.microsoft.com/default.aspx/kb/838166 (for getting the 878
build).
S. L.
"Gary" <clgary@.yahoo.com> wrote in message
news:unQeCtRGFHA.1188@.tk2msftngp13.phx.gbl...
>I have an issue where i can do the following
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns
> 174,000
> Select count(*) from table where ncolumn is null and it returns 153,000
> Now, those numbers should at up to the count of the top one,
> I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
> dbreindex with the same problem.
> Anyone have any suggestions?
>|||Perhaps uncommitted inserts or updates are being held open by other
processes and your current transaction isolation level allows your query to
include "dirty reads". Sometimes a large number of updates (100,000s) can
take 1/2 hour or more to rollback if a batch transaction fails. Try running
the following, it will lock any records as it reads and will not include
"dirty reads".
Run sp_lock to see what other processes may have records locked. Also, if
the queries seems to take longer than usual to run using this locking
method, then you can use sp_who2 to determine if your SPID is blocked by
another process.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Select count(*) from table
Select count(*) from table where ncolumn is not null
Select count(*) from table where ncolumn is null
"Gary" <clgary@.yahoo.com> wrote in message
news:uDpR8ISGFHA.4004@.tk2msftngp13.phx.gbl...
> Thanks for the help, it sort of worked
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns
174,000
> Select count(*) from table where ncolumn is null and it returns 153,000
> Same results there, however,
> select count(*), count(case when ncolumn is not null then 1 end),
> count(ncolumn is null then 1 end)
> from table
> returns
> 181,000 159,000 22,000
> which is correct,.but I'm still not sure whats going on there.
> Another poster I saw asked if there were inserts happening during the
> selects, and its not.
> So, I'm still baffled. Something else to note, even if i run them
> individually, instead of all 3 as a batch in QA, it still returns the
wrong
> count.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> news:61BE7216-D2F7-435D-B1E4-656E4D683C5E@.microsoft.com...
> 174,000
153,000
>|||Thanks,
That seems to do the trick
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:OOqE3YSGFHA.2296@.TK2MSFTNGP15.phx.gbl...
> If you are running parallel queries:
> http://support.microsoft.com/kb/814509/en-us
> http://support.microsoft.com/default.aspx/kb/838166 (for getting the 878
> build).
> S. L.
> "Gary" <clgary@.yahoo.com> wrote in message
> news:unQeCtRGFHA.1188@.tk2msftngp13.phx.gbl...
>sql