Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 top 1 column on heap

Author  Topic 

Kimi86
Yak Posting Veteran

79 Posts

Posted - 2014-08-13 : 13:20:38
Hi People,
I have a table which does not have a primary key on it.. It does have an identity column which is the first columnm called rowid.. When i do a
select top 1 * from this table every time i get the same row. I am wondering what logic does the query uses to find out which is the top row .. Every time I get the row with lowest rowid.. Is that how this will always work??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-13 : 13:36:27
It just grabs whatever row it returns first. And no this is not how it will always work. You need to add an ORDER BY to guarantee which row you'll receive with TOP 1.

Having a PK or not has nothing to do with the behavior, but you should add a primary key to that id column.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-13 : 13:37:01
Like this:

select top 1 *
from yourtable
order by idcolumn asc

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Kimi86
Yak Posting Veteran

79 Posts

Posted - 2014-08-13 : 15:36:17
ok.. i thought so.. but do u know the reason why every time i run the query i get the same results.. this code is in production and i have never seen it bring anything else.. is the data cached or something
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-13 : 15:47:59
quote:
Originally posted by Kimi86

ok.. i thought so.. but do u know the reason why every time i run the query i get the same results.. this code is in production and i have never seen it bring anything else.. is the data cached or something



Yes data is cached, but what's cached is likely more than just the one row. What gets cached is pages. Typically lots of rows fit onto a page. And SQL retrieves 8 pages at a time, thinking you might want more than just that 1 page.

Regardless if you return that one row 12 billion times, it is not guaranteed to continue returning that row. The only way to guarantee it is to add the appropriate ORDER BY. Period. End of story.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Kimi86
Yak Posting Veteran

79 Posts

Posted - 2014-08-13 : 15:48:58
Thanks so much again..:)
Go to Top of Page
   

- Advertisement -