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:
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]
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 performance\complexity 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: Row Offset in SQL Server
[2.2] I found some new ways. One of them is using the
SET ROWCOUNT
https://web.archive.org/web/20211020131201/https://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 ?
