SQL provides powerful capabilities for querying and manipulating data stored in databases. However, sometimes the result sets returned by SQL queries can be unexpectedly large, containing more rows and columns than an application needs to process. Want to constrain the rows returned by a query to a manageable number? SQL offers two useful clauses for reining in result sets – LIMIT and FETCH.

Introduction to LIMIT and FETCH

The LIMIT and FETCH clauses in SQL allow for precise control over the size of result sets. By restricting rows returned, they prevent runaway queries from hogging server resources or overwhelming applications.

LIMIT and FETCH serve similar purposes, but work in slightly different ways:

• LIMIT – Reduces the overall number of rows a query returns. For example, LIMIT 10 returns only the first 10 rows.
• FETCH – Retrieves a portion of rows from a larger result set. So FETCH FIRST 10 ROWS gets the first 10 rows, but doesn’t cap the total size.

Together, LIMIT and FETCH provide fine-grained options for constraining result sets by number of rows, saving memory and network bandwidth.

In this guide, we’ll explore how to use SQL LIMIT and FETCH clauses to control result set sizes.

How SQL LIMIT Works

The LIMIT clause restricts the number of rows returned by a query. Up to two numeric arguments can be provided:

SELECT column1, column2
FROM table
LIMIT row_count;

• row_count – Maximum number of rows to return. Required parameter.
• offset – Optional numeric offset for start of row retrieval.

For example, this query returns the first 10 rows from the results:

SELECT * FROM students LIMIT 10;

The offset parameter can specify the starting point for row retrieval. To get rows 11-20, we’d do:

SELECT * FROM students LIMIT 10 OFFSET 10;

You can think of LIMIT as setting a cap on the total result set size. Even if thousands of rows match the query conditions, only the limited number will be returned.

Some key capabilities provided by LIMIT:

• Paginating data – Retrieve pages of rows at a time from large tables using LIMIT with offset. This avoids pulling entire contents into memory.
• Faster testing queries – Trimming down big result sets makes queries run faster. During development, LIMIT can isolate a working data subset.
• Reducing user wait time – Queries limited to a reasonable size return to the application quickly, improving perceived performance.

Now let’s look at how FETCH provides additional options for constraining rows.

Using FETCH for More Granular Control

Like LIMIT, the FETCH clause retrieves a specified number of rows from a query’s result set. But FETCH gives more granular control over the subset of rows returned.

The basic syntax of FETCH is:

SELECT column1, column2
FROM table
FETCH {FIRST | NEXT} row_count {ROW | ROWS} {ONLY | WITH TIES};

Breaking this down:

• FIRST/NEXT – Start fetching from beginning or current position
• row_count – Number of rows to return
• ONLY/WITH TIES – Additional qualifiers on rows fetched

For simple row retrieval, ONLY and WITH TIES aren’t required. This gets the first 10 rows:

SELECT * FROM students FETCH FIRST 10 ROWS;

The powerful thing about FETCH is it doesn’t limit the underlying query result set size. So additional FETCH calls can retrieve further subsets:

-- First 10 rows
FETCH FIRST 10 ROWS; 

-- Next 10 rows  
FETCH NEXT 10 ROWS;

This makes FETCH very useful for batched retrieval of results for processing. And since the total rows aren’t capped like with LIMIT, the query doesn’t need to be rewritten to reach additional rows.

Some key FETCH features:

• Batched retrieval – FETCH in a loop to retrieve large result sets in chunks
• Row numbering – Use ROW_NUMBER() to dynamically identify row subsets for FETCH
• No total limit – Underlying query result set can contain any number of rows

So in summary, FETCH provides more flexibility over result sets, while LIMIT sets a maximum size cap.

Best Practices for Using LIMIT and FETCH

When constraining SQL result sets, keep these tips in mind:

• Use LIMIT early in development – Adding a low LIMIT while testing queries prevents unexpected server load. Make sure performance remains good as the limit is raised.
• Small batches with FETCH – Processing chunks of a few hundred or thousand rows with FETCH avoids memory problems. Combine with ORDER BY and ROW_NUMBER() for orderly batched retrieval.
• Watch for ties on FETCH – The WITH TIES option includes extra rows with the same “tie-breaker” values as the last fetched row. Useful for keeping groups intact.
• Limit secondary result sets – Queries in stored procedures can produce multiple result sets. Apply LIMIT to secondary ones that may be large.
• Implement security checks – Don’t rely solely on LIMIT and FETCH to restrict unauthorized access to rows. Additional security layers are still important.
• Check and set database fetch size – Some databases have a default fetch size that should be tuned for performance. FETCH then controls rows within that.

Properly leveraging LIMIT and FETCH takes some practice – but can optimize queries and deliver user results quickly. Now let’s go over some troubleshooting tips.

Common Errors and Troubleshooting

When adding LIMIT or FETCH to SQL queries, watch for these potential issues:

No rows returned

Make sure the OFFSET or FETCH FIRST row number hasn’t passed the available rows from earlier fetches. This may require resetting the query or result set position.

Unexpected extra or missing rows

The WITH TIES fetch option can grab additional rows compared to just using ONLY. Double check this behavior is intended, or remove WITH TIES.

Performance decrease

LIMIT and FETCH retrieve only a portion of rows, but the server query execution may still pull and process all rows before applying the limits. Tune indexing and query conditions to restrict row processing early.

Duplicate rows

Unlike LIMIT, repeated FETCH calls can retrieve overlapping row ranges. Deduplicate programmatically if needed by tracking identifiers of rows already seen.

Proper error handling around LIMIT and FETCH clauses ensures robust application behavior when working with constrained result sets.

FAQ

Should I always use LIMIT in my queries?

No, LIMIT isn’t required or useful in every query. With well-optimized search conditions that match only a few rows, LIMIT may be unnecessary. But for queries that scan large tables or join multiple ones, limiting rows is wise.

When is FETCH better than LIMIT?

Use FETCH when you need to retrieve big result sets in manageable batches, processing them programmatically in loops until reaching the end. Unlike LIMIT, FETCH allows sequentially getting more rows.

Why does my FETCH return duplicate rows sometimes?

The FETCH clauses retrieve “windows” of rows that can overlap if you fetch row batches that aren’t sequential. Deduplicating may be required in application code if duplicates cause issues. An ORDER BY clause can enforce row ordering to avoid this.

What’s the maximum rows for LIMIT and FETCH?

There aren’t fixed limits – extremely high values can be specified! But very large limits or fetches defeat the purpose of constraining resources. Target only the rows genuinely needed by your application. For limits higher than 100,000 rows, further optimization is probably needed.

Conclusion

SQL’s LIMIT and FETCH clauses provide powerful, flexible options for constraining query result sets. By capping total rows or retrieving specific row ranges, they tame unexpected deluges of data that overwhelm applications. Mastering LIMIT and FETCH takes practice – but ultimately helps build robust database-driven systems that leverage SQL responsibly.