SQL Server Performance

Consolidated: SQL 2005+ Dynamic Pagination using SET ROWCOUNT or RowNumber() ?

Discussion in 'General DBA Questions' started by Hmnt, Nov 3, 2009.

  1. Hmnt New Member

    I've a complex SP which applies multiple JOINs and lookup and come complex filters like comma-separated values, etc... On top of it, I've to deploy two complex yet performance-effective features:
    **1. Dynamic sorting** but I see its limited - you kno the long/clumsy CASE hierarchy, its strange that experts also agree that this is the only 'best' solution we've got:
    http://stackoverflow.com/questions/149380/dynamic-sorting-within-sql-stored-procedures
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=5942
    Anyway, I don't expect much on this one for now.
    **2. Dynamic pagination** - that is I want the SP to be able to return only X number of records (X = page size) starting from Y (Y = page number). I hope you've got the general idea.
    To make it more clear I want to use something available in MySQL & PostgreSQL:
    [LIMIT { number | ALL }] [OFFSET number]
    http://stackoverflow.com/questions/1545203/database-sql-pagination
    http://www.sql.org/sql-database/postgresql/manual/queries-limit.html
    *Its strange such a simple & basic functionality is NOT available in SQL 2005+ .. or am I wrong (I'd be glad to hear it [:#])*
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------
    I've known two approaches which suite my performancecomplexity tradeoff -
    **[2.1] Using the 'RowNumber()' feature of SQL 2005** and then applying filter: (I've used it in past)
    WHERE (Row BETWEEN (@PageIndex-1) * @PageSize +1 AND @PageIndex* @PageSize)
    But again, this needs creating a temp table or using a WITH clause. This is also explained in:
    http://stackoverflow.com/questions/187998/row-offset-in-ms-sql-server
    **[2.2] I found some new ways.** One of them is using the
    SET ROWCOUNT
    http://www.4guysfromrolla.com/webtech/042606-1.shtml
    *And they say that overall 2.2 is effective then 2.1. Is it? Also, I wanted to know what happens if two user-requests trigger the same SP twice simultaneously .. I hope the 'SET ROWCOUNT' won't be 'shared' or 'over-written' among simultaneous SP-calls. Pls confirm.
    Any other points comparing 2.1 & 2.2 ?*

    Pheew .. I hope I've done my homework in this consolidated analysis I've shared. Pls rate it and share your thots. Let me know if this has been useful.
    Thank you.
  2. Adriaan New Member

    The scope of any SET command is the current connection or script.

Share This Page