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
FETCH NEXT y ROWS ONLY
x means number of record to skip and y means number of record to retrieve
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 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.
- Breakdown complex queries
- Avoid sub queires
- Simply certain syntax
Syntax for CTE
WITH <cte name>[(columns)]
SELECT SUM(Total) AS 'TotalSold',
YEAR(OrderDate) AS '[Year]',
FROM Order o INNER JOIN
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.