Rowcount and @ @Rowcount in SQL Server

The use of ROWCOUNT

The function of rowcount is to restrict the subsequent SQL to stop processing after returning the specified number of rows, such as the following example,

SET ROWCOUNT 10select * FROM Table A

Such a query will only return the first 10 data in table A. It acts as a "select top * from Table a". Note that SET ROWCOUNT settings are valid throughout the session. such as the following SQL example:

SET ROWCOUNT 10select * FROM table agoselect * from table B

Both table A and Table B return only the first 10 data.

To cancel the SET ROWCOUNT qualification, SET ROWCOUNT 0 is all you need.

From the above example, it seems that rowcount does not have much use, limit the data of the query results, we use top to be able to, and do not have to worry about if forget to cancel the rowcount's setting and the effect on the subsequent SQL. But in the following circumstances, rowcount's settings will bring us a lot of convenience oh.

We all know that no arguments can be added to the select top, only a number with a specific int type. If we want to implement the function of the parameter in the back of top, we only construct the SQL string and then use exec to execute it. Like what:

DECLARE @n intdeclare @sql nvarchar (+) set @n=10set @sql = ' select Top ' +cast (@n as varchar) + ' * from Table a ' exec (@sql)

Not to mention the performance of exec in the above statement, from the readability of SQL is very unfriendly. But if we use rowcount to solve this, it will be very elegant, because the SET rowcount can use parameters later. Examples are as follows:

DECLARE @n intset @n=10set rowcount @nselect * FROM Table A

Note:the qualification of SET ROWCOUNT is as valid as the modification and deletion. such as the following example:

SET ROWCOUNT 10update Table a set qty=10 where id<100

In this way, the above statement modifies only the first 10 data of id<100 in table A (assuming that id<100 has more than 10 data)

Delete is the same

SET ROWCOUNT 10delete from Table A

Thus, the above statement will only delete the first 10 data in table A

How to use @ @Rowcount

@ @Rowcount and rowcount look like, only two different @, but their function is not the same, @ @Rowcount is mainly to return the last SQL statement affected by the number of rows of data, such as:

Select top 2 * from table Aselect @ @Rowcount

If the amount of data in table A is greater than or equal to 2, then the SELECT @ @Rowcount returns 2, if only 1 or 0 data, then the SELECT @ @Rowcount will return 1 or 0

Note that the @ @Rowcount is not understood to return only the number of results of the query, delete, modify, add new statements, and return the @ @Rowcount value correctly. For example:

Update table A set gid= ' a ' where gid= ' a ' SELECT @ @Rowcount

If the data for gid= ' a ' exists in table A, then the SELECT @ @Rowcount returns the number of rows of data it modifies, and if there is no data for gid= ' A ', then the SELECT @ @Rowcount returns 0, and the deletion is the same as the new one.

So, where do we use @ @Rowcount?

One, may we see @ @Rowcount figure Most of the place is the trigger, a good trigger, usually at the front with the IF @ @rowcount =0 return statement, such as:

Create trigger Ti_tablea on TableA after updateasif @ @rowcount =0 return ...

Thus, if the number of data rows modified by TableA is 0, then the trigger Ti_tablea exits without executing the subsequent code.

The second possible place is that we can use @ @rowcount for recursion or looping. such as the following example:

DECLARE @n intset @n=1select * from client_goods where [email protected]while @ @rowcount >0beginset @[email protected]+1 SELECT * from Client_goods where [email protected]end

This example is to query client_goods whether there is id=1 data, if any, and then query whether there is id=2 data, continue to check until the ID is not continuous. Of course, when you look at this example, do not consider the meaning of this example, it just shows that @ @rowcount can be used as a cyclic condition.

Rowcount and @ @Rowcount in SQL Server