sql limit 子句_SQL Server TOP子句概述和示例

sql limit 子句

This article explores the SQL Server TOP clause using various examples, along the way, we will also run through performance optimization while using the TOP clause in SQL Server.

本文使用各种示例探索SQL Server TOP子句，同时，我们还将在使用SQL Server中的TOP子句的同时进行性能优化。

介绍 (Introduction)

Sometimes we want to retrieve a specific number of records from the SQL table rather than retrieving all table records. We can use the WHERE clause in a SELECT statement to limit the number of rows. Suppose you have a customer table and you want to retrieve records for customers belonging to a particular country. There might be many records satisfying the condition. We require top ‘N’ customer records to satisfy the conditions. For this requirement, we can use TOP or ROW_Number() clauses.

有时我们想从SQL表中检索特定数量的记录，而不是检索所有表记录。 我们可以在SELECT语句中使用WHERE子句来限制行数。 假设您有一个客户表，并且想要检索属于特定国家/地区的客户的记录。 可能有许多满足条件的记录。 我们需要满足前N个客户记录的条件。 对于此要求，我们可以使用TOP或ROW_Number（）子句。

Let’s go ahead and explore the TOP clause and its performance aspects.

让我们继续探索TOP子句及其性能方面。

SQL Server TOP子句语法 (SQL Server TOP clause syntax)

SELECT TOP Expression | Percentage [Column_Names] [ WITH TIES ]
FROM [Table_Name]

SELECT TOP表达式| 百分比[Column_Names] [WITH TIES]
来自[Table_Name]

• Expression: We can specify a numeric expression that defines the number of rows to be returned 表达式 ：我们可以指定一个数字表达式，该表达式定义要返回的行数
• PERCENT: Here, we can use PERCENT value to return the percentage number of rows from total rows in the output PERCENT ：在这里，我们可以使用PERCENT值返回输出中总行数的百分比
• WITH TIES: We can specify the WITH TIES clause to return more rows with values that match the last row in the result set. For example, we want the top 10 customers based on the purchase amount. If we do not use WITH TIES, SQL Server returns exactly 10 records while we might have more customers with a similar purchase cost. In this case, we can use WITH TIES to return more rows. We must use WITH TIES along with the ORDER BY Clause WITH TIES：我们可以指定WITH TIES子句以返回更多行，这些行的值与结果集中的最后一行相匹配。 例如，我们希望基于购买金额来排名前10位的客户。 如果我们不使用WITH TIES，则SQL Server会恰好返回10条记录，而我们可能会有更多的顾客以相似的购买成本。 在这种情况下，我们可以使用WITH TIES返回更多行。 我们必须将WITH TIES与ORDER BY子句一起使用

Let’s look at a few examples of the SQL Server TOP clause for [HumanResources.Employee] table in the AdventureWorks sample database.

让我们看一下AdventureWorks示例数据库中[HumanResources.Employee]表SQL Server TOP子句的一些示例。

示例1：具有恒定值SQL Server TOP子句 (Example 1: SQL Server TOP Clause with a constant value)

In this example, we retrieve the top 10 records from a SQL table without specifying any condition and ORDER BY Clause.

在此示例中，我们从SQL表中检索了前10条记录，而未指定任何条件和ORDER BY子句。

SELECT TOP (10) *
FROM HumanResources.Employee;  
GO

Let’s filter the output and retrieve only relevant columns. We can specify the required column names in the select statement.

让我们过滤输出并仅检索相关列。 我们可以在select语句中指定所需的列名称。

SELECT TOP (10) NationalIDNumber, 
                JobTitle, 
                BirthDate, 
                HireDate
FROM HumanResources.Employee;  
GO

In the next query, we specify an ORDER BY Clause to sort results in descending order of birthdate column.

在下一个查询中，我们指定一个ORDER BY子句，以将结果按生日日期列的降序排序。

SELECT TOP (10) NationalIDNumber, 
                JobTitle, 
                BirthDate, 
                HireDate
FROM HumanResources.Employee
ORDER BY BirthDate DESC;
GO

If we do not specify the DESC clause, it sorts result in ascending order, and the top statement shows the required data.

如果我们未指定DESC子句，则它将结果按升序排序，并且最上面的语句显示所需的数据。

SELECT TOP (10) NationalIDNumber, 
                JobTitle, 
                BirthDate, 
                HireDate
FROM HumanResources.Employee
ORDER BY BirthDate;
GO

示例2：具有PERCENT值的TOP子句 (Example 2: TOP Clause with a PERCENT value)

We can use PERCENT in a TOP statement to retrieve N PERCENT of rows in the output. The following query returns 2 percent of rows in the output.

我们可以在TOP语句中使用PERCENT来检索输出中N行的行。 以下查询返回输出中2％的行。

SELECT TOP (2) PERCENT NationalIDNumber, 
                JobTitle, 
                BirthDate, 
                HireDate
FROM HumanResources.Employee
ORDER BY BirthDate;
GO

We have a total of 290 records in the [HumanResources.Employee].

