We have the answers to your questions! - Don't miss our next open house about the data universe!

SQL ROWCOUNT: Everything you need to know about SQL formulas

- Reading Time: 3 minutes
sql row

SQL databases usually contain several hundred or even several thousand lines. But very often, modifications, selections, updates and other instructions only affect a few rows. To find out exactly how many rows have been affected by your various operations, you can use SQL ROWCOUNT. Deciphering.

SQL ROWCOUNT to count rows

@ROWCOUNT is an SQL query designed to return the number of rows affected by the last statement executed. This can be any SQL statement, such as INSERT, UPDATE, DELETE, SELECT…

For SQL ROWCOUNT to function correctly, it is imperative that both queries (ROWCOUNT and the statement affecting the rows) are in the same execution. If they are executed separately, the value returned will be 1, which does not correspond to the number of rows affected by the previously executed statement.

The aim of @@ROWCOUNT is to avoid infinite loops when a database is analyzed in a loop. When all rows have been processed, SQL ROWCOUNT automatically stops the analysis process.

In addition to processing data in a loop, this query can also be used to check the number of rows allocated in a statement. In this way, ROWCOUNT facilitates error handling.


💡Related articles:

SQL INSERT INTO: Insert data with this SQL query
SQL DELETE: How do I use this query?
SQL Developer: What is it? What’s it for?
SQL Tutorial: Top 5 Most Useful Methods
SQL joins: everything you need to know about table associations
SQL vs NoSQL: differences, uses, advantages and disadvantages

5 examples of using SQL ROWCOUNT

SQL ROWCOUNT can be executed with any type of instruction. To help you understand, here are a few examples.


Simultaneous use of SQL ROWCOUNT and SELECT identifies the number of rows selected by the initial query. Here’s how it manifests itself:
FROM [table_name] SELECT @@ROWCOUNT The number of rows affected by the SELECT query will then appear at the bottom of the table. It is also possible to call @@ROWCOUNT after executing two SELECT commands during the same operation. For example:
FROM [table_1_name] SELECT top 100 FROM [table_name_1] – [table_name_2] In this case, the SQL query ROWCOUNT only considers the second statement.


By calling SQL ROWCOUNT after using a UPDATE query, you’ll be able to identify the number of rows updated during this operation.

Here’s how this combination looks:

UPDATE [table_name]

SET [column_name] = ‘new value’

WHERE condition


Here again, the number of updated rows appears below the table.


The combination of ROWCOUNT and DELETE identifies the number of lines deleted.

The syntax is as follows:

DELETE [table_name]

WHERE condition



With the INSERT query, the SQL command ROWCOUNT indicates the number of rows added.

Here’s the basic syntax:


VALUES ('value 1', 'value 2', ...)


It’s also possible to run batch queries by adding the GO command.

For example:


VALUES (‘value 1’, ‘value 2’, ‘value 3’)

GO 10


When there are several simultaneous queries in a single operation, SQL ROWCOUNT only takes the last query into account. In this example, even if the INSERT query is repeated 10 times, the ROWCOUNT command will only show 3 modifications, since this is the number of values modified by the last query.

This rule applies to all statements, whether INSERT. SELECT, UPDATE, DELETE, etc.


SQL ROWCOUNT can also be used with IF to control the flow of the instruction. Depending on the number of lines affected, it will be possible to add a specific message. For example:
FROM [table_name] IF @@ROWCOUNT > 10 PRINT “message_1” ELSE PRINT “message_2”. In this example, this means: select the top 100 from [table_name]. If the number of rows is greater than 10, write message_1, otherwise, write message_2. In this case, as we’re selecting a top 100, the number of rows will indeed be greater than 10, so message_1 will appear.

Join DataScientest to master the SQL language

SQL ROWCOUNT is just one of the many possibilities offered by the SQL language. There are also updates, deletions, additions, conditions, triggers…

Mastering this language is a fundamental skill for any professional who needs to learn how to manage relational databases with fluidity. To do this, you need to combine theory (to know all the available queries) and practice (to apply them on a case-by-case basis). This is precisely what we offer at DataScientest with our numerous data-related training courses. Find out more!

You are not available?

Leave us your e-mail, so that we can send you your new articles when they are published!
icon newsletter


Get monthly insider insights from experts directly in your mailbox