Interview Question of the Week #025 – How to Implement Paging in SQL Server?

Here is a very popular question when SQL Server interview consists of live coding example.

Question: How to implement paging in SQL Server? Explain with the help of script.

Answer: Here is the script to implemented paging in SQL Server 2005.

USE AdventureWorks
GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120
SET @EndRow = 140
SELECT FirstName, LastName, EmailAddress
FROM (
SELECT PC.FirstName, PC.LastName, PC.EmailAddress,
ROW_NUMBER() OVER(
ORDER BY PC.FirstName, PC.LastName,PC.ContactID) AS RowNumber
FROM Person.Contact PC) PersonContact
WHERE RowNumber > @StartRow
AND RowNumber < @EndRow
ORDER BY FirstName, LastName, EmailAddress
GO

 

Above query is using ROW_NUMBER () function, but you can also use CTE to accomplish the same. Here is the script for the same:

USE AdventureWorks
GO
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120;
SET @EndRow = 140;
WITH PersonContact AS
(
SELECT PC.FirstName, PC.LastName, PC.EmailAddress,
ROW_NUMBER() OVER(
ORDER BY PC.FirstName, PC.LastName,PC.ContactID) AS RowNumber
FROM Person.Contact PC)
SELECT FirstName, LastName, EmailAddress
FROM PersonContact
WHERE RowNumber > @StartRow
AND RowNumber < @EndRow
ORDER BY FirstName, LastName, EmailAddress
GO


You can read more about this in following blog posts:

Reference : Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Using PowerShell and Native Client to run queries in SQL Server
Next Post
SQL SERVER – Evaluation Period Has Expired – How to Activate SQL Server?

Related Posts

1 Comment. Leave new

  • Subbareddy Kamireddy
    November 28, 2017 3:18 pm

    We can use OFFSET Fetch concept to implement Paging as well

    Reply

Leave a Reply

Menu