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 x means number of record to skip and y means number of record to retrieve Example 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 Advertisements
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 Example 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.