{"id":182526,"date":"2024-03-19T10:41:00","date_gmt":"2024-03-19T09:41:00","guid":{"rendered":"https:\/\/liora.io\/en\/?p=182526"},"modified":"2026-02-12T16:47:51","modified_gmt":"2026-02-12T15:47:51","slug":"sql-rowcount-everything-you-need-to-know-about-sql-formulas","status":"publish","type":"post","link":"https:\/\/liora.io\/en\/sql-rowcount-everything-you-need-to-know-about-sql-formulas","title":{"rendered":"SQL ROWCOUNT: Everything you need to know about SQL formulas"},"content":{"rendered":"\n

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.<\/strong><\/p>\n\n\n\n

SQL ROWCOUNT to count rows<\/h2>\n\n\n\n

@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…<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

?Related articles:<\/p>\n\n\n\n

SQL INSERT INTO: Insert data with this SQL query<\/a><\/p>\n\n\n\n

SQL DELETE: How do I use this query?<\/a><\/p>\n\n\n\n

SQL Developer: What is it? What’s it for?<\/a><\/p>\n\n\n\n

SQL Tutorial: Top 5 Most Useful Methods<\/a><\/p>\n\n\n\n

SQL joins: everything you need to know about table associations<\/a><\/p>\n\n\n\n

SQL vs NoSQL: differences, uses, advantages and disadvantages<\/a><\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n
\n
Learn to use SQL ROWCOUNT<\/a><\/div>\n<\/div>\n\n\n

5 examples of using SQL ROWCOUNT<\/h2>\n\n\n\n

SQL ROWCOUNT can be executed with any type of instruction. To help you understand, here are a few examples.<\/p>\n\n\n

ROWCOUNT and SELECT<\/h3>\n\n\n\n

Simultaneous use of SQL ROWCOUNT and SELECT identifies the number of rows selected by the initial query.
Here’s how it manifests itself:<\/p>\n\n\n\n

SELECT TOP (100)<\/b><\/pre>\n\n\n\n
FROM [table_name]<\/b>
SELECT @@ROWCOUNT<\/b>
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:<\/p>\n\n\n\n
SELECT TOP (100)<\/b><\/pre>\n\n\n\n
FROM [table_1_name]<\/b>
SELECT top 100<\/b>
FROM [table_name_1] – [table_name_2]<\/b>
In this case, the SQL query ROWCOUNT only considers the second statement.<\/p>\n\n\n
ROWCOUNT and UPDATE<\/h3>\n\n\n\n
By calling SQL ROWCOUNT after using a UPDATE<\/a> query, you’ll be able to identify the number of rows updated during this operation.<\/p>\n\n\n\n
Here’s how this combination looks:<\/p>\n\n\n\n
UPDATE [table_name]<\/b><\/pre>\n\n\n\n
SET [column_name] = ‘new value’<\/b><\/p>\n\n\n\n
WHERE condition<\/b><\/p>\n\n\n\n
SELECT @@ROWCOUNT<\/b><\/p>\n\n\n\n
Here again, the number of updated rows appears below the table.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\n
Master SQL ROWCOUNT<\/a><\/div>\n<\/div>\n\n\n
ROWCOUNT and DELETE<\/h3>\n\n\n\n
The combination of ROWCOUNT and DELETE<\/a> identifies the number of lines deleted.<\/p>\n\n\n\n
The syntax is as follows:<\/p>\n\n\n\n
DELETE [table_name]<\/b><\/pre>\n\n\n\n
WHERE condition<\/b><\/p>\n\n\n\n
SELECT @@ROWCOUNT<\/b><\/p>\n\n\n
ROWCOUNT and INSERT<\/h3>\n\n\n\n
With the INSERT query, the SQL command ROWCOUNT indicates the number of rows added.<\/p>\n\n\n\n
Here’s the basic syntax:<\/p>\n\n\n\n
INSERT INTO table <\/b>\nVALUES ('value 1', 'value 2', ...)<\/b>\nSELECT @@ROWCOUNT<\/b><\/pre>\n\n\n\n
It’s also possible to run batch queries by adding the GO command.<\/p>\n\n\n\n
For example:<\/p>\n\n\n\n
INSERT INTO table <\/b><\/pre>\n\n\n\n
VALUES (‘value 1’, ‘value 2’, ‘value 3’)<\/b><\/p>\n\n\n\n
GO 10<\/b><\/p>\n\n\n\n
SELECT @@ROWCOUNT<\/b><\/p>\n\n\n\n
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.<\/p>\n\n\n\n
This rule applies to all statements, whether INSERT. SELECT, UPDATE, DELETE, etc.<\/p>\n\n\n
ROWCOUNT and IF<\/h3>\n\n\n\n
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:<\/p>\n\n\n\n
SELECT TOP (100)<\/b><\/pre>\n\n\n\n
FROM [table_name]<\/b>
IF @@ROWCOUNT > 10 <\/b>
PRINT “message_1”<\/b>
ELSE<\/b>
PRINT “message_2”. <\/b>
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.<\/p>\n\n\n
Join Liora to master the SQL language<\/h2>\n\n\n\n
SQL ROWCOUNT<\/strong> is just one of the many possibilities offered by the SQL language. There are also updates, deletions, additions, conditions, triggers…<\/p>\n\n\n\n
Mastering this language is a fundamental skill for any professional who needs to learn how to manage relational databases<\/strong> 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 Liora with our numerous data-related training courses. Find out more!<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
\n
Discover our SQL training<\/a><\/div>\n<\/div>\n\n\n\n