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 



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



select   ClaimNumber,DecpageID,PolicyID,Risk,StateCode,YEAR(firstdateopened) as  Year_Opened, MONTH(FirstDateOpened) as  Month_Opened,CoverageCode,ClaimNumber claims from


select StateCode,DecpageID,PolicyID,atable.Risk,
atable.DateOpened,atable.ClaimID1,atable.ClaimID2,claimnumber, atable.claimant, atable.statuscode,CoverageCode,atable.CovExaminer, Loss_Paid,Expense_Paid, 
AccidentDate Loss_Date, reportdate Report_Date, dateclosed Date_Closed, claims.statuscode ClaimsTableStatusCode from 
select --MIN (h.TransactionDate) First_Date_Opened,
DecpageID,PolicyID,Risk,DateOpened,AccidentDate,CovExaminer,agentcode, statecode, statclaimdate,c.claimid1,c.claimid2,c.claimant,
sum(ClaimPayment) as Loss_Paid,SUM(laepayment) as Expense_Paid,claimnumber, reportdate, c.statuscode 
from statclaims as c inner join statclaimhistory as h   
on c.claimid1 = h.claimid1 and c.claimid2 = h.claimid2 and c.claimant = h.claimant  
where statclaimdate = '4/26/2012'   
--and StateCode = 3 and agentcode ='3002'
c.Claimant <> '99'  
and IsNull(CoverageCode,'') <> ''   
and c.StatusCode <> 'E' 
group by DateOpened,CovExaminer,AccidentDate,DecpageID,PolicyID,Risk,
statclaimdate, claimnumber, reportdate, c.claimant, c.claimid1, c.claimid2, agentcode, statecode, c.statuscode
) as atable join isdata15sql.dbo.claims as claims on atable.claimid1 = claims.claimid1 and atable.claimid2 = claims.claimid2 and atable.claimant = claims.claimant 
where atable.DateOpened between '1-1-2011' and '4-12-2012'-- and CoverageCode = 'PIP'

) as t1 join

(select MIN(transactiondate) FirstDateOpened,ClaimID1,ClaimID2 from StatClaimHistory
where ClaimID1 in ('2011','2012')
group by ClaimID1,ClaimID2
as t2 on t1.ClaimID1 = t2.ClaimID1 and t1.ClaimID2 = t2.ClaimID2

) as table1 left join StatRiskDecpages s on table1.PolicyID= s.PolicyID and table1.DecpageID= s.DecpageID and table1.Risk = s.Risk

where --CoverageCode = 'coll' and 
table1.StateCode = 9 and Year_Opened=DATEPART(year,@date) and Month_Opened=DATEPART(month,@date)

and CoverageCode not in ('rental','towing','pu')
group by Year_Opened,Month_Opened,CoverageCode,table1.StateCode,table1.DecpageID,table1.PolicyID,table1.Risk,Multicar,AgentCode,


as t1 full outer join

--sum(EarnedPremium) as EarnedPremium, 
sum(EarnedExposure) as EarnedExposures,s.DecpageID,s.Risk,s.PolicyID,CoverageCode,StateCode,

from StatRiskPrem_udf(@date,CONVERT(DATETIME, DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,CONVERT(DATETIME, DATEADD(M,+1,DATEADD(mm, DATEDIFF(m,0,@date),0)), 102)),0)), 102),'Y','Y','') as u            
inner join StatRiskDetails as d         
on u.PolicyID = d.PolicyID and u.Risk = d.Risk and u.InvDetailNum = d.InvDetailNum          
inner join StatRiskDecpages as s            
on d.PolicyID = s.PolicyID and d.DecpageID = s.DecpageID and d.Risk = s.Risk
where StateCode = 9 and CoverageCode not in('towing','rental','pu')
Group by StateCode,s.DecpageID,s.Risk,s.PolicyID,CoverageCode, Multicar,AgentCode,DecpageTypeCode,Symbol,VehicleClassCode,ModelYear,RatingTier,PriorCoverage,

) as t2 on t1.PolicyID = t2.PolicyID and t1.DecpageID = t2.DecpageID and t1.Risk = t2.Risk and t1.CoverageCode = t2.CoverageCode

