Posted by Erland Sommarskog on 06/14/05 01:22

(uli2003wien@lycos.at) writes:
> We have two tables with around 2 / 3 million rows. These tables have no
> key and no ID. (This major design flaw will be overcome in some later
> version of the application-software working on this DB but right now i
> have to live with this).
>
> Now for the funny bit
>
> 1) I open one window in the Query-Analyzer and write some code like
> Begin transaction INSERT INTO TABLE COMMIT
> 2) in another window i write "SELECT COUNT(*) from TABLE"
>
> If I perform the insert then afterwards select count(*) the row-count
> is incremented by two whereas the Insert-Statement said "1 row(s)
> modified.
>
> DBCC gives no errors.
> DBCC gives amount of rows 2 million rows
> Select count(*) on the same table gives 3 million rows

Well, I would definitely add a non-unique clustered index on the
table. It does not really matter which column, but if you add the
index, the entire table will be reorganized.

I recognize the symptom; other people have recommended similar observations.
Although they usually had a WHERE clause, and maybe even some indexes
on the table. I vaguely recall that a clustered index was a workaround
out of the problem.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]