Skip to content

Searcharticlesvideosblogsforumsinterview questionsarticles

Tech
Articles
Blogs
News
Videos
Interviews
Forums
Books
Events
- Jobs
- Stories

Article
Blog
Video
Ebook
Interview Question

Loading

Upcoming Events

23 NOV
AI Agents Conference 2026 - 3rd Edition

Our Training Programs

AI & Machine Learning
Mastering Large Language Models
Mastering Prompt Engineering
Certified Vibe Coder
Github Copilot Training
Generative AI for Beginners
n8n Automation & AI Agents Training

About Us Contact Us Privacy Policy Terms Brand Guidelines Partners C# Tutorials Consultants Ideas Report A Bug FAQs Sitemap Stories CSharp TV DB Talks Web3 Universe Jumpstart Blockchain

©2026 C# Corner.

All contents are copyright of their authors.

DENSE_RANK() Vs OFFSET in Sql?

Home
Forums

DENSE_RANK() Vs OFFSET in Sql?

Thiyagarajan E

Thiyagarajan E

7yAsked Nov 10, 2018, 4:34 AM1.8k

which is the optimized way for server-side pagination in SQL using DENSE_RANK() or OFFSET?

OFFSET Query:

SELECT * FROM Tbl_Employee OFFSET @PageSize * (@Page - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);

DENSE_RANK():

SELECT *
FROM (
SELECT DENSE_RANK() OVER (ORDER BY FullName ASC) AS RowNo,* FROM Tbl_Employee
) TAB
WHERE RowNo >= 1 and RowNo <= 10
ORDER BY 2 ASC;

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Sign in to answer this question

It is the same account you read, post and publish with — and you will come straight back to this page.

Sign in Create an account

Laxmidhar Sahoo7y ago
OFFSET is use for Pagination where as DENSE_RANK(),ROW_NUMBER and NITILE are used for Ranking on column values
CREATE PROCEDURE dbo.Pagination_Test_1 -- ORDER BY CustomerID
  @PageNumber INT = 1,
  @PageSize   INT = 100
AS
BEGIN
  SET NOCOUNT ON;

  SELECT CustomerID, FirstName, LastName,
      EMail, Active, Created, Updated
    FROM dbo.Customers_I
    ORDER BY CustomerID
    OFFSET @PageSize * (@PageNumber - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY OPTION (RECOMPILE);
END
GO
DENSE_RANK()
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity
    ,DENSE_RANK() OVER
    (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank
FROM Production.ProductInventory AS i
INNER JOIN Production.Product AS p
    ON i.ProductID = p.ProductID
WHERE i.LocationID BETWEEN 3 AND 4
ORDER BY i.LocationID;
GO
0