[HumanResources.Employee]中共有290条记录。

SELECT COUNT(*) AS TotalRecords FROM HumanResources.Employee;

Two percent of 290 is 5.8 that is a fractional value, so SQL Server rounds output to six rows (next whole number).

290的2％是5.8，这是一个小数，因此SQL Server将输出舍入为六行（下一个整数）。

示例3：SQL Server TOP子句和WITH TIES子句 (Example 3: SQL Server TOP Clause and WITH TIES clause)

In the following query, we retrieve records from the SQL table order by [SickLeaveHours] column.

在以下查询中，我们按[SickLeaveHours]列从SQL表顺序检索记录。

SELECT TOP (15) * FROM HumanResources.Employee
ORDER BY SickLeaveHours;
GO

We get 15 rows in the output, as shown below.

我们在输出中获得15行，如下所示。

Let’s execute the previous query by adding the WITH TIES clause.

让我们通过添加WITH TIES子句来执行上一个查询。

SELECT TOP (15) WITH TIES * FROM HumanResources.Employee
ORDER BY SickLeaveHours;
GO

We get 18 records in the output despite adding the TOP 15 clause. In the output, we have multiple records for SickLeaveHours = 22, therefore WITH TIES clause adds all records have the same values and we get 18 records now in the output.

尽管添加了TOP 15子句，我们仍然在输出中获得18条记录。 在输出中，我们有SickLeaveHours = 22的多个记录，因此WITH TIES子句将所有记录添加为相同的值，现在在输出中获得18个记录。

示例4：TOP子句和SQL Delete语句 (Example 4: TOP Clause and SQL Delete statement)

We can use TOP Clause in a SQL delete statement as well. We should use the ORDER BY clause in the delete statement to avoid unnecessary deletion of data.

我们也可以在SQL delete语句中使用TOP子句。 我们应该在delete语句中使用ORDER BY子句，以避免不必要的数据删除。

DELETE FROM [dbo].[SalesData]
WHERE CustomerId IN  
   (SELECT TOP 10 CustomerId   
    FROM [dbo].[SalesData]  
    ORDER BY OrderDate ASC);  
GO

In the above query, we want to retrieve the top 10 customers’ records in [orderdate] ascending order. Execute the query, and it deleted 13 records. We specified the TOP 10 clause, so it should not delete more than 10 records.

在上面的查询中，我们要按[orderdate]升序检索前10位客户的记录。 执行查询，它删除了13条记录。 我们指定了TOP 10子句，因此它不应删除超过10条记录。

It might delete more records if the column specified in the TOP Clause contains a duplicate value. Therefore, we should use a delete statement with TOP carefully and use it with the primary key column only.

如果在TOP子句中指定的列包含重复值，则它可能会删除更多记录。 因此，我们应该对TOP谨慎使用delete语句，并将其仅与主键列一起使用。

示例5：TOP子句和SQL Insert语句 (Example 5: TOP Clause and SQL Insert statement)

The following query inserts the top 10 records from a select statement into [TempInsert] table. First, let’s create a SQL table using the CREATE TABLE statement with relevant data types.

以下查询将select语句中的前10条记录插入[TempInsert]表中。 首先，让我们使用带有相关数据类型的CREATE TABLE语句创建一个SQL表。

CREATE TABLE TempInsert
([FirstName] VARCHAR(50), 
 [LastName]  VARCHAR(50), 
 [JobTitle]  VARCHAR(150)
);
 
INSERT INTO TempInsert
OUTPUT inserted.[FirstName], 
       inserted.[LastName], 
       inserted.[JobTitle]
       SELECT TOP 10 [FirstName], 
                     [LastName], 
                     [JobTitle]
       FROM [AdventureWorks].[HumanResources].[vEmployee]
       ORDER BY BusinessEntityID DESC;

We use the OUTPUT clause to display the records inserted in the TempInsert table. Executing the query gives the following result. We can use TOP Clause in insert statement as well, but it is recommended to use it in the Select statement. If we use it in the INSERT clause, it might cause performance issues, especially for a large table.

我们使用OUTPUT子句显示在TempInsert表中插入的记录。 执行查询将得到以下结果。 我们也可以在insert语句中使用TOP子句，但建议在Select语句中使用它。 如果在INSERT子句中使用它，则可能会导致性能问题，尤其是对于大表。

示例6：SQL Server TOP子句和SQL UPDATE语句 (Example 6: SQL Server TOP Clause and SQL UPDATE statement)

We can use TOP Clause in a SQL Update statement as well to restrict the number of rows for an update. Essentially, it is a combination of the select statement and update. In the following query, we use the TOP clause in the select statement and update the values in the [HumanResources.Employee] table.

我们也可以在SQL Update语句中使用TOP子句来限制更新的行数。 本质上，它是select语句和update的组合。 在以下查询中，我们在select语句中使用TOP子句，并更新[HumanResources.Employee]表中的值。

UPDATE HumanResources.Employee
  SET 
      SickLeaveHours = SickLeaveHours - 10