WHERE NOT(#_of_Claims is null AND earnedexposures=0)

group by isnull(t1.CoverageCode, t2.CoverageCode),isnull(t1.StateCode, t2.StateCode),isnull(t1.Multicar, t2.Multicar),isnull(t1.DecpageTypeCode, t2.DecpageTypeCode),
isnull(t1.VehiclePoints, t2.VehiclePoints),isnull(t1.VehicleClassCode, t2.VehicleClassCode),isnull(t1.ModelYear, t2.ModelYear),isnull(t1.RatingTier, t2.RatingTier),
isnull(t1.PriorCoverage, t2.PriorCoverage)
  • Can you show us the query you are running?, this could be for a number of reasons
    – Lamak
    Apr 27, 2012 at 19:20
  • If that would help then yes I will add the code but the query is pretty big has a lot of code and might be a litle bit messy. I just noticed that the number of rows can go up or down by 1 or 2.... So it could return 33938 rows and the when i run the query again it will return 33937 and if i run it again it might return 33939 and so forth. And i'ts just a regular select query ... nothing to complicated, just a lot of code
    – Juan Velez
    Apr 27, 2012 at 19:25
  • I don't see any non-deterministic function used (like GETDATE() or RAND() that could be responsible for such behaviour. But you have a StatRiskPrem_udf() function. Does the result of that function depend on something other than tables' data? Does it have a call to a non-deterministic function? Apr 27, 2012 at 19:50

1 Answer 1


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.


We need a table with data and a function that provides random integers:

CREATE TABLE dbo.WideTable
             IDENTITY ,
      RandomValue INT NOT NULL ,
      FILLER CHAR(1000) ,
      CONSTRAINT PK_WideTable PRIMARY KEY ( RandomValue, ID )
    ) ;

-- inserts 10,000 rows
INSERT INTO dbo.WideTable(RandomValue, Filler)
SELECT Number, 'just some chars'
FROM data.Numbers

CREATE VIEW dbo.WrappedRandView
SELECT RAND() AS RandomValue ;

CREATE FUNCTION dbo.RandomInt ( @Multiplier INT )
        DECLARE @ret INT ;
        SET @ret = ( SELECT CAST(RandomValue * @Multiplier AS INT)
                     FROM   dbo.WrappedRandView
                   ) ;
        RETURN @ret ;

Running repro script

In one tab, we shall be adding 10 rows at a time, 10000 times:
DECLARE @randomInts TABLE ( randomInt INT ) ;
DECLARE @numIterations INT ,
    @iterationNumber INT ;

SET @numIterations = 10000 ;
SET @iterationNumber = 1 ;
WHILE @iterationNumber <= @numIterations 
        INSERT  INTO @randomInts
                ( randomInt 
                SELECT  dbo.RandomInt(10000)
                FROM    Data.Numbers
                WHERE   Number < 10 ;
        INSERT  dbo.WideTable
                ( RandomValue ,
                SELECT  randomInt ,
                        'some chars'
                FROM    @randomInts ;

        DELETE  FROM @randomInts ;
        SET @iterationNumber = @iterationNumber + 1 ;                                        
    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:


DECLARE @counts TABLE ( cnt INT ) ;
DECLARE @numIterations INT ,
    @iterationNumber INT ;

SET @numIterations = 1000 ;
SET @iterationNumber = 1 ;

WHILE @iterationNumber <= @numIterations 
        INSERT  INTO @counts
                ( cnt )
                SELECT  COUNT(*)
                FROM    dbo.WideTable ;
        SET @iterationNumber = @iterationNumber + 1 ;                                        
    END ;

FROM    @counts
WHERE   cnt % 10 > 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.

  • Has the cause of this behaviour been explained? I remember your original blog post with this repro but don't recall seeing any follow up that explained how or why it occurs. Apr 29, 2012 at 14:29
  • @MarkStorey-Smith I have just added the explanation.
    – A-K
    May 2, 2012 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.