Usage of rowcount and @ @Rowcount in SQL Server

Using ROWCOUNT

The function of rowcount is to restrict the subsequent SQL to stop processing after returning the specified number of rows.

RowCount settings are valid throughout the session

SET ROWCOUNT 10
SELECT * FROM dbo. Customer ORDER by id DESC

When you are finished, you can set it to:

SET ROWCOUNT 0

Indicates that the following query or other operation can operate all,

Otherwise, the number set to 10 may also be used in the following code

Because this setting: Set ROWCOUNT 10 is for the entire session

Use top:

Cannot add dynamic parameter, only with integer

Select Top Ten * FROM dbo. Customer ORDER by ID DESC

If you want to add, you need to use a spelling string

Like what:

DECLARE @n int
declare @sql nvarchar (1000)
Set @n=10
Set @sql = ' Select Top ' +cast (@n as varchar) + ' * FROM dbo. Customer '
EXEC (@sql)

Less performance and readability than rowcount

In addition the role of ROWCOUNT

can also be used to modify and delete

@ @rowcount Returns the number of rows affected by the last SQL statement

Select Top 2 * from Customer
SELECT @ @Rowcount

--Returns 2 if the table exists with data greater than or equal to 2
--If 1 or 0, the @ @rowcount return is also 1 or 0

Note: Deleting a modified query will return the number of rows affected

This thing uses more of the other side is: Trigger

For example

Create trigger Ti_tablea on Customer after update
As
If @ @rowcount =0 return
......

If the number of rows affected by this table is 0, the trigger exits directly and does not continue down

The second place could be recursion or looping.

DECLARE @n int
Set @n=1
SELECT * from client_goods where [email protected]

While @ @rowcount >0
Begin
Set @[email protected]+1
SELECT * from client_goods where [email protected]
End

Usage of rowcount and @ @Rowcount in SQL Server