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
