SQL Server Paging

1. Implement paging using Table Variables

Declare @ start int -- start query location

Declare @ length int -- Query Length

Set @ start = 20 -- set to start from 20th

Set @ length = 20 -- set the length to 20

/*

Declare table variables to generate numbers

*/

DECLARE @IndexTable TABLE

(

N_ID int identity,

C_ID int not null

)

-- Generate ID

INSERT INTO @IndexTable 

SELECT OrderID FROM Orders

-- Search for the desired content

SELECT * FROM Orders,@IndexTable

WHERE 

C_ID=OrderID

AND

N_ID>@Start AND N_ID<=@Start+@Length