Select Data By Page in SQL Server 2008 Side

---

When we have huge data and we need to display data in client side page by page, the best way might be paging data in SQL Server side, otherwise the huge data retrieving will be ‘dead’ in your client side.

Now we have a table as following:

CREATE TABLE [dbo].[USER_EVENTS_LOG](
    [LogEventsUserID] [int] IDENTITY(1,1) NOT NULL,
    [CfgUserID] [int] NOT NULL,
    [ClientID] [nchar](64) NOT NULL,
    [EventID] [int] NOT NULL,
    [EventDetail] [nchar](300) NOT NULL,
    [CreateTime] [datetime] NOT NULL,
 CONSTRAINT [PK_log_events_user] PRIMARY KEY CLUSTERED 
(
    [LogEventsUserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Method 1: Using RowNumber() function.

-- select records between the row 99 and 151
select * from 
(  
SELECT *, Row_Number() over (ORDER BY [LogEventsUserID]) AS RowNumber 
FROM [dbo].[USER_EVENTS_LOG]
) as TBRowNum
where (TBRowNum.RowNumber > 99) AND (TBRowNum.RowNumber < 151)
ORDER BY TBRowNum.[CreateTime] desc

The above is just a sample from row 100 to 150, you need to change to your page index and page rows (page size)

Method 2: Using Not IN:

Here we use a stored procedure, it is from a real project, the first sql string is the main part which we use NOT IN, the second string is for the result which uses join

USE [ourDB]
GO
/****** Object:  StoredProcedure [dbo].[GetUserEventLogByPage]    Script Date: 06/10/2011 18:21:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:
-- Create date: 2011 June 10
-- Description:    

-- Get User Event Log by Page Size and Page Index
-- =============================================

--IF OBJECT_ID ( 'dbo.GetUserEventLogByPage', 'P' ) IS NULL 
--BEGIN
ALTER PROCEDURE [dbo].[GetUserEventLogByPage]
    @PageIndex int,
    @RowsOfPage int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    
    DECLARE @selRangeSQL AS varchar(2000)
    DECLARE @selSQL AS varchar(2000)
    DECLARE @RowsCount As int
    
    SET  @RowsCount = @RowsOfPage * @PageIndex    
    
    SET @selRangeSQL = '( SELECT TOP ' +  CAST(@RowsOfPage as varchar) + ' * '
                    + ' FROM [USER_EVENTS_LOG] '
                    + ' WHERE [LogEventsUserID] NOT IN '
                    + ' ( SELECT TOP ' + CAST(@RowsCount as varchar) + ' [LogEventsUserID] '
                    + ' FROM [USER_EVENTS_LOG] ORDER BY [CreateTime] DESC ) '
                    + ' ORDER BY [CreateTime] DESC ) '
                    
    -- select  @selRangeSQL            
                    
     SET @selSQL = 'SELECT A.LogEventsUserID, U.UserName, A.ClientID, A.CreateTime, B.EventName, A.EventDetail
     FROM  ' + @selRangeSQL + ' AS A INNER JOIN 
            USER_CONFIG AS U ON A.CfgUserID = U.CfgUserID INNER JOIN 
                    EVENT_DESC_CONFIG AS B ON A.EventID = B.CfgEventID '
                    
     --select @selSQL
     EXEC(@selSQL) 

END
--END
--GO

A sample load this procedure is:

exec dbo.GetUserEventLogByPage 6, 50