Menu
What would be the most efficient way to do a paging query in SQLServer 2000?
Where a "paging query" would be the equivalent of using the LIMIT statement in MySQL.
EDIT: Could a stored procedure be more efficient than any set based query in that case?
634
The best way for paging in sql server 2012 is by using offset and fetch next in a stored procedure. OFFSET Keyword - If we use offset with the order by clause then the query will skip the number of records we specified in OFFSET n Rows.
Thanks to pagination, we can split our large dataset into chunks ( or pages ) that we can gradually fetch and display to the user, thus reducing the load on the database. Pagination also solves a lot of performance issues both on the client and server-side!
1:- Check Indexes. 2:- There should be indexes on all fields used in the WHERE and JOIN portions of the SQL statement 3:- Limit Size of Your Working Data Set. 4:- Only Select Fields You select as Need. 5:- Remove Unnecessary Table and index 6:- Remove OUTER JOINS.
Paging of Large Resultsets and the winner is using RowCount. Also there's a generalized version for more complex queries. But give credit to Jasmin Muharemovic :)
DECLARE @Sort /* the type of the sorting column */
SET ROWCOUNT @StartRow
SELECT @Sort = SortColumn FROM Table ORDER BY SortColumn
SET ROWCOUNT @PageSize
SELECT ... FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn
The article contains the entire source code.
Please read the "Update 2004-05-05" information. !
58
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
