December 8, 2003 at 9:44 am
send: bit column (updated daily)
select count(*) from table where send = 1
11.5 million rows
drop and recreate index
select count(*) from table where send = 1
6.4 million rows.
Why does this happen?
I need to drop and recreate index almos daily to avoid this issue. Forcing a no index use just takes forever when counting.
December 8, 2003 at 11:12 am
Are you updating this table constantly?
Does it use the same index for both Count statements?
You might try DBCC dbreindex.
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
December 8, 2003 at 11:22 am
Are you updating this table constantly?
YES
Does it use the same index for both Count statements?
YES
You might try DBCC dbreindex.
It does not workaround the problem. I need to DROP the index and Recreate manually.
December 8, 2003 at 2:27 pm
One workaround would be to change the column to an int or smallint.
Is it a clustered index? Is there a clustered index on the table? Are there any other columns in it? What @@version of SQL?
Just curious i'd like to putter with this and see if I can get it to do the same thing.
December 8, 2003 at 2:40 pm
Yes we are thinking on changing the column datatype.
The tables have a clustered index on the primary key. There are many columns, related to customer information.
I am Using SQL Server 2000 with service pack 3
December 8, 2003 at 2:56 pm
You mean the problem field isn't part of an index itself? I noticed in later versions of SQL it will allow bit fields to be indexed, but seem to recall it wouldnt let me in 6.5. I was just testing for someone - don't think I've every indexed on a bit field myself; not even as a composite.
If I set up a table Field1 int not null identity(1,1) primary key, field2 bit not null default(0) would that match your scenario on a small scale? Or is your bit field not constrained to not null?
December 8, 2003 at 3:14 pm
Sounds like it. Thanks jonathan you saved me some exploration time.
December 8, 2003 at 3:22 pm
Jonathan thanks a lot. That is the problem. I will appy the patch or just workaround it.
cmore thanks for your help too.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply