T_SQL SELECT table returns different result

Jason Harris 21 Reputation points
2022-02-01T09:07:59.057+00:00

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.

Developer technologies | Transact-SQL
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2022-02-02T11:44:18.277+00:00

    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?".

    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Tom Phillips 17,771 Reputation points
    2022-02-01T12:48:39.897+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

  2. Viorel 122.6K Reputation points
    2022-02-01T09:11:07.393+00:00

    Try adding the corresponding 'ORDER BY DateColumn DESC'.

    0 comments No comments

  3. ZoeHui-MSFT 41,491 Reputation points
    2022-02-02T07:13:36.743+00:00

    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.


  4. Jason Harris 21 Reputation points
    2022-02-03T08:39:37.73+00:00

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.