Paging in sql server

It is best idea to do paging in server side. From Sql Server 2012 we have useful paging mechanism

We can use FETCH and OFFSET to paging in sql server.

FETCH -> indicates number of rows to retrieve

OFFSET-> indicates the number of rows to skip

Syntax for Paging

SELECT <columns>
FROM <tables>
ORDER BY<columns>
OFFSET x
FETCH NEXT y ROWS ONLY

x means number of record to skip and y means number of record to retrieve

Example

SELECT * 
FROM product 
ORDER BY productId 
OFFSET 20 ROWS 
FETCH NEXT 20 ROWS ONLY

In this it skips first 20 records and fetch next 20 rows

But in this some restrictions are there

• ORDER BY is required
• TOP is not allowed

CTE in sql server

CTE stands for Common Table Expression. It is introduced in sql server 2005.

Actualy it is not a terrible thing as we heard. We can consider it as inline view or temporary table.  CTE allows us to do exact same things like what we doing using views.

Benefits:

• Breakdown complex queries
• Avoid sub queires
• Simply certain syntax

Syntax for CTE

WITH <cte name>[(columns)]

AS(

<SELECT statement>

)

Example

WITH SalesData(TotalSold,Year,Branch)
AS(
SELECT SUM(Total) AS 'TotalSold',
       YEAR(OrderDate) AS '[Year]',
       b.BranchName
 FROM Order o INNER JOIN 
 Branch b 
 ON o.branchId = b.branchId
 GROUP BY YEAR(OrderDate),b.BranchName
)

Then we can do any operations to SalesData CTE as we doing to a View.

Like : SELECT * FROM SalesData

Note: CTE column specifying is optional . if not specified it takes select query result columns

We can use it for short hand for sub-query. More over there are many other benefits in CTE. I noted a here very basic usage.