Usage of rowcount in SQL

I have never been ignorant.

 

A friend asked me about the following.CodeWhy not:

 

Declare @ Rowcount Int ;
Set @ Rowcount = 10 ;
Select Top @ Rowcount * From Table

 

This is because top only accepts constants as the number parameter, but cannot accept variables. The solution provided to the other party is:

 

Declare @ SQL Nvarchar ( 4000 );
Set @ SQL = ' Select top ' + Convert ( Varchar ( 10 ), @ Rowcount ) + ' * From table '

Exec(@ SQL)

 

In the blog, I asked about rowcount usage. I checked it and said this in the document:

 

 

Enable SQL server to stop processing queries after returning the specified number of rows.

Transact-SQL syntax conventions

Syntax

Set rowcount {Number| @Number_var}

Parameters

Number| @ Number_var

The number of rows (integers) to be processed before a specific query is stopped ).

Note

Important:

In the next version of SQL Server, using set rowcount does not affect the delete, insert, and update statements. Do not use the set rowcount statement with the delete, insert, and update statements in the new development work, and be prepared to modify the application currently using it.Program. In addition, for the delete, insert, and update statements currently using set rowcount, we recommend that you use top syntax to overwrite them. For more information, see Delete (TRANSACT-SQL), insert (TRANSACT-SQL), or update (TRANSACT-SQL ).

The Set rowcount option settings are ignored for insert, update, and delete statements executed on remote tables and local and remote partition views.

To set this option to off to return all rows, set rowcount to 0.

Note:

Setting the set rowcount option will stop most Transact-SQL statements from processing when they are affected by a specified number of rows. This includes data modification statements such as triggers, insert, update, and delete. The rowcount option is invalid for dynamic cursors, but it can restrict the row set and non-differentiated cursors of the key set. Exercise caution when using this option, which is mainly used with the SELECT statement.

If the row value is small, set rowcount overwrites the top keyword of the SELECT statement.

When the insert, update, and delete statements use an explicit top expression, these statements ignore set rowcount. This includes the insert statement followed by the Select clause.

Set rowcount is set during execution or runtime, rather than during analysis.

Permission

A public role is required.

Example

Set rowcount stops processing when it reaches the specified number of rows. Note that in the following example, a total of 545 rows meetQuantityLess300. However, the number of rows returned after the update shows that not all rows have been processed. Rowcount affects all Transact-SQL statements.

	Copy code
Use adventureworks2008r2; go select count (*) as count from production. productinventory where quantity <300; go

The following is the result set:

Count

-----------

537

(1 row (s) affected)

NowRowcountSet4, AndQuantityLess300To update all rows.

	Copy code
Set rowcount 4; update production. productinventory set quantity = 400 where quantity <300; go

(4 row (s) affected)

 

As a result, he wrote a new solution to his friend's problems:

 

 

Declare @ Rowcount Int ;
Set @ Rowcount = 1 ;
Set Rowcount @ Rowcount

Select * From Table