SQL Server Performance Forum – Threads Archive

Problem with 2000 SP in 2005

I have this stored procedure that simulates paging. It works fine in SQL server 2000 but today I moved the database to a different server running sql express 2005. Everything works fine except for this SP. It is supposed to Order By HitDate DESC but returns the records in acending order. Here is the procedure: CREATE PROCEDURE sp_Journal_Hits_FillRange (
@AbsolutePage INT=1,
@PageSize INT=10,
@RecordCount int output
) AS SET NOCOUNT ON — create temporary table
CREATE TABLE #TempTable
(ID INT IDENTITY, HitID bigint, HitDate datetime, IPAddress varchar(16), ReverseLookup varchar(100), ISP varchar(50), VisitLength bigint, VName varchar(50)) — fill temporary table
INSERT INTO #TempTable (HitID, HitDate, IPAddress, ReverseLookup, ISP, VisitLength, VName)
(SELECT * FROM (SELECT TOP 100 PERCENT HitID, HitDate, IPAddress, ReverseLookup, ISP, VisitLength=ISNULL(VisitLength,0), VName=ISNULL(VName,’ ‘) FROM Journal_Hits ORDER BY HitDate DESC) DERIVEDTBL) — set the paging sizes
DECLARE @FirstRec INT, @LastRec INT
SELECT @FirstRec = (@AbsolutePage – 1) * @PageSize + 1
SELECT @LastRec = (@AbsolutePage * @PageSize) — return requested records
SELECT * FROM #TempTable WHERE ID Between @FirstRec AND @LastRec SELECT @RecordCount=(SELECT COUNT(*) FROM Journal_Hits)
GO
Does anyone know why it would do this? Or know of alternative method to simulate paging?

---

Do not double-post.

---

Just add order by id in select * from #tempTable and/or make id column clustered primary key of the table variable. Primary key on table variable theoretically doesn’t guarantee order but so far I always got the desirable order. Adding order by clause guarantees the order.

---

]]>