patternsqlMinor

Changing Query Results?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

changingqueryresults

Problem

I have a query that I am running and I just noticed that every time I run it, it is returning a different amount of records (usually less by 1 or 2 rows). My question is how can this be possible? I am running the same exact query (just pressing f5 on SQL server 2008) and the number of rows returned changes.

1 reason might be that someone is updating the tables right now in the database and that is the reason my query results are also changing but I am not aware of anyone doing any updates during regular work hours, as the updates are usually done after hours.

Would the tables being updated be the only possible reason for the query results to be changing or could it be something else?

If it is the fact that someone is running an update on the database, is there some code/command I can put in my query to at least make sure the query is looking at the same data when it starts running at the begging of execution as it is in the middle and ending of the query execution, so that the data is not changing on me at least during query execution, kind of like a LOCK?

Below is the query code:

```
declare @date as date
set @date = '03-01-2012'

select DATEPART(MONTH,@date) as Month_Opened,DATEPART(year,@date) as Year_Opened,isnull(t1.StateCode, t2.StateCode) StateCode,isnull(t1.CoverageCode, t2.CoverageCode) coveragecode,sum(t1.#_of_Claims)#_of_Claims,sum(t2.EarnedExposures) Earned_Exposures,
isnull(t1.Multicar, t2.Multicar) Multicar, isnull(t1.DecpageTypeCode, t2.DecpageTypeCode) Policy_Type,
isnull(t1.VehiclePoints, t2.VehiclePoints) VehiclePoints,isnull(t1.VehicleClassCode, t2.VehicleClassCode) VehicleClassCode,isnull(t1.ModelYear, t2.ModelYear) ModelYear,
isnull(t1.RatingTier, t2.RatingTier) RatingTier,isnull(t1.PriorCoverage, t2.PriorCoverage)PriorCoverage

from

(

select table1.DecpageID,table1.PolicyID,table1.Risk,Year_Opened,Month_Opened,table1.StateCode,CoverageCode,COUNT(claims) #_of_Claims,
Multicar, AgentCode,DecpageTypeCode,VehicleClassCode,ModelYear,RatingT

Solution

If you switch to snapshot isolation, this effect should be gone. The following repro script 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  0 ;

However, when I ran the script above I got hundreds of incorrect results:

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.

Edit: the explanation is quite simple: although a select running under REPEATABLE READ does acquire a range lock on the rows it has read, this range lock does not prevent inserting new rows. As such, when some of the 10 new rows get inserted into the pages which have already been read by the select, the do not get counted.

Code Snippets

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

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

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 ;

Context

StackExchange Database Administrators Q#17175, answer score: 3

Revisions (0)

No revisions yet.