FROM
(
    SELECT TOP 10 BusinessEntityID
    FROM HumanResources.Employee
    ORDER BY HireDate ASC
) AS th
WHERE HumanResources.Employee.BusinessEntityID = th.BusinessEntityID;  
GO

示例7：select语句的变量中的TOP子句 (Example 7: The TOP clause in a variable of a select statement)

We can use a variable to define the number of records in the TOP statement. In the following query, we defined an int variable @i and specified value 10 for it. Later, we use this variable in the TOP clause.

我们可以使用变量定义TOP语句中的记录数。 在以下查询中，我们定义了一个int变量@i并为其指定了值10。 稍后，我们在TOP子句中使用此变量。

DECLARE @i AS INT= 10;  
SELECT TOP (@i) [FirstName], 
                [LastName], 
                [JobTitle]
FROM [AdventureWorks].[HumanResources].[vEmployee]
ORDER BY BusinessEntityID DESC;  
GO

SQL Server TOP子句的性能优化 (Performance optimization for SQL Server TOP clause)

For this section, let’s generate some test data and use the TOP clause in the select statement.

对于本节，让我们生成一些测试数据，并在select语句中使用TOP子句。

CREATE TABLE TestTable
(ID      INT
 PRIMARY KEY CLUSTERED , 
 ZIP     INT, 
 Remarks CHAR(3000)
);
GO
DECLARE @i INT;
SET @i = 1;
WHILE @i < 60000
    BEGIN
        INSERT INTO TestTable
        VALUES
        (@i, 
         RAND() * 200000, 
         'Sample Data'
        );
        SET @i = @i + 1;
    END;
GO

Now, clear the buffer cache, enable the actual execution plan in SSMS and execute the following query.

现在，清除缓冲区高速缓存，在SSMS中启用实际的执行计划，然后执行以下查询。

DBCC FREEPROCCACHE
SELECT TOP 100 *
FROM TestTable
WHERE ID < 30000
ORDER BY ZIP;

Now, let’s run another select statement after clearing the buffer cache and view the execution plan.

现在，让我们在清除缓冲区高速缓存并查看执行计划后运行另一个select语句。

DBCC FREEPROCCACHE;
SELECT TOP 1001 *
FROM TestTable
WHERE ID < 30000
ORDER BY ZIP;

The execution plan is the same; however, we see a warning in the sort operator. Sort operator also took 1.205s in comparison to 0.112s of the previous execution.

执行计划是相同的。 但是，我们在sort运算符中看到一个警告。 与之前执行的0.112秒相比，排序运算符也花费了1.205秒。

Once we hover the mouse over the sort operator, it shows that the sort operator used tempdb to spill data. It read and write 1977 pages from the tempdb spill.

将鼠标悬停在sort运算符上后，它表明sort运算符使用tempdb溢出数据。 它从tempdb溢出中读取和写入1977页。

In many cases, this sort operator and TempDB spill might cause performance issues, and we should eliminate it using proper indexes. We should never ignore the TempDB spill in the query execution plan. In the above image, we can see it caused the TempDB spill due to the ZIP column. It is the same column for which we want to sort the data.

在许多情况下，这种排序运算符和TempDB溢出可能会导致性能问题，我们应该使用适当的索引来消除它。 我们永远不应忽略查询执行计划中的TempDB溢出。 在上图中，我们可以看到由于ZIP列导致TempDB溢出。 我们要对数据进行排序的是同一列。

For this example, let’s create a Non-clustered index on the ZIP column

对于此示例，让我们在ZIP列上创建非聚集索引

CREATE NONCLUSTERED INDEX INX_NC_TestTable ON TestTable(ZIP);

Once we have created the index, update the statistics with FULLSCAN so that query optimizer can use this newly created index.

创建索引后，请使用FULLSCAN更新统计信息，以便查询优化器可以使用此新创建的索引。

UPDATE STATISTICS TestTable WITH FULLSCAN;

Let’s rerun the select statement that caused the TempDB spill.

让我们重新运行导致TempDB溢出的select语句。

DBCC FREEPROCCACHE;
SELECT TOP 1001 *
FROM TestTable
WHERE ID < 30000
ORDER BY ZIP;

In the execution plan, we do not have a sort operator, and it uses a NonClustered scan along with clustered key lookup. It also eliminated the TempDB spill.

在执行计划中，我们没有排序运算符，它使用NonClustered扫描以及集群键查找。 它还消除了TempDB泄漏。

结论 (Conclusion)

In this article, we get a glimpse of the SQL Server TOP clause using various examples. We also looked at the costly sort operator and its performance implications. You should always look at the workload, tune the query and create proper indexes.

在本文中，我们将使用各种示例来一窥SQL Server TOP子句。 我们还研究了昂贵的排序运算符及其性能含义。 您应该始终查看工作负载，调整查询并创建适当的索引。

翻译自: https://www.sqlshack.com/sql-server-top-clause-overview-and-examples/

sql limit 子句