Post
Ask Question

@@ROWCOUNT in SQL Server

@@ROWCOUNT is a system variable that holds the number of rows affected by the most recently executed statement in a Transact-SQL (T-SQL) batch. This function is often used in scripts and stored procedures to determine the impact of a command.

This includes statements that perform.

  • Inserts (INSERT)
  • Updates (UPDATE)
  • Deletes (DELETE)
  • Specific SELECT statements (with certain conditions)

Example

-- Example table creation
CREATE TABLE tbl_Employees (
    emp_ID int,
    emp_FirstName varchar(50),
    emp_LastName varchar(50),
    emp_Department varchar(50)
);

-- Inserting some data
INSERT INTO tbl_Employees (emp_ID, emp_FirstName, emp_LastName, emp_Department)
VALUES
    (1, 'Sreenath', 'K G', 'IT'),
    (2, 'Sarath', 'Babu', 'UI'),
    (3, 'Adarsh', 'A T', 'IT');

-- Select all data from the table
SELECT * FROM tbl_Employees;

-- Update statement
UPDATE tbl_Employees
SET emp_FirstName = 'Uday', emp_LastName = 'Kumar'
WHERE emp_Department = 'UI';

-- Check the number of affected rows
SELECT @@ROWCOUNT AS AFFECTEDROWS;

Result

AFFECTED ROWS
1

It reflects the number of rows modified or retrieved, not necessarily returned to the client application.

It's only valid within the current execution batch. Subsequent statements or stored procedures can reset its value.

The Statements like USE, SET, and COMMIT TRANSACTION reset @@ROWCOUNT to 0.

The data type of @@ROWCOUNT is INT, limiting it to a maximum of 2,147,483,647 rows. For larger datasets, you can use the ROWCOUNT_BIG function, which returns a bigint value.

ROWCOUNT_BIG Example

CREATE TABLE #TEST (ID INT);

INSERT INTO #TEST (ID)
SELECT 1
UNION
SELECT 2;

SELECT ID FROM #TEST
UNION ALL
SELECT ROWCOUNT_BIG();

Output

ID
1
2
2 -- ROWCOUNT_BIG()

Common Uses

  • Checking Operation Results: You can use @@ROWCOUNT to verify if an INSERT, UPDATE, or DELETE statement modified the expected number of rows.
  • Loop Control: In loops iterating through data, @@ROWCOUNT helps determine when to stop processing after all rows are handled.

Limitations and Alternatives

  • Data Type Limitation: As mentioned earlier, @@ROWCOUNT has a data type of INT, which limits it to a maximum of around 2.1 billion rows. If you're working with very large datasets that might exceed this limit, you can use the ROWCOUNT_BIG function. This function returns a bigint data type, allowing you to handle significantly larger numbers.
  • Specificity with SELECT Statements: Not all SELECT statements affect @@ROWCOUNT. Only SELECT statements with data manipulation operations like DELETE within the SELECT or use of the TOP clause will modify @@ROWCOUNT. Simple SELECT statements for retrieving data won't change its value.

Advanced Usage Scenarios

Error Handling: You can combine @@ROWCOUNT with conditional logic (e.g., IF) to handle errors based on the number of rows affected. For instance, you could check if an UPDATE statement modified zero rows, indicating that the target data wasn't found.

Example

-- Example table
CREATE TABLE tbl_Employee (
    emp_ID INT,
    emp_FirstName NVARCHAR(50),
    emp_LastName NVARCHAR(50)
);

-- Insert statement
INSERT INTO tbl_Employee (emp_ID, emp_FirstName, emp_LastName)
VALUES (1, 'Sreenath', 'KG');

-- Check if the insert was successful
IF @@ROWCOUNT > 0
BEGIN
    PRINT 'Insert successful';
END
ELSE
BEGIN
    PRINT 'Insert failed';
END

Cursors and @@ROWCOUNT: When working with cursors, fetching data row by row, @@ROWCOUNT is usually set to 1 after each FETCH statement. This indicates that one row was retrieved.

Example

