Limit Records in SQL

Introduction

When working with SQL databases, there are many situations where you might want to limit the number of rows returned by a query or impacted by an operation. For instance, you might want to show only the top 10 results from a large dataset, or paginate through records in a web application. Each SQL database management system (DBMS) provides different methods to limit the number of records returned. In this chapter, we will explore the various techniques to limit records across different SQL databases, with a special focus on SQL Server (MS SQL).

Why Limit Records?

Limiting records is essential in many scenarios:

• Performance: Returning or updating a limited number of records reduces processing time and system load.
• Pagination: Websites often display results in pages, showing only a subset of data at a time.
• Sampling: You might want to see a quick snapshot of data without processing the entire dataset.
• Top Results: In situations like ranking, you might only need the top results based on a certain criteria.

Tables

Customers

Orders

Limiting Records

SQL Server does not support the LIMIT keyword found in databases like MySQL. Instead, SQL Server uses the TOP keyword to limit the number of rows returned. Additionally, starting from SQL Server 2012, the OFFSET-FETCH clause is available for more advanced pagination.

1. Using TOP to Limit Results

The TOP keyword is used to limit the number of rows returned in a query result. This is the simplest and most common way to limit rows in SQL Server.

Syntax

SELECT TOP (n) column1, column2, ...
FROM table_name;

• n represents the number of rows to be returned.
• If n is a percentage, use PERCENT to get that percentage of rows.

Example

SELECT TOP (5) *
FROM Orders;

This query will return the first 5 rows from the Orders table. The ordering of the rows depends on the structure of the table unless an explicit ORDER BY clause is provided.

Example: Using ORDER BY with TOP

SELECT TOP (5) *
FROM Orders
ORDER BY order_date DESC;

In this query, the first 5 most recent orders will be returned, as the ORDER BY clause sorts the results by the order_date column in descending order.

Example: Using TOP with PERCENT

You can also limit the number of records by a percentage of the total rows using the PERCENT keyword.

SELECT TOP (10) PERCENT *
FROM Orders;

This query will return the top 10 percent of rows from the Orders table.

2. Limiting Records Using OFFSET-FETCH

Starting from SQL Server 2012, the OFFSET-FETCH clause provides a more flexible way to limit and paginate records, especially for applications that require data pagination.

Basic Syntax of OFFSET-FETCH

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name
OFFSET n ROWS FETCH NEXT m ROWS ONLY;

• OFFSET n ROWS: Skips the first n rows.
• FETCH NEXT m ROWS ONLY: Fetches the next m rows after skipping n rows.

Example: Paginating Through Data

SELECT *
FROM Customers
ORDER BY customer_id
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

This query will skip the first 10 rows and then fetch the next 5 rows from the Customers table. This is particularly useful when displaying data in pages on a website.

Example: Skipping All but the First 10 Rows

SELECT *
FROM Orders
ORDER BY order_date
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

This query fetches only the first 10 rows from the Orders table, based on their order date.

Example: Advanced Pagination

SELECT *
FROM Products
ORDER BY product_name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

In this example, the query skips the first 20 products and fetches the next 10. This is helpful when implementing "Next Page" functionality on a website.

Conclusion

Limiting records is a crucial operation when working with large datasets, both for performance reasons and for user-facing applications that display data in a paginated manner. While databases like MySQL use the LIMIT clause, SQL Server uses the TOP clause and the OFFSET-FETCH feature for similar functionality. Understanding how to control the number of records returned or affected by queries is essential for optimizing queries, improving performance, and creating efficient applications.

Asked in Interview

• How would you retrieve the first 5 rows from a table using SQL?
• How would you skip the first 10 rows and retrieve the next 5 rows?
• How can you retrieve the highest/lowest N records from a table?
• What is the difference between LIMIT and TOP in SQL?
• How do you implement pagination in SQL?
• What is the impact of using LIMIT on query performance?
• Can you use LIMIT in an update or delete query?
• How does LIMIT affect the use of aggregate functions like COUNT, SUM, or AVG?