Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL: Use LIMIT and OFFSET for Efficient Pagination

- 13.06.26 - ErcanOPAK

📄 Don’t Load 10,000 Rows at Once

Loading all rows is slow. LIMIT and OFFSET load only needed rows. Faster queries, better user experience.

📝 Basic Pagination

-- PostgreSQL / MySQL / SQLite
SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 0;  -- Page 1 (rows 1-10)

SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 10; -- Page 2 (rows 11-20)

-- SQL Server (OFFSET FETCH)
SELECT * FROM users
ORDER BY id
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

-- MySQL alternative
SELECT * FROM users
ORDER BY id
LIMIT 10, 10;  -- LIMIT offset, count

🎯 Cursor-Based Pagination (Better for Large Data)

-- Instead of OFFSET (which scans rows), use WHERE
SELECT * FROM users
WHERE id > 100  -- last seen ID
ORDER BY id
LIMIT 10;

-- With date cursor
SELECT * FROM orders
WHERE order_date > '2024-01-15'
ORDER BY order_date
LIMIT 20;

-- Performance: OFFSET 10000 is slow (scans 10000 rows)
-- Cursor: only scans 10 rows (much faster)

💡 Best Practices

  • Always use ORDER BY with LIMIT (consistent results)
  • Small page sizes (10-50 rows for UI, 100-500 for APIs)
  • For large datasets, use cursor-based pagination (keyset pagination)
  • Index the ORDER BY column for fast pagination

“Query loaded 50,000 rows. UI crashed. Added LIMIT 50. API response 2s → 50ms. Pagination is essential for any list view.”

— Backend Developer

Related posts:

SQL: Use CROSS JOIN to Generate All Possible Combinations

SQL: Use CROSS JOIN to Generate All Possible Combinations

SQL Temporal Tables: Track Data Changes Over Time

SQL Temporal Tables: Track Data Changes Over Time

SQL: Master Window Functions for Advanced Analytics

SQL: Master Window Functions for Advanced Analytics

Views: 19

Post navigation

.NET Core: Configure CORS to Allow API Access from Other Domains
C#: Use Null Forgiving Operator (!) When You Know Better Than Compiler

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com