How to use ROWCOUNT in SQL Server?




When to use ROWCOUNT?

The ROWCOUNT function returns the number of rows affected by the last statement involving any select, delete, update, insert commands in SQL Server.

ROWCOUNT is typically used in loops and handling errors.

Syntax

@@ROWCOUNT  

The output is an int value. If the value is estimated to be bigger than 2 billion, use ROWCOUNT_BIG instead, then the output will be bigint.

Here are the common ways ROWCOUNT is used in a SQL statement:

• Set @@ROWCOUNT to the number of rows affected or read. Rows may or may not be sent to the client.
• Preserve @@ROWCOUNT from the previous statement execution.
• Reset @@ROWCOUNT to 0 but do not return the value to the client.

Example

We have table Course

CourseID	Title	Credits	DepartmentID
1045	Calculus	4	7
1050	Chemistry	4	1
1061	Physics	4	1
2021	Composition	3	2
2030	Poetry	2	2
2042	Literature	4	2
3141	Tri nometry	4	7
4022	Microeconomics	3	4
4041	Macroeconomics	3	4
4061	Quantitative	2	4

We run these three statements consecutively:

SELECT TOP 5 * FROM Course;
SELECT @@ROWCOUNT;
SELECT @@ROWCOUNT;

The result will be:

• For the 1st query:

CourseID	Title	Credits	DepartmentID
1045	Calculus	4	7
1050	Chemistry	4	1
1061	Physics	4	1
2021	Composition	3	2
2030	Poetry	2	2

• For the 2nd query:

last_affected_1
5

• For the 3rd query:

last_affected_2
1

So the first select statement read through the first 5 rows of the table Course, thus the returned value for the second statement is 5.

For the third statement, the SELECT statement right before it read only one row, so the returned value is 1.

---

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS