SQL Server - Result paganation in sql server

A lot of SQL Server database users at some time faced a big obstacle, especially web sites programmers when need to read data from the database in case of a partial data was a fairly large size. After a long and arduous journey made in the search for the best method that can solve the problem very haunting, I've experimenting with dozens of ways that were not satisfactory at first, especially if the data is very large size.

In fact,i did't invent magic solution, but I found the best located after many modifications and tests, all web solutions was talked about using  these methods (ROW_NUMBER() , TOP Rows ,CTE and Temp tables) in sql server 2005 and older versions wich i prefer using TOP method to done this job , because it's flexible and no limitation or conflicts for using TOP with your query criteria's and orders data results .

Using TOP IN SQL Server 2005

i prefer using sql stored procedure with parameters like @page_num , @page_size and @query_type that i use @query type to switch between counting results and query reaults it self .

Create PROCEDURE [dbo].[GetArchive]
(@username NVARCHAR(100),@page_num int,@page_size int,@proc_type nvarchar(20))AS
BEGIN


declare @page_count int ,@StartRow int , @EndRow int ,@sql nvarchar(max)
 

if (@proc_type='get_count')
begin
 set @sql = 'SELECT Count(*) as AllCount FROM Archive where id > 0'


end
else
begin
 set @sql = 'SELECT TOP ' + cast(@page_size as nvarchar(100)) 
 set @sql = @sql + ' Name,Mobile,Email,[Datetime] '
 set @sql = @sql +' FROM Archive '
 set @sql = @sql + 'WHERE ID NOT IN(SELECT TOP ('+cast(@page_size as nvarchar(100))+' * ('+cast(@page_num as nvarchar(100))+' - 1)) ID FROM Archive where id > 0 AND Username ='''+@username+''' order by id desc) '
end
set @sql = @sql + ' and username = '''+@username +'''';


if (@proc_type<>'get_count')
   set @sql = @sql + ' order by id desc '




exec (@sql)

 

END

Executing procedure will generate sql query like this

SELECT TOP 10 Name,Mobile,Email,[Datetime] FROM Archive WHERE ID NOT IN(SELECT TOP (10 * (2 - 1)) ID FROM Archive where id > 0 AND Username ='User' order by id desc)  and username = 'User' order by id desc 

Paganation IN SQL Server 2012

Sql server 2012 intreduce new keyword to do this job in easy and fast way like any other databsaes especially LIMIT in Mysql, using OFFSET ended this long conflict by add this keyword to your query

SELECT
     *
FROM  [dbo].[Archive]
ORDER BY id
OFFSET 10 ROWS
FETCH NEXT 20 ROWS ONLY