TSQL TOP Usage

The first part: TSQL Top has two uses

1, limits the number of rows or total rows returned by the query result set.

When the top is used in conjunction with the ORDER BY clause, the result set is limited to the first n sorted rows, otherwise the first n rows are returned in undefined order.

2, limit the number of rows affected by the INSERT, UPDATE, MERGE, or DELETE statements.

Part Two: Discussion of the second usage of top

1, the sample code is as follows

Create Tabledbo. Financemonth (Monthnumint, Quantityint); withCte as(    Select 1  asMonthnum, -  asQuantityUnion  All    SelectMonthnum+1, quantity+ -     fromCTEwhereMonthnum< A)Insert  intodbo. FinancemonthSelect * fromCte

2, use set ROWCOUNT to limit the number of rows returned, but Ms does not recommend this way, "in future versions of SQL Server, using set ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements."

SET ROWCOUNT 2 Select *  from dbo. Financemonth-- equivalent to selecttop2* from Dbo. Financemonth

Cancel limit, specify set ROWCOUNT to 0

SET ROWCOUNT 0

3, use top to limit the number of rows affected by Delete, INSERT, and update, and the number or percentage following top must be enclosed in parentheses, otherwise an error occurs.

Delete Top(2) fromdbo. FinancemonthUpdate Top(2) dbo. FinancemonthSetQuantity=1Insert Top(2) intodbo. FinancemonthSelect *  fromDbo. Financemonth

TSQL TOP Usage