-- Example table
CREATE TABLE tbl_Employee (
    emp_ID INT,
    emp_FirstName NVARCHAR(50),
    emp_LastName NVARCHAR(50),
    emp_Salary DECIMAL(10, 2)
);

-- Insert sample data
INSERT INTO tbl_Employee (emp_ID, emp_FirstName, emp_LastName, emp_Salary)
VALUES
    (1, 'Sreenath', 'K G', 75000),
    (2, 'Sarath', 'Babu', 72000),
    (3, 'Adarsh', 'A T', 70000);

-- Declare variables
DECLARE @EmployeeID INT, @FirstName NVARCHAR(50), @LastName NVARCHAR(50), @Salary DECIMAL(10, 2);

-- Declare a cursor
DECLARE EmployeeCursor CURSOR FOR
SELECT emp_ID, emp_FirstName, emp_LastName, emp_Salary
FROM tbl_Employee;

-- Open the cursor
OPEN EmployeeCursor;

-- Fetch the first row from the cursor
FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @FirstName, @LastName, @Salary;

-- Loop through the cursor
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Update the salary (example operation)
    UPDATE tbl_Employee
    SET emp_Salary = emp_Salary * 1.1
    WHERE CURRENT OF EmployeeCursor;

    -- Check the number of rows affected by the update
    IF @@ROWCOUNT > 0
    BEGIN
        PRINT 'Salary updated for EmployeeID: ' + CAST(@EmployeeID AS NVARCHAR(10));
    END

    -- Fetch the next row from the cursor
    FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @FirstName, @LastName, @Salary;
END

-- Close and deallocate the cursor
CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;

Result

emp_ID    emp_FirstName    emp_LastName    emp_Salary
1         Sreenath        K G             82500.00
2         Sarath          Babu            79200.00
3         Adarsh          A T             77000.00

Temporary Tables and @@ROWCOUNT: Creating temporary tables doesn't affect @@ROWCOUNT. However, inserting data into temporary tables or performing other operations within them will update @@ROWCOUNT based on the number of rows impacted.

Example

-- Create a temporary table
CREATE TABLE #Temp_Employees (
    emp_ID INT,
    emp_FirstName NVARCHAR(50),
    emp_LastName NVARCHAR(50),
    emp_Salary DECIMAL(10, 2)
);

-- Check @@ROWCOUNT after creating the temporary table
PRINT 'Rows affected by CREATE TABLE: ' + CAST(@@ROWCOUNT AS NVARCHAR(10));

-- Insert data into the temporary table
INSERT INTO #Temp_Employees (emp_ID, emp_FirstName, emp_LastName, emp_Salary)
VALUES
    (1, 'Sreenath', 'K G', 75000),
    (2, 'Sarath', 'Babu', 72000),
    (3, 'Adarsh', 'A T', 70000);

-- Check @@ROWCOUNT after inserting data
PRINT 'Rows affected by INSERT: ' + CAST(@@ROWCOUNT AS NVARCHAR(10));

-- Update data in the temporary table
UPDATE #Temp_Employees
SET emp_Salary = emp_Salary * 1.1;

-- Check @@ROWCOUNT after updating data
PRINT 'Rows affected by UPDATE: ' + CAST(@@ROWCOUNT AS NVARCHAR(10));

-- Select data from the temporary table
SELECT * FROM #Temp_Employees;

-- Check @@ROWCOUNT after selecting data
PRINT 'Rows affected by SELECT: ' + CAST(@@ROWCOUNT AS NVARCHAR(10));

-- Drop the temporary table
DROP TABLE #Temp_Employees;

Result

emp_ID | emp_FirstName | emp_LastName | emp_Salary
----------------------------------------------------
   1   |  Sreenath     |    K G        |  82500.00
   2   |  Sarath       |    Babu       |  79200.00
   3   |  Adarsh       |    A T        |  77000.00

Best Practices

  • Clarity and Readability: When using @@ROWCOUNT in your T-SQL code, consider adding comments to explain its purpose and what it's checking. This improves code readability and maintainability.
  • Alternative Approaches: In some cases, using COUNT(*) within a subquery might be a more readable approach compared to relying on @@ROWCOUNT, especially for counting rows in a SELECT statement.


Similar Articles