Use caution when you're specifying the TOP clause in a query that contains a UNION, UNION ALL, EXCEPT, or INTERSECT operator. In this scenario, it's important to ensure that all successive batches target new rows.
SQL TOP 5 FULL
Because the MERGE statement does a full table scan of both the source and target tables, I/O performance can be affected when you use the TOP clause to modify a large table by creating multiple batches. Or, one may be deleted, five updated, and four inserted, and so on. For example, if specifying TOP (10) affects 10 rows, of these rows, seven may be updated and three inserted. That is, there's no order in which the rows are distributed among the actions defined in the WHEN clauses. The TOP clause further reduces the number of joined rows to the specified value and the insert, update, or delete actions apply to the remaining joined rows in an unordered way. And, the joined rows that don't qualify for an insert, update, or delete action are removed. When specified in the MERGE statement, the TOP clause applies after the entire source table and the entire target table are joined. Because you can include the TOP clause in the view definition, certain rows may disappear from the view if the rows no longer meet the requirements of the TOP expression due to an update. SQL Server allows for updating rows through views. For example, if an INSERT TRIGGER fires as the result of an INSERT statement that used a TOP clause. The inserted and deleted tables in the triggers return only the rows that are truly affected by the INSERT, UPDATE, MERGE, or DELETE statements. The TOP expression doesn't affect statements that might run because of a trigger. Doing so provides consistency with its required use in INSERT, UPDATE, MERGE, and DELETE statements. We recommend that you always use parentheses for TOP in SELECT statements. Because you use SET ROWCOUNT outside of a statement that runs a query, its value can't be considered in a query plan.įor backward compatibility, the parentheses are optional in SELECT statements if the expression is an integer constant. As a part of a SELECT statement, the query optimizer can consider the value of expression in the TOP or FETCH clauses during query optimization.These methods are preferred over using SET ROWCOUNT for the following reasons: Use TOP (or OFFSET and FETCH) instead of SET ROWCOUNT to limit the number of rows returned. For more information, see ORDER BY Clause (Transact-SQL). A paging solution (that is, sending chunks or "pages" of data to the client) is easier to implement using OFFSET and FETCH clauses. Use OFFSET and FETCH in the ORDER BY clause instead of the TOP clause to implement a query paging solution. Because, it's the only way to predictably indicate which rows are affected by TOP. In a SELECT statement, always use an ORDER BY clause with the TOP clause. The returned order of tying records is arbitrary. You can specify the TOP clause with the WITH TIES argument only in SELECT statements, and only if you've also specified the ORDER BY clause. For example, if expression is set to 5 but two additional rows match the values of the ORDER BY columns in row 5, the result set will contain seven rows. WITH TIES might cause more rows to be returned than the value specified in expression. You must use this argument with the ORDER BY clause. Returns two or more rows that tie for last place in the limited results set. Fractional values are rounded up to the next integer value. Indicates that the query returns only the first expression percent of rows from the result set. Otherwise, expression is converted to bigint. expression is implicitly converted to a float value if you specify PERCENT. The numeric expression that specifies the number of rows to be returned. To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.