Seemingly inaccurate COUNT(*)

27 Aug 2005 11:17 AM

Chris Lacey

Hi all,

I was wondering if anyone could explain this apparent anomaly.

I have a fairly large table (600,000 rows) into which some regular processes
are inserting and updating rows. (Inserts occur by means of a transactional
stored procedure which inserts into this table and a secondary one, on which
a Foreign Key to the table exists.)

The primary key for the table is an identity, and started at 1, hence in
most cases "SELECT MAX(MessageID) FROM Message" returns the same result as
"SELECT COUNT(*) FROM Message".

However, at some given points in time (possibly during a time when some of
the rows are being updated), COUNT(*) returns a value that is a little
higher than MAX(MessageId). A second or so later, COUNT(*) will fall back
down to the value of MAX(MessageId). No DELETEs are, or ever have been,
performed on the table.

Does anyone have any idea why this inconsistency may be occurring? It seems
as if phantom rows are being added in, visible to the COUNT but not the MAX
(although they're both being executed in the same transaction), and then
removed again - but not affecting the identity count because the next "real"
row to get added assumes the next identity value.

Many thanks in advance for any help,

Cheers,

Chris.

27 Aug 2005 3:24 PM

Brian Selzer

IDENTITY does not guarantee that there will never be gaps, even if there
have never been any deletes. Any rollback, whether from a deadlock,
constraint violation, out of memory condition, etc., will leave gaps in the
IDENTITY sequence. It is extremely bad practice to rely on the sequence of
IDENTITY values.

You didn't give any information on the transaction isolation level that
you're using. If it is set to READ UNCOMMITTED, or the select statement has
WITH(NOLOCK), then it is possible that another transaction is halfway
through the process of updating. SQL Server physically deletes the old rows
and inserts new rows when it performs an update. If you use READ
UNCOMMITTED, then the exclusive locks are ignored, so you'll get
inconsistent results. Another possibility is that you're using
WITH(READPAST) to ignore rows locked by another transaction.

When you post a question, you should post DDL and sample data, so people can
try to understand and maybe reproduce the problem.

Show quote

"Chris Lacey" <chris.la***@bigfoot.com> wrote in message
news:u0sBsjvqFHA.2064@TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> I was wondering if anyone could explain this apparent anomaly.
>
> I have a fairly large table (600,000 rows) into which some regular
processes
> are inserting and updating rows. (Inserts occur by means of a
transactional
> stored procedure which inserts into this table and a secondary one, on
which
> a Foreign Key to the table exists.)
>
> The primary key for the table is an identity, and started at 1, hence in
> most cases "SELECT MAX(MessageID) FROM Message" returns the same result as
> "SELECT COUNT(*) FROM Message".
>
> However, at some given points in time (possibly during a time when some of
> the rows are being updated), COUNT(*) returns a value that is a little
> higher than MAX(MessageId). A second or so later, COUNT(*) will fall back
> down to the value of MAX(MessageId). No DELETEs are, or ever have been,
> performed on the table.
>
> Does anyone have any idea why this inconsistency may be occurring? It
seems
> as if phantom rows are being added in, visible to the COUNT but not the
MAX
> (although they're both being executed in the same transaction), and then
> removed again - but not affecting the identity count because the next
"real"
> row to get added assumes the next identity value.
>
> Many thanks in advance for any help,
>
> Cheers,
>
> Chris.
>
>
>
>
>