Possible Bug Using TOP and Paging via a Temp Table

This is not a "bug" per se, as I know that the order of rows in SQL
Server isn't guaranteed to be consistent unless ORDER BY is specified.
Still, this is somewhat odd behavior.
This involves paging logic via parameterized queries. Since I can't
post my client's DDL, I have used northwind to duplicate the issue.
Code:
--vars to simulate paging
DECLARE
@.start int,
@.end int
--create temp table
CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT
NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT
NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT
NULL, [Orderdate] [DateTime] NOT NULL)
SELECT @.start = 0 /*****CHANGE ME*****/
SELECT @.end = 12 /*****CHANGE ME*****/
--insert rows
INSERT INTO #TempTable (CustomerID, ShipVia, ShipName,
ShipAddress,Orderdate)
SELECT DISTINCT
TOP 12 /*****CHANGE ME*****/
CustomerID,
ShipVia,
ShipName,
ShipAddress,
Orderdate
FROM
ORDERS
WHERE
CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
ORDER BY
CustomerId
--select the page of data
SELECT * FROM #TempTable WHERE __rowcnt > @.start AND __rowcnt <= @.end
--select to see the whole temp table
--SELECT * FROM #TempTable
DROP TABLE #TempTable
1. Run the query as is. Notice that there are 3 rows returned where
ShipVia = 2.
2. Change @.start to 12, @.end to 24, and the TOP statement to be "TOP
24". Notice that although the __rowcnt selection correctly returns
13-24, the rows are the same.
3. Change @.start to 24, @.end to 36, and the TOP statement to be "TOP
36". The page of data now changes (I believe because the customerID
changes on this page).
4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages
of data returned are now "correct"--the data returned is different for
each page.
You can uncomment the final select statement to see what is actually
going into the temp table.
It seems that the TOP statement is somehow causing the rows to be added
to the temp table in reverse order. Apparently, the "correct" rows are
returned using an unpatched version of SQL Server.
Anyone know what might cause this to happen?
Thanks for any insight,
PhilHi
Add a few 100 thousand rows to this, plus a machine with 4 processors and a
lot of RAM and the query performs different again. Even a different OS.
As the row count in your example increases, there is probably a different
query plan/spool to disk occurring.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<psandler70@.hotmail.com> wrote in message
news:1135119108.603421.214770@.z14g2000cwz.googlegroups.com...
> This is not a "bug" per se, as I know that the order of rows in SQL
> Server isn't guaranteed to be consistent unless ORDER BY is specified.
> Still, this is somewhat odd behavior.
> This involves paging logic via parameterized queries. Since I can't
> post my client's DDL, I have used northwind to duplicate the issue.
> Code:
> --vars to simulate paging
> DECLARE
> @.start int,
> @.end int
> --create temp table
> CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT
> NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT
> NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT
> NULL, [Orderdate] [DateTime] NOT NULL)
> SELECT @.start = 0 /*****CHANGE ME*****/
> SELECT @.end = 12 /*****CHANGE ME*****/
> --insert rows
> INSERT INTO #TempTable (CustomerID, ShipVia, ShipName,
> ShipAddress,Orderdate)
> SELECT DISTINCT
> TOP 12 /*****CHANGE ME*****/
> CustomerID,
> ShipVia,
> ShipName,
> ShipAddress,
> Orderdate
> FROM
> ORDERS
> WHERE
> CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
> ORDER BY
> CustomerId
> --select the page of data
> SELECT * FROM #TempTable WHERE __rowcnt > @.start AND __rowcnt <= @.end
> --select to see the whole temp table
> --SELECT * FROM #TempTable
> DROP TABLE #TempTable
>
> 1. Run the query as is. Notice that there are 3 rows returned where
> ShipVia = 2.
> 2. Change @.start to 12, @.end to 24, and the TOP statement to be "TOP
> 24". Notice that although the __rowcnt selection correctly returns
> 13-24, the rows are the same.
> 3. Change @.start to 24, @.end to 36, and the TOP statement to be "TOP
> 36". The page of data now changes (I believe because the customerID
> changes on this page).
> 4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages
> of data returned are now "correct"--the data returned is different for
> each page.
> You can uncomment the final select statement to see what is actually
> going into the temp table.
> It seems that the TOP statement is somehow causing the rows to be added
> to the temp table in reverse order. Apparently, the "correct" rows are
> returned using an unpatched version of SQL Server.
> Anyone know what might cause this to happen?
> Thanks for any insight,
> Phil
>|||There are some better approaches to paging that don't exhibit these
symptoms.
http://www.aspfaq.com/2120
<psandler70@.hotmail.com> wrote in message
news:1135119108.603421.214770@.z14g2000cwz.googlegroups.com...
> This is not a "bug" per se, as I know that the order of rows in SQL
> Server isn't guaranteed to be consistent unless ORDER BY is specified.
> Still, this is somewhat odd behavior.
> This involves paging logic via parameterized queries. Since I can't
> post my client's DDL, I have used northwind to duplicate the issue.
> Code:
> --vars to simulate paging
> DECLARE
> @.start int,
> @.end int
> --create temp table
> CREATE TABLE #TempTable ([__rowcnt][int] IDENTITY (1,1) NOT
> NULL,[CustomerId][varchar](50) NOT NULL, [ShipVia][Int] NOT
> NULL,[ShipName][VarChar](50) NOT NULL,[ShipAddress][varchar](50) NOT
> NULL, [Orderdate] [DateTime] NOT NULL)
> SELECT @.start = 0 /*****CHANGE ME*****/
> SELECT @.end = 12 /*****CHANGE ME*****/
> --insert rows
> INSERT INTO #TempTable (CustomerID, ShipVia, ShipName,
> ShipAddress,Orderdate)
> SELECT DISTINCT
> TOP 12 /*****CHANGE ME*****/
> CustomerID,
> ShipVia,
> ShipName,
> ShipAddress,
> Orderdate
> FROM
> ORDERS
> WHERE
> CustomerId IN ('SAVEA', 'ERNSH', 'QUICK')
> ORDER BY
> CustomerId
> --select the page of data
> SELECT * FROM #TempTable WHERE __rowcnt > @.start AND __rowcnt <= @.end
> --select to see the whole temp table
> --SELECT * FROM #TempTable
> DROP TABLE #TempTable
>
> 1. Run the query as is. Notice that there are 3 rows returned where
> ShipVia = 2.
> 2. Change @.start to 12, @.end to 24, and the TOP statement to be "TOP
> 24". Notice that although the __rowcnt selection correctly returns
> 13-24, the rows are the same.
> 3. Change @.start to 24, @.end to 36, and the TOP statement to be "TOP
> 36". The page of data now changes (I believe because the customerID
> changes on this page).
> 4. Comment out the TOP statement and re-do steps 1, 2, and 3. The pages
> of data returned are now "correct"--the data returned is different for
> each page.
> You can uncomment the final select statement to see what is actually
> going into the temp table.
> It seems that the TOP statement is somehow causing the rows to be added
> to the temp table in reverse order. Apparently, the "correct" rows are
> returned using an unpatched version of SQL Server.
> Anyone know what might cause this to happen?
> Thanks for any insight,
> Phil
>