asp.net the classic database records pagination Code _ Practical Skills

1,
SELECT Top PAGESIZE Newstitle

FORM Newsinfo WHERE NEWSID not in

(SELECT Top (PAGE-1) * PAGESIZE NEWSID from Newsinfo

WHERE auditing=1 and newsbreed= ' Enterprise News ' ORDER by NEWSID DESC)

and Auditing=1 and newsbreed= ' Enterprise News ' ORDER by NEWSID DESC



Where: page represents the current number of pages, pagesize represents the size of the page; This utilizes not in, does not compound sarg, but is always better than reading all the records at once.

2, conforms to Sarg code



For my example there is a better solution: Because the NewSID field is a self added field, the following modifications to the not in statement do not affect the result. But the speed has improved a lot.



SELECT Top PAGESIZE Newstitle

FORM Newsinfo WHERE NEWSID <

(select MIN (NEWSID) from (select Top (PAGE-1) * PAGESIZE NEWSID from Newsinfo WHERE auditing=1 and newsbreed= ' Enterprise News ' order by NEWSID DESC) as TB) and auditing=1 and newsbreed= ' Enterprise News ' ORDER by NEWSID DESC

Description
For Web applications that use paging functionality in multiple places, it would be better to change the SQL statement to a stored procedure.

Please actively participate in the discussion of pagination algorithm, and the good plan to share with you.