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
- Write a query that shows record counts for different conditions on the same row
- Combining multiple rows into one in SSIS
- Complex LIKE Query extremely slow againt NVARCHAR(450), compared to VARCHAR(450)
- SQL And NULL Values in where clause
- Retrieve a value from XML using T-SQL
- django-pyodbc-azure 2.1.0.0 not supporting Django 2.1.0
- Get only rows with the latest date for each name
- Generating Unique Random Numbers Efficiently
- How do I read a string > 255 chars in php/adodb?
- SQL Server select first instance of ranked data
- How can I merge 3 distinct Sqls by the same grouping but different columns (Sql Server Stored Procedure)
- Print statements in SQL merge
- How to sum the values in a one row by grouping from multiples rows
- Problem in stored procedure
- Get matching string with the percentage
- I need help using CAST to return the month/day only
- Sql query getting datetime range
- Looping through all tables and then looping through all column datatypes
- Is it possible to modify the default SQL in query analyzer for 'edit top 200 rows'?
- Need to filter out all columns which have null,0 or empty values in sql server from a group of tables
- How do i write INSTR in T-SQL?
- How to get row number in select query
- "Converting Data Type to Bit" after updating Database
- Couldn't set new column name using AS
- Custom Autogenerated Column in SQL Server
- Escaping Null Values From Quartile in SQl Server
- query to join tables and select null if the condition is not valid
- How to backup SQL Server 2008 database - only the objects, without data
- Permission to DROP Tables in SQL Server (T-SQL)
- SQL Server as Database Backend for Ionic?
- Can I use regex capturing groups in SQL Server 2014?
- How to get SQL result view from row to separate columns
- SQL Sever Query Join Optimization
- Dynamically switching on/off WHERE boolean expression(s) based on a parameter
- Using a cartisan product to created a list of comparisons with no duplicate questions
- What's your approach for optimizing large tables (+1M rows) on SQL Server?
- IS_ROLEMEMBER erroneously returns 0 for database role members?
- Why this simple SSIS Data Flow freeze?
- no local server type "database engine" were found (V 2016)
- Is it a good idea to include flags in Fact table
- COUNT and delete of the number of duplicates from table with billion rows
- Problem with default constraint "with values" in sql server
- Joining next Sequential Row
- Groupin/pivoting table
- What is the meaning of a specific part of an SQL statement?
- Join table on null where condition
- Executed as user: dbo. You do not have permission to run 'SYS.TRACES'. [SQLSTATE 42000] (Error 8189)
- SMO Transfer Class to transfer table with none default schema
- Many left joins on subqueries, need some way to increase performance
- TSQL Parse XML in Stored Procedure
- Parameters to the EXISTS clause in a stored procedure
- What is the maximum number of columns and size for Azure Sql Data warehouse table record?
- How to select the database dynamically
- Nested Subquery vs Derived table
- Do while loop in SQL Server 2008
- the best way to connect sql server (Windows authentication vs SQL Server authentication) for asp.net app
- How to create a customized calendar to display just a particular Month on Button-click, with no Saturday and Sunday?
- Select sum of a column without cursor
- SELECT Top 1 ID, DISTINCT Field
- Build customers report of last years with T-SQL
- how to set formula in column
- Dynamically create column in Table
- C# "System.IndexOutOfRangeException: There is no row at position 0" when using Stored Procedure
- SQL Merge Multiple Rows together Table
- SQL Server : does NEWID() always gives a unique ID?
- How can I structure a query to give me only the rows that match ALL values in a CSV list of IDs in T-SQL
- Replace specific value in all cells when selecting from SQL table to dgv
- Select Top 5 records of every employee in SQL Server
- sql server Concatenate Multiple Rows Using FOR XML PATH
- Does -1 always mean "max" in SQL Server metadata table?
- Retrieving value from EXEC(@sql)
- T-SQL create table statement will not accept variable
- Need to generate SQL script file in every build of every changes in database project on TFS
- Hide SELECT results in Procedure
- Delete after Insert trigger
- How to create sequential numbering against two select statements that are unioned?
- SQL Server return a list in a select case in where statement
- Why does this query return different results when in a stored procedure?
- Get data from 3 database tables
- SQL Server: changing the filegroup of an index (which is also a PK)
- Is there an UPDATE equivalent command to SELECT json data
- SQL Server 2012 Filestream : An error occurred during the drop table operation on a FileTable object
- Select all records created or modified after a specific date in all tables in a SQL Server db
- Copying just the data from one Database to another
- How to add a Case Statement for an aggregate
- SQL Aggregation With Condtion on aggregated value
- How can I fetch total of Qty of Buy and Sale in month of July in single sql query
- Divide two columns but avoid division by zero
- Replace in SQL Server
- "SQLServerException: String or binary data would be truncated" issue - Configured H2 DB to diagnose but column size change not taking effect?
- Using EXEC command as INSERT VALUE in SQL
- Error "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements"
- SQLite equivalent query of the given MSSQL query.
- Export data from SQL to text file dynamcally
- Having problems importing CSV into table
- How to get Latitude Longitude from SQL Server 2008 geography line?
- CDC is enabled, but cdc.dbo<table-name>_CT table is not being populated
- Check if date is between the start of May this year and the end of May next year
- Trigger: Inserted table column value cannot be referenced inside condition
- Return stored procedure result in function
- Inserting into multiple tables using SCOPE_IDENTITY
- Running stored procedures with parameters
- Using returned Values from a DataSet
- SQL Server: Use output from first insert to insert into second table
- SQL query to search for a record that has a certain text?
- Split query results with long strings into multiple rows in T-SQL
- How to call a UDF with a parameter which is known only at "execution time"?
- SQL Server with clause versus temporary table
- check for duplicate records in a sql server table using c#
- How could a INNER/RIGHT/LEFT JOIN be 14x slower than a FULL JOIN?
- Performance of nested select and DISTINCT/ORDER
- SQL Server equivalent to DBMS_METADATA.GET_DDL
- Check for uniqueness within update statement
- Finding adjacent column values from the last non-null value of a certain column in Snowflake (SQL) using partition by
- Stored Procedure updates where last modified equals getdate but it also updates getdate trigger
- Using Round/Ceiling/Floor function with IIF in SSRS expression
- SQL Server & update (or insert) parallelism
- Select table with column named "index"
- Can I use FOR JSON without a source column name becoming a key in SQL Server 2016?
- Create a new row in the results set for each newline character in an entered string
- TSQL return distinct rows based on the preferred value of a column
- Student Attendance Report Month-wise SQL Query
- SQL Server sort comma separated string in one column
- What is using all my storage in SQL server
- SQL Server interview questions
- Merge Replication fails to replicate UD type
- find out every date from first to last day of the month using table valued function
- How select rows which contains at least one value from other table and is not equal? TSQL
- INSERT Pandas Dataframe to SQL-Server using Python - Jupyter Notebook
- SQL Server 2008: copy table structure, and schema
- can't add a sql database to my project
- mssql_connect() with PHP5 and MSSQL2012 Express
- Read data from Microsoft SQL database on Remote Desktop via. R
- Split date into intervals
- Help with creating a computed column (sql server 2008)
- Search for element *without* a given child (where the child element is variable)
- Select a non-empty value for each column in each table in each database
- How to list all dates between two dates
- Copy values from real value column to decimal value column in SQL
- Converting varchar to decimal baseball average
- SSISDB not found and can not create New catalog in SQL Server 2012
- Composite and Covering index
- Perl: how to export data from SQL Server into tab delimited txt file using perl
- Need to populate a column based on another column in another table
- using GO keyword in SQL Server
- SQL: Update table where column = Multiple Values
- How to use cases inside of where clause?
- Difference Between Report Builder in SQL Server and Reporting Services in VS
- Querying SQL Geometry data using Longitude and Latitude
- LINQ Left Join with multiple ON OR conditions