7/25/2007

Server Side Paging Using T-Sql

It's a common task to sort and page the query result in the server side when doing database development. Here are some useful tips for doing this on Microsoft SQL Server using T-Sql.
To illustrate with real T-Sql code, suppose we have the following table definition and want to support sorting/paging on birthday column in DESC order, and we also have some parameter definitions.

CREATE TABLE user_info (

    user_guid BIGINT IDENTITY(1, 1),

    first_name NVARCHAR(64),

    last_name NVARCHAR(64),

    birthday DATETIME

)

DECLARE @StartIndex BIGINT

DECLARE @PageSize INT

1. Use TOP N function:
This method is supported on many Sql Server versions including Sql Server 2000, but peformance is poor in big table.
    SELECT *
    FROM (SELECT TOP(@StartIndex + @PageSize + 1) *
      FROM user_info
      ORDER BY birthday DESC
    ) AS low_bd
    WHERE user_guid NOT IN (
      SELECT TOP(@StartIndex) *
      FROM user_info
      ORDER BY birthday DESC
    )
2. Use ROW_NUMBER() and Common Table Expression:
This method is only support in Sql Server 2005, but has very low performance cost.

WITH u_cte AS (

  SELECT user_guid, ROW_NUMBER() OVER (ORDER BY birthday DESC) AS row_num

  FROM user_info

)

SELECT *

FROM u_cte, user_info

WHERE u_cte.user_guid = user_info.user_guid

AND row_num BETWEEN @StartIndex AND (@StartIndex + @PageSize + 1)

ORDER BY row_num ASC

No comments: