In this article, we will learn, How to select records from one table with pagination and count.
Recently I’m working on a Social Media WebApp Project in which users can share images, feed videos, etc. And We are showing users post on a page just like Facebook.
For improving performance, I need to implement the pegging in the SQL server. I have completed my task successfully after that I have decided to share the best way according to performance-wise to paginate results in SQL Server.we can do this in many ways in this post we will discuss some techniques to do this.
if you are using MSSQL 2012 or the latest version, then you can use Offset and Fetch which is the best way to handle the pegging in the SQL server.
SELECT * FROM NewsFeed ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Or
declare @SkipRecord int = 25,
@TakeRecord int = 100,
@count int = 0
;WITH Feed_cte AS (
SELECT Id,Title
FROM dbo.NewsFeed
)
SELECT
Id,
Title,
Feedscount.CountFeed AS TotalRows
FROM Feed_cte
CROSS JOIN (SELECT Count(*) AS CountFeed FROM Feed_cte) AS Feedscount
ORDER BY Id
OFFSET @SkipRecord ROWS
FETCH NEXT @TakeRecord ROWS ONLY;
Key points to consider when using it:
- ORDER BY is necessary to use when using the OFFSET and FETCH clause.
- OFFSET clause is necessary with FETCH.
- TOP cannot be used with OFFSET and FETCH.
if you have lower version of sql server you can use below query, the following query would be most efficient:
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY CreatedAt ) AS RowNum, *
FROM NewsFeed
WHERE CreatedAt >= '2018-01-01'
) AS RowResult
WHERE RowNum >= 1
AND RowNum <= 50
ORDER BY RowNum
Above query return rows 1-50 of the original query. Using this query you only have to keep any state the row numbers to be returned.
In SQL server 2000 we don’t have ROW_NUMBER() we can assume ROW_NUMBER() using a table variable with an IDENTITY column.
DECLARE @pageNo int
DECLARE @pageSize int
SET @pageNo = 2--page number of the webpage
SET @pageSize = 10 ---no of records in one page
DECLARE @firstrow int
DECLARE @lastrow int
SET @firstrow = (@pageNo - 1) * @pageSize + 1 -- 1001
SET @lastrow = @firstrow + @pageSize - 1 -- 1020
DECLARE @feedKeys TABLE (
rownum int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
TableKey int NOT NULL
)
SET ROWCOUNT @lastrow
INSERT INTO @feedKeys (TableKey) SELECT ID FROM NewsFeed WHERE CreatedAt >= '2000-01-01' ORDER BY CreatedAt
SET ROWCOUNT 0
SELECT t.*
FROM NewsFeed t
INNER JOIN @feedKeys o ON o.TableKey = t.ID
WHERE o.rownum >= @firstrow
ORDER BY o.rownum
Please share your thought!
The post [Solved]-Best Sql server query with pagination and count appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- COM Error 80020005 Type mismatch in Domain User login
- TSQL Control Flow Logic
- SQL Server Management Studio: missing "Security" page
- SQLCLR & IronPython
- Converting rows to columns and keeping data pairs
- Sample application with multitenant database
- How to use dynamic query in maven project?
- Rows disappears from SQL Server Table after Insert and Commit
- Order by inside a subquery
- How to speed up the delete operation from a table that has millions of records?
- update top 100 rows order by Datetime desc
- Cannot set IMPLICIT_TRANSACTIONS to OFF for stored procedure?
- Virtualize the database server or the web server?
- how to insert a summation of 2 values in back end in sql database
- STORED PROCEDURE working in my local test machine cannot be created in production environment
- How to view default values of the SET options of a server
- There was an exception running the extensions specified in the config file. Maximum request length exceeded
- T-SQL for merging data from one table to another
- Consolidate rows of data in SQL Server
- commit and rollback in sql server 2005
- Comparing Comma Separate Value with Comma Separated Values SQL
- Finding most queried items in a table in SQL Server
- Change username programmatically when connecting to a Sql Server using Windows authentication from a Delphi application
- Convert string to System.Data.SqlTypes.SqlBytes C#
- SQL Server 2008 R2 Sales transaction Trigger
- Crystal report to SSRS : Evaluating running total only on change of Field
- How to replace NULL values with Mean value of a category in SQL?
- Error when creating linked server in SQL Server 2008R2
- Bulk Update Email Trigger
- EF Reverse POCO against a SQL Database Project?
- SQL Server Return just 1 row from total matches
- SQL Collapse Data
- select the locale based on the current date
- SQL Server XQuery - Find the original data type
- How do aggregates (group by) work on SQL Server?
- Is there any way to do select on top of WITH clause in a SQL Server CTE?
- Moving Only Subscriptions from SSRS 2008 to SSRS 2012
- Position of a column in a table
- Query for earliest datetime and corresponding number field
- Case statement in where clause with "not equal" condition
- Webmatrix webgrid not allowing to change rowsPerPage using Razor
- Update XML node value in SQL Server
- Remove Multiple Trailing Characters in SQL Server - Oracle equivalent to Trim (Trailing)
- Using Top in T-SQL
- Dynamically load .xlsx files in ssis
- How to generate decimal numbers sequence for particular number range
- How to output stored procedure query results to a text file
- Unlock table if browser is closed
- SQL Server: Regenerate value for identity column on row update
- How do obtain the @@identity value after using SqlCeDataAdapter.Update ()
- The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Messages__Email__3D9E16F4"
- SQL CASE statement needs to handle Text
- Right Substring Statement SQL
- Create comma separated value strings using data from different tables in SQL Server
- How can I set bit column to null?
- True getDate() while updating?
- simple recursive query
- how to check the query is using index
- How to nest xml through sql query
- Sql 2005 triggers
- Purpose of TRAN and COMMIT in single/multiple SQL INSERT SELECT UPDATE DELETE statement
- How to write a SQL query to manipulate data between 2 tables?
- User defined function sql, Incorrect syntax near the keyword 'return'
- TSQL - An object or column name is missing or empty .. When trying to backup sql server database to disk
- "select * into table" Will it work for inserting data into existing table
- Cumulating value of current row + sum of previous rows
- Select only one Product with MAX(Id) for each Date
- SQL Group By Customized Categories (Number)
- SQL Server Average Query
- Laravel 4 mssql stored procedure with parameters as a prepared statements
- C# using a while loop for SqlDataReader
- Top-N By Decade for successive decades (in SQL Server)
- Create a procedure for table
- Which Sql Server column type to use for a position in a video?
- How to apply conditional aggregation in SQL the following scenario?
- List Tables and Table Extended Properties
- Use CASE with JOIN Statements in SQL Server
- Deploying SQL CLR Project fails when creating assembly on database
- DDL Trigger with example
- SQL Query Join Issue
- Combining two queries
- Update SQL Database from client side in ASP.NET
- PHP Warning: PHP Startup: Unable to load dynamic library 'sqlsrv.so'
- How distribute SQL results horizontally?
- undo stored procedure if there is an error on execution
- Exclude records where particular value exists in related record
- Finding the most repeated element from a table grouped by restrictions in sql
- SQL Server 2000: Is there a way to tell when a record was last modified?
- SCOPE_IDENTITY, multiple inserts, carries value from previous insert if current insert doesn't insert
- how to join two tables into a new table for display in c#?
- Know what id is not present
- Search a varchar field that contains all words from another string
- sql server: using coalesce to check if character exists and replace it
- SQL Server Distinct on Earliest Date in a Timestamp Column
- In clause with 2 fields on 1 table
- How to get the data from below sql table?
- How to convert date to 104 format without zero in month using T-SQL
- Can't install SQL Server 2014 on WIndows 7 - HRESULT 0x8007370B
- show additional row where column value is null
- Microsoft SQL Server backup file very heavy
- Find all the columns and keys for multiple tables in one database
- Check if stored proc exists in DB?
- Update Primary table with String match from another table
- How to connect SQL Server 2008 Express?
- How to get row number in select query
- limit select based on group
- Passing integer lists in a sql query, best practices
- SQL Server varchar memory use
- SMO syntax in C#
- How to alter column of a table with indexes?
- SQL NOT LIKE and LIKE
- How to properly retrieve table ID
- Flatfile Datatype Issue with SSIS to SQL Server
- Why is my index getting fragmented?
- SQL Server: conditional aggregation
- SQL Server : query with incorrect SUM result
- How can I copy one column to from one table to another in SQL Server
- Position of TRY/CATCH bloc for SQL Server stored procedure
- Sql Server to MySql Data Migration
- Rename Index Error parameter @objname is ambiguous
- System.Security.SecurityException: That assembly does not allow partially trusted callers
- Project query results in column
- SQL Server aggregate performance
- Export query result to excel or csv on SQL server
- Waitfor/pause commands for sql server
- Grouping By Two Column But Only Showing One
- Azure Complete ARM Deployment Fails with MSSQL PaaS Instance
- want to create excel file in C# by preserving all rows and columns in text format
- Refactoring SQL query
- Memcached vs SQL Server cache
- Saving generated pdf in the database using c#
- Is it possible to add a column to an existing clustered index in sql server?
- Display data on datagrid from multiple tables in c# while using inner join in stored procedure
- Can't access mdf file in desktop application
- Get values from xml nodes within sql statement
- Converting column to decimal in stored procedure
- How to timeout "A network-related or instance-specific error occurred while establishing a connection to SQL Server" error
- How can I construct a data model to bind to a SQL table with an XML column
- SQL Error (4145): An expression of non-boolean type specified in a context where condition is expected, near '('
- What is the scope of SET IDENTITY_INSERT xyz ON?
- Calculate sum in tier pricing. SQL Server. Tier pricing table
- How to automatically update database SQL Server?
- Vertical tab in SQL Server Management Studio - query and results side by side?
- SQLDataAdapter is updating, but it is appending to the Database and not updating
- SQL Server 2008 View with LEFT OUTER JOIN fails on Entity Framework 4
- One INSERT with UNIONS or multiple INSERTS?
- Select Certain Groups while Excluding Others
- SQL Join on nearest available date
- return 0 on sql query instead of nothing
- Evaluating XQuery/XPath expressions from table