• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

TOP and ROWCOUNT return different record sets

I have a query which can be written in two ways

Please refer the code snippet for Scenario 1 and 2

Both these scenarios are returning different record sets. Is this an expected behaviour?

Scenario 1
 
SELECT TOP(50) * from table order by column1 ASC
 
Scenario 2 
 
SET ROWCOUNT=50
SELECT * from table order by column1 ASC

Open in new window

0
Kanchipuramdeena
Asked:
Kanchipuramdeena
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
If you dont have proper order by clause then there can be discrepancies.

But it should return same set of result set provided same set of order by clause.
And one more thing,

SET ROWCOUNT=50

Above is incorrect and it should be

SET ROWCOUNT 50

Kindly try this one and provide us sample result set to disprove it.
0
 
KanchipuramdeenaAuthor Commented:
Sorry SET ROWCOUNT=50 was a typo

I am trying SET ROWCOUNT 50 only

The different queries are

SELECT TOP(50) * from table order by TimeStamp ASC

Take a note of ID value of the 50 row
for me its - {E17FF1A4-45CA-4CD8-A309-0D159403B021}
the execute

SET ROWCOUNT 50
SELECT * from table order by TimeStamp ASC

Now look at the value of ID at the 50th row
for me its - {E295779E-4D6E-4801-8628-8A3ABB4F767E}


dat.txt
0
 
Patrick MatthewsCommented:
Hello Kanchipuramdeena,

Try running these, and see if you get identical results:

SET ROWCOUNT 50
SELECT * from table order by TimeStamp ASC, ID ASC

SELECT TOP(50) * from table order by TimeStamp ASC, ID ASC

Regards,

Patrick
0
 
KanchipuramdeenaAuthor Commented:
That's Great.

I was confused a bit... know i am clear...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now