Does anyone have efficient SQL paging code?

• isuckatsql

  SSChampion

  Points: 10381

  More actions

  May 16, 2014 at 2:14 pm

  #307467

  I have the code below, but it seems to be a little slower than i would like, even though the SQL DETA did not find anything wrong with it.

  Thanks

  SELECT

  id,

  title,

  companyname,

  did,

  begindate,

  description,

  city,

  state,

  city + ', ' + state as Location,

  employmenttype,

  degree,

  PAYLOW,PAYHIGH,payper,

  (select count(*) from results) totalcount

  FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY BeginDate desc) R1 FROM results) T

  WHERE R1 BETWEEN 0 AND 9

  OPTION(Maxdop 8)

  End

• Sean Pearce

  SSCoach

  Points: 15999

  More actions

  May 19, 2014 at 8:13 am

  #1714650

  DECLARE

  @NumberOfRows INT = 10,

  @PageNumber INT = 2;

  SELECT

  name

  FROM

  sys.tables

  ORDER BY

  object_id

  OFFSET

  (@PageNumber - 1) * @NumberOfRows ROWS

  FETCH NEXT

  @NumberOfRows ROWS ONLY

  GO

  

  The SQL Guy @ blogspot[/url]

  @SeanPearceSQL

  About Me[/url]