Hi there - have a weird thing happening on one of my tables in a legacy sql 2000 database. The table ion question has around 6.4million rows and has a primary key index on it. The problem is that if you issue
Select count * from mytable
the query just runs and runs. The table is just a staging table and as such gets truncated every so often when new data arrives for processing.
If you then run the
Select top 1000 * from mytable and slowly increase the value it does return results - however certainly not as quick as we'd expect. If you have a look at the actual execution plan for each of the incremental counts we run it is using the PK index on the table to obtain the info.
We even tried to drop the index but it said it could not be done due to the fact the index was tied to another table. But this was not the case - totally weird.
Has anyone got any ideas what might be causing this?
Thanks in advance