CODEWITHSUNDEEP
sqlsql-serverrowcount
wk2752
wk2752

Reputation: 167

Initial value of @@ROWCOUNT varies across databases and servers

We are attempting to run the a DELETE statement inside a WHILE loop (to avoid large transaction logs for lots of rows) as follows:

WHILE (@@ROWCOUNT > 0)
    BEGIN
        DELETE TOP (250000)
        FROM
            MYDATABASE.MYSCHEMA.MYTABLE
        WHERE
            MYDATABASE.MYSCHEMA.MYTABLE.DATE_KEY = 20160301
    END

When this command is executed inside a new SQL Server Management Studio connection in our development environment, it deletes rows in blocks of 250K, which is the expected behavior.

When this command is executed in the same way on our test server, we get the message

Command completed successfully

That is, the WHILE loop was not entered when the statement was run.

After some additional investigation, we have found that the behavior also varies depending on the database that we connect to. So if the code is run (in our test environment) while SQL Server Management Studio is connected to MYDATABASE, the DELETE statement does not run. If we run the code while connected to SOME_OTHER_DATABASE, it does.

We partially suspect that the value of @@ROWCOUNT is not reliable, and may be different for different connections. But when we run the code multiple times for each database & server combination, we see behavior that is 100% consistent. So random initial values of @@ROWCOUNT do not appear to explain things.

Any suggestions as to what could be going on here? Thanks for your help!

Edit #1

For those asking about the initial value of @@ROWCOUNT and where it is coming from, we're not sure. But in some cases @@ROWCOUNT is definitely being initialized to some value above zero, as the code works on a fresh connection as-is.

Edit #2

For those proposing the declaration of our own variable, for our particular application we are executing SQL commands via a programming language wrapper which only allows for the execution of one statement at a time (i.e., one semicolon).

We have previously tried to establish the value of @@ROWCOUNT by executing one delete statement prior to the loop:

Statement #1:

DELETE TOP (250000)
FROM
    MYDATABASE.MYSCHEMA.MYTABLE
WHERE
    MYDATABASE.MYSCHEMA.MYTABLE.DATE_KEY = 20160301

Statement #2 (@@ROWCOUNT is presumably now 250,000):

WHILE (@@ROWCOUNT > 0)
    BEGIN
        DELETE TOP (250000)
        FROM
            MYDATABASE.MYSCHEMA.MYTABLE
        WHERE
            MYDATABASE.MYSCHEMA.MYTABLE.DATE_KEY = 20160301
    END

However, whatever is causing @@ROWCOUNT to take on a different value on start-up is also affecting the value between commands. So in some cases the second statement never executes.

Upvotes: 0

Views: 107

Answers (3)

Justin Cave
Justin Cave

Reputation: 231661

Have you traced the session? Since @@ROWCOUNT returns the number of rows affected by the prior statement in the session, I would guess that either the last query SSMS executes as part of establishing the session returns a different number of rows in the two environments or that you have a login trigger in one or the other environments whose last statement returns a different number of rows. Either way, a trace should tell you exactly why the behavior is different.

Fundamentally, though, it makes no sense to refer to @@ROWCOUNT before you run the statement that you are interested in getting a count for. It's easy enough to fix this using a variable

DECLARE cnt integer = -1;
WHILE (cnt != 0)
BEGIN
  DELETE TOP (250000)
    FROM MYDATABASE.MYSCHEMA.MYTABLE
   WHERE MYDATABASE.MYSCHEMA.MYTABLE.DATE_KEY = 20160301;
  SET cnt = @@ROWCOUNT;
END

Upvotes: 0

David T. Macknet
David T. Macknet

Reputation: 3162

Where are you getting your initial @@ROWCOUNT from? I mean, you're never going to enter that block, because @@ROWCOUNT would be expected to be zero, so you'd never enter the loop. Also, deleting in 250K batches wouldn't change the size of your transaction log - all of the deletions will be logged if you're logging, so there's no benefit (and some penalty) for doing this w/in a loop.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You should not use a variable before you have set its value. That is equally true for system variables.

The code that you have is very dangerous. Someone could add something like SELECT 'Here I am in the loop' after the delete and it will break.

A better approach? Use your own variable:

DELCARE @RC int;
WHILE (@RC > 0 OR @RC IS NULL)
    BEGIN
        DELETE TOP (250000)
        FROM MYDATABASE.MYSCHEMA.MYTABLE
        WHERE MYDATABASE.MYSCHEMA.MYTABLE.DATE_KEY = 20160301;

        SET @RC = @@ROWCOUNT;
    END;

Upvotes: 2

Related Questions