CODEWITHSUNDEEP
t-sql
Ninjanoel
Ninjanoel

Reputation: 2914

'select top' returns too many rows

insert into
    @resultSet
SELECT TOP (@topN)  
    field1,
    field2
FROM    
    dbo.table1 DataLog  
WHERE           
    DataLog.SelectedForProcessing is null

I'm passing 300 into @topN in the above sql, a value I've got configured in my app.config file, but this query running on 2 different servers has returned 304 rows in one instance and 307 rows in another instance.

I cant find anywhere that may be interfering with the 300, to turn it into 304 or 307, so I'm beginning to wonder whether SQL Server will just return a few extra rows sometimes? (Same code on another server IS returning the expected 300 rows)

Is this expected behaviour?

Upvotes: 0

Views: 322

Answers (2)

paparazzo
paparazzo

Reputation: 45096

Test this

declare @topN int = 100;
select @topN ;
delete * from @resultSet;
insert into
    @resultSet
SELECT TOP (@topN)  
    field1,
    field2
FROM    
    dbo.table1 DataLog  
WHERE           
    DataLog.SelectedForProcessing is null;
select count(*) 
FROM    
dbo.table1 DataLog  
WHERE           
    DataLog.SelectedForProcessing is null;
select count(*) from @resultSet;

Upvotes: 2

3BK
3BK

Reputation: 1348

SQL Server will consistently return TOP N rows when N is a constant value - no wiggle room there.

I see two possibilities:

  1. @topN is getting a different value on occasion
  2. @resultSet is somehow not empty before having new values inserted

If @resultSet is a variable declared elsewhere in your scripts, check to see that no other INSERT INTO statements might be leaving unnecessary rows.

One easy way to implement this in run-time would be to simply add another command before this INSERT INTO statement:

DELETE @resultSet;
INSERT INTO
    @resultSet
SELECT TOP (@topN)  
    field1,
    field2
FROM    
    dbo.table1 DataLog  
WHERE           
    DataLog.SelectedForProcessing IS NULL
;

Upvotes: 1

Related Questions