SQL Server Performance

Problem with 2000 SP in 2005

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by rushonerok, Feb 14, 2006.

  1. rushonerok New Member

    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


    -- 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)

    Does anyone know why it would do this? Or know of alternative method to simulate paging?
  2. Adriaan New Member

    Do not double-post.
  3. mmarovic Active Member

    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.

Share This Page