This came up on sqlservercentral.com, where I read the following claim (in a discussion thread, not in an article): "COUNT(*) is only guaranteed accurate if your isolation level is REPEATABLE READ, SERIALIZABLE, or SNAPSHOT". I have a repro script which shows how COUNT(*) running under REPEATABLE READ returns wrong results with high concurrency.
Prerequisites
We need a table with data and a function that provides random integers:
CREATE TABLE dbo.WideTable
(
ID INT NOT NULL
IDENTITY ,
RandomValue INT NOT NULL ,
FILLER CHAR(1000) ,
CONSTRAINT PK_WideTable PRIMARY KEY ( RandomValue, ID )
) ;
GO
-- inserts 10,000 rows
INSERT INTO dbo.WideTable(RandomValue, Filler)
SELECT Number, 'just some chars'
FROM data.Numbers
GO
CREATE VIEW dbo.WrappedRandView
AS
SELECT RAND() AS RandomValue ;
GO
CREATE FUNCTION dbo.RandomInt ( @Multiplier INT )
RETURNS INT
AS
BEGIN
DECLARE @ret INT ;
SET @ret = ( SELECT CAST(RandomValue * @Multiplier AS INT)
FROM dbo.WrappedRandView
) ;
RETURN @ret ;
END
Running repro script
In one tab, we shall be adding 10 rows at a time, 10000 times:
SET NOCOUNT ON ;
DECLARE @randomInts TABLE ( randomInt INT ) ;
DECLARE @numIterations INT ,
@iterationNumber INT ;
SET @numIterations = 10000 ;
SET @iterationNumber = 1 ;
WHILE @iterationNumber <= @numIterations
BEGIN
INSERT INTO @randomInts
( randomInt
)
SELECT dbo.RandomInt(10000)
FROM Data.Numbers
WHERE Number < 10 ;
INSERT dbo.WideTable
( RandomValue ,
FILLER
)
SELECT randomInt ,
'some chars'
FROM @randomInts ;
DELETE FROM @randomInts ;
SET @iterationNumber = @iterationNumber + 1 ;
END ;
END
In another tab, COUNT(*) should always return a multiple of 10, but the select at the bottom returns all COUNT(*) that are not multiples of 10:
SET NOCOUNT ON ;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
DECLARE @counts TABLE ( cnt INT ) ;
DECLARE @numIterations INT ,
@iterationNumber INT ;
SET @numIterations = 1000 ;
SET @iterationNumber = 1 ;
WHILE @iterationNumber <= @numIterations
BEGIN
INSERT INTO @counts
( cnt )
SELECT COUNT(*)
FROM dbo.WideTable ;
SET @iterationNumber = @iterationNumber + 1 ;
END ;
SELECT *
FROM @counts
WHERE cnt % 10 > 0 ;
However, when I ran the script above I got hundreds of incorrect results:
38763
38862
38947
39056
39102
39142
(snip)
108909
109129
109315
109558
109676
109786
109888
Overall, 755 times out of 10000 the COUNT(*) results were not multiples of 10.
Of course, every time time we run this repro script, we shall be getting somewhat different results, but we should still frequently get wrong totals.