In this article, we will explore the concept of SQL SELECT TOP queries and how they can be used to limit the result set of a query. We will also provide examples to reinforce the learning.

Introduction

The TOP clause in SQL Server allows us to limit the number of rows returned by a query. It can be used to retrieve a specific number of rows or a percentage of rows from a table. The TOP clause is often used in conjunction with the ORDER BY clause to ensure a certain order of the result set.

Syntax

The syntax of the TOP clause is as follows:

SELECT TOP (expression) [PERCENT] [WITH TIES]
FROM table_name

The expression parameter defines how many rows are returned from the query. For example, to retrieve the first 10 rows of a table, we can set the expression as 10.

Examples

Let’s look at some examples to understand how the TOP clause works.

Example 1: Retrieving a specific number of rows

SELECT TOP (5) Name, ProductNumber, StandardCost
FROM Production.Product;

In this example, we retrieve 5 random rows from the Product table.

Example 2: Retrieving a percentage of rows

SELECT TOP (20) PERCENT Name, ProductNumber, StandardCost
FROM Production.Product;

In this example, we retrieve 20 percent of the rows from the Product table.

Example 3: Using the WITH TIES keyword

SELECT TOP (1) WITH TIES Name, ProductNumber, StandardCost
FROM Production.Product
ORDER BY StandardCost DESC;

The WITH TIES keyword allows us to include rows in the result set that have the same value as the last row. In this example, the query will return all rows that have the same cost as the highest cost product.

Using Variables with the TOP Clause

We can also use variables with the TOP clause to dynamically set the number of rows to be returned. Here’s an example:

DECLARE @Val AS INT
SET @Val = 3

SELECT TOP (@Val) Name, ProductNumber, StandardCost
FROM Production.Product
ORDER BY StandardCost DESC;

In this example, we assign a value to the variable @Val and the query will return the first @Val rows that match the assigned value.

Using TOP with UPDATE and DELETE Statements

The TOP clause can also be used with UPDATE and DELETE statements to limit the number of rows affected by the operation.

UPDATE Statement

UPDATE TOP (10) Production.ProductListColors
SET Color = 'Pink';

In this example, the update statement will only affect the first 10 rows of the ProductListColors table.

DELETE Statement

DELETE TOP (1)
FROM Production.ProductListColors;

In this example, a random row will be deleted from the ProductListColors table.

Comparison with SET ROWCOUNT and ROW_NUMBER

There are alternative ways to achieve similar results as the TOP clause.

SET ROWCOUNT

The SET ROWCOUNT option limits the number of rows returned from a query. However, it is important to note that it does not consider the query optimizer and may have worse performance compared to the TOP clause.

SET ROWCOUNT 4

SELECT Name, ProductNumber, StandardCost
FROM Production.Product;

In this example, the query will process the entire result set but only return 4 rows.

ROW_NUMBER Function

The ROW_NUMBER function assigns temporary numbers to the result set of a query. It can be used as an alternative to the TOP clause.

SELECT *
FROM (
    SELECT Name, ProductNumber, StandardCost, ROW_NUMBER() OVER (ORDER BY StandardCost DESC) AS RN
    FROM Production.Product
) AS TMP_TBL
WHERE RN <= 4;

In this example, the query will return the first 4 rows of the Product table.

Conclusion

In this article, we have discussed the SQL SELECT TOP statements and how they can be used to limit the result set of a query. We have also explored some alternatives to the TOP clause. Understanding the TOP clause is essential for controlling the number of rows returned by a query and optimizing query performance.