The ways to paging

最新推荐文章于 2025-11-30 19:37:16 发布

原创最新推荐文章于 2025-11-30 19:37:16 发布 · 487 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#linq #sql #idea #table #c

数据库专栏收录该内容

8 篇文章

订阅专栏

本文探讨了四种常见的数据库分页方法：1. 先获取所有数据再进行分页；2. 利用现有框架如 NHibernate 的分页功能；3. 使用存储过程及动态 SQL 实现分页；4. 在 SQL 中使用行号进行分页。对于记录数量适中的情况，第四种方法表现最佳。

In projects, you must have the experience of how to paging datas. you would have below options:

1. Fetch all the data first, then do the paging in BE logic.

such as Linq:

    List<string> results = (from c in lists
                               where !string.IsNullOrEmpty(c)
                               select c).Skip(skip).Take(count).ToList();

2. Use the feature of existed framwork, such as NHibernate,

    IQuery q = Helper.CurrentSession.CreateQuery( getHQL )
                             .SetMaxResults( pageSize )
                             .SetFirstResult( startIndex )
                             .SetCacheable( true );

3. Use store procedure, TSQL. use dynamic sql in store procedure.

@count int

DECLARE @SQLString NVARCHAR(500);

SET @SQLString = N'SELECT TOP ' + CAST(@count AS NVARCHAR(10)) + ' * FirstName, LastName, JobTitle' + CHAR(13);

EXEC sp_executesql @SQLString;

4. Use logic in sql, combine the row number in a temp variable.

CREATE PROCEDURE GetItems
@count int = 0
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ItemID
    FROM
    (SELECT ROW_NUMBER() OVER(ORDER BY ItemID) as Id,
           ItemID,
      FROM dbo.MediaVideos
     ) a
    WHERE Id <= @count
END
GO

For the perfomance point, if the records amount in table is not very big, the #4 option will be idea one.