This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(284.8, 73%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJH%3C/text%3E%3C/svg%3E)
T-SQL SELECT table returns different result. Just Select top 1 * from tablename
It returns the latest row. However, when DB is under READ-ONLY mode, the same command returns the oldest one. It happens in SSMS only, SSMS version 18.10. SELECT COUNT(*) behaves correctly though. It seems only SSMS is behaving strangely. SQLCMD. SSRS and apps works fine.
Why the return result is different only when I enable readyonly for this DB?
Technically, it is because when the database is readonly, SQL Server can use an IAM (IAM = Index Allocation Map) scan instead of following the clustered index.
But the question is kind of funny. "Why do you sometimes give me the red one and sometimes when I have said that I don't care which one I get?".
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
If you do not use ORDER BY, your order is "unknown" and random. So TOP 1 will give random results.
See:
https://learn.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-ver15
Limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server. When you use TOP with the ORDER BY clause, the result set is limited to the first N number of ordered rows. Otherwise, TOP returns the first N number of rows in an undefined order.
Try adding the corresponding 'ORDER BY DateColumn DESC'.
Hi @Jason Harris ,
In a SELECT statement, always use an ORDER BY clause with the TOP clause. Because, it's the only way to predictably indicate which rows are affected by TOP.
Regards，
Zoe
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Why the return result is different only when I enable readyonly for this DB?
Why the return result is different only when I enable readyonly for this DB?
Has nothing to do with the database state, as the others already wrote, without an explicit ORDER BY clause you get a random result; that's by design.
Why the return result is different only when I enable readyonly for this DB?
Did you consider all of the changes in order to prove the unique effect of 'readonly'?
I found an interesting Microsoft blog.
https://techcommunity.microsoft.com/t5/sql-server-blog/when-can-allocation-order-scans-be-used/ba-p/383180
It think it uses allocation order scan when I run select in read only mode.
Yes, that is what I alluded to in my comment.
But the key is really that when you say TOP 1 without ORDER BY that means "Just give me the first one you can get hold of, I don't care which". It is is not random, but nor is it deterministic.