Paging i SQL med stored procedure

Paging er å presentere en datamengde over flere sider. Dersom du har en tabell med 1000 rader er det lite hensiktsmessig å liste alle radene. Det er da man trenger paging, et script som lar deg presentere feks. 10 og 10 rader.

I dette eksempelet skal vi bruke en metode som fungerer både med SQL Server 2000 og 2005. Dette er en metode du enkelt kan tilpasse til bruk på andre tabeller enn dette eksempelet. Vi skal bruke temp-tabeller og det er alltid en diskusjon rundt dette når det kommer til ytelse. Det er gjort mange forsøk og tester med dette som viser at temp-tabeller faktisk i mange tilfeller er langt bedre rent ytelsesmessig enn andre metoder (dersom man bruker det riktig).

Vi trenger en enkel tabell med data. I dette eksempelet lager vi en tabell med 3 kolonner: BrukerIdNavn og Email. For å gjøre det enkelt har jeg laget et script som lager tabellen og putter inn litt testdata:

CREATE TABLE Brukere
(
   BrukerId int IDENTITY(1,1) NOT NULL,
   Navn varchar(255) NOT NULL,
   Email varchar(255) NOT NULL
)  

Du finner et helt script med testdata her. Når det scriptet er kjørt har har du tabellen og 50 navn med mailadresse.

Da kan vi lage selve proceduren som foretar paging i dataene våre. Jeg kaller denne proceduren GetBrukere og den trenger 2 input-parametre for å fungere. Disse to er naturligvis PageSize og PageNumber som angir hvor mange rader vi vil har pr side og hvilken side vi vil ha ut.

CREATE PROCEDURE GetBrukere
(
 @PageNumber int,
 @PageSize int
)
AS
BEGIN

 SET NOCOUNT ON;
 DECLARE @Top int
 SET @Top = @PageSize * @PageNumber

 CREATE TABLE #TempBrukere 
   (Id int IDENTITY(1,1),BrukerId int)

 INSERT INTO #TempBrukere (BrukerId)
 EXECUTE ('SELECT TOP ' + @Top + ' BrukerId FROM Brukere')

 DECLARE @startRec int, @endRec int
 SET @startRec = (@PageNumber - 1) * @PageSize
 SET @endRec = (@PageNumber * @PageSize + 1)

 SELECT #TempBrukere.BrukerId,Brukere.Navn,Brukere.Email
 FROM #TempBrukere INNER JOIN Brukere 
   ON #TempBrukere.BrukerId = Brukere.BrukerId
 WHERE (#TempBrukere.Id > @startRec) 
   AND (#TempBrukere.Id < @endRec)

 DROP TABLE #TempBrukere

END

Det er flere ting som er verdt å merke seg her. Spesielt vil jeg understreke viktigheten av å ikke lage for store temporære tabeller. Dersom det er en tabell med mye data (ikke navn og email, men kanskje lengre tekster) så er det vikitg å unngå kopiering av all data. I dette eksempelet lages det en temporær tabell, men det er kun BrukerId som lagres i temp-tabellen og deretter gjøres det en INNER JOIN for å ta ut de dataene man spør etter. Husk å slette temp-tabellen etter bruk.

Dette er et meget enkelt eksempel og det er enkelt å utvide. Man bør feks legge inn mulighet for å sende inn parametre som sier noe om sortering osv.

Samme metode brukes også i det komplette kodeeksempelet Komplett gjestebok prosjekt for Visual Studio 2005 med SQL Server 2005 database