Data Paging Issue with SQL Server 2008
-
8 июня 2012 г. 16:57
Hi,
We are upgrading the Sql Server 2005 to 2008 and during the regression testing we found there is a mismatch with order of the record selected.
We have a SP say USP_getData, which creates local temp table to hold the records selected from multiple queries and selects the rows from that table at the end. There are no indexes on the temp table. hence the order of the data selected should be the order of the data.
The temp table has around 200 records. table has a identity column to record the order the rows are inserted in to it.
when I check the page id details for the row, from row 8, it goes to different page and after 90 records it uses the old page.
Below given is the Physical RID ( FileId:PageId:Slot) and the identify column.
(Physical RId) Identity
(1:219:0) 1
(1:219:1) 2
(1:219:2) 3
(1:219:3) 4
(1:219:4) 5
(1:219:5) 6
(1:219:6) 7
(1:219:7) 92
(1:219:8) 93
(1:219:9) 94
(1:219:10) 95
(1:219:11) 96
(1:219:12) 97
(1:219:13) 98
(1:219:14) 99(1:176504:0) 8
(1:176504:1) 9
(1:176504:2) 10
(1:176504:3) 11
(1:176504:4) 12
(1:176504:5) 13
(1:176504:6) 14
(1:176504:7) 15
(1:176504:8) 16
(1:176504:9) 17
(1:176504:10) 18Another thing observed is, when perm tables are used instead of local temp table, the PageId and Identity are in order.
For local temp tables, selection of page id depends on space availability in the temp db. but I executed the same SP in different sessions and many times and it is still the case.
Would appreciate if you could advise on this .
thanks
- Перемещено Tom PhillipsModerator 8 июня 2012 г. 17:34 TSQL question (From:SQL Server Database Engine)
Все ответы
-
8 июня 2012 г. 17:14
In SQL you can NEVER depend on row order unless you have an order-by clause.
That's it.
Josh
- Предложено в качестве ответа Naomi NMicrosoft Community Contributor, Moderator 8 июня 2012 г. 17:37
- Помечено в качестве ответа Iric WenModerator 18 июня 2012 г. 1:33
-
8 июня 2012 г. 17:33Модератор
As Josh said, if you do not use ORDER BY the order of the results are "undefined" and cannot be guaranteed. If you need the rows in a specific order, you must ORDER BY. It is only coincidence it was working before.
-
8 июня 2012 г. 17:37МодераторAs the others have stated a table does not have an order. The query plan is different betweent the versions of SQL Server which is why it worked on the prior version (by coincidence). You have to add a order by clause to your query to ensure the data is returned in a consistent order.
-
9 июня 2012 г. 6:04
As others have mentioned, there is no guarantee that rows will be returned in the same order for the same query without an ORDER BY clause. Demonstrated here -
http://blog.sqlauthority.com/2008/11/24/sql-server-interesting-observation-about-order-of-resultset-without-order-by/
Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com) -
9 июня 2012 г. 9:23
There's a sliver of a chance the link in the first post of this thread might help: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/23561fd3-ee35-45f2-9821-ec94748986b1. In other words, it may not be the temp table causing your problem, it may be that it's populated by an ordered view, which might by addressable by the hotfix.
It's a long shot, but worth investigating. However, it's still just not a good idea to depend on assumed behaviors like this. If whoever wrote this in the first place had used order by like they should have, you wouldn't be having this issue now!
.png)
