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.
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-08-13 : 13:37:01
|
| Like this:select top 1 * from yourtableorder by idcolumn ascTara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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 |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Kimi86
Yak Posting Veteran
79 Posts |
Posted - 2014-08-13 : 15:48:58
|
| Thanks so much again..:) |
|
|
|
|
|
|
top 1 column on heap