For Erp version upgrade we copied our database and restored to new one. We altered tables. (Added new columns or changed Width etc. We dropped indexes and recreated). After all we tested new database, caught a situation like this.
We have a query like
select top 5 * from table.Old database: when we run the query continuously, result order not changing
New database: when we run, order sequence of results changing.
I think there is a clustered index issue but how can I determine and solve it. Any help?
2条答案
按热度按时间l0oc07j21#
Order is not guaranteed unless you use order by.
Old database may have clustered index created on table. Please check it. But even clustered index does not always guarantee ordering.
jvlzgdj92#
If you don't use the ORDER BY statement after the TOP statement the database engine does not guarantee the order of the resultset.
SQL Server wants to return qualified rows quickly in some cases and this mechanism called Row Goals and most probably your query execution plan uses this mechanism. On the other hand, the clustered index operator includes an ordered attribute and this shows us the retrieved rows are fetched in the ordered manner or not.