SQL Server 2000 and a basic select count * issue

Last Post 06 Nov 2009 07:49 AM by Markus_SQL. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

06 Nov 2009 06:19 AM
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
New Member
New Member

06 Nov 2009 07:49 AM
Try running select count(1) from ......... runs MUCH quicker

Acceptable Use Policy