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

7/11/2007

Hash Index in Microsoft SQL Server

In fact, there is no real Hash Index (index that implemented using hash mechanism ONLY)in MS Sql Server, all indices are b-tree based.

But when you use some relatively long string field in search condition or join condition, there is a way to improve the query performance. This technique uses both Hash and Index concepts, so it is named as Hash Index in SQL Server Book Online.

This technique involved two steps:
1. Add a numeric column to the existing table, which is is hash value of the string field

CREATE TABLE website(
ip nvarchar(16),
site_uri nvarchar(1024) NOT NULL,
uri_hash AS (checksum([site_uri])),
);

2. Build a non-clustered, non-unique index on this numeric field

CREATE INDEX idx_uri_hash ON
website(uri_hash) INCLUDE (site_uri);

Later when you do some equal comparison on this field, you can compose your query like:
SELECT ip
FROM website
WHERE uri_hash = CHECKSUM(N'http://www.live.com')
AND site_uri = N'http://www.live.com'

Sql Server Storage Engine will use the hash index first, and only do string comparison on the records returned from previous step.