Sql-server – How to check how many rows a query will affect before executing it

sql serversql server 2014sql-server-express

I'm writing an application to interface with my database (SQL Server 2014 SP1 Express), and there are two sections of the application that worry me slightly. The first involves an UPDATE:

UPDATE Jobs
SET JobStatus = 'Running' --there's also an UPDATE where this is set to 'Completed'
WHERE (ExperimentID = @currentID);

And a DELETE:

DELETE from Simulations
WHERE (ExperimentID = @currentID);

These seem like fine statements at a glance, but if there's something wrong with how @currentID is passed from my application to the DB, I could possibly lose all information about which Jobs have been run, and more gravely, all of my simulation data.

How do I protect these queries from deleting/updating more than @n rows? The UPDATE should really only update one row at a time, so I'd like that to be a restriction. There's also a column in the Simulations table that could be used to restrict how many the DELETE should be getting rid of, by doing:

SELECT MAX(CycleCount)+1 AS numRows
FROM Simulations
WHERE ExperimentID = @currentID

So what do you think? How best do I sanitize these queries to prevent them from doing damage in the event that my application fails?

There are three tables in this DB: Jobs, Parameters, and Simulations. ExperimentID is a primary key for both Jobs and Parameters, and they are both the same size (each job has a corresponding set of parameters). There's more detail about Parameters in my post history if you're interested.

The Simulations Table stores results from a simulation at each step of the simulation. So the primary key is then formed not just by ExperimentID (as theres many many steps per simulation), but by the compound of ExperimentID and CycleCount.

I'm trying to account for situations where something gets corrupted in transfer (highly unlikely, but you never know), and situations where a simulation terminates prematurely (loses connection with DB, computer loses power, etc). ExperimentID in both Jobs and Parameters is determined by IDENTITY(1,1). Hope this clears up a bit of what I'm hoping to accomplish here.

@currentID is programmed as an integer. It is pulled initially from the database using:

SELECT MIN(ExperimentID) FROM Jobs WHERE JobStatus = 'ToRun'

Best Answer

If ExperimentID is the primary key or has unique constraint/index, there is no need to limit the number of rows updated since there will never be more than one row updated. If ExperimentID is not unique by itself, I suggest you add additional criteria to identify the desired row. The TOP approach you provided will limit the number of rows updated for non-unique values but the one updated will be arbitrary unless your order by additional columns such that the updated row deterministic.

To validate no more than the expected number of rows are modified, one method is to perform the DML in a transaction and rollback if the number of rows is greater than expected. Below is a parameterized example of this technique:

SET XACT_ABORT ON;

BEGIN TRY

    BEGIN TRAN;

    UPDATE Jobs
    SET JobStatus = @NewJobStatus
    WHERE ExperimentID = @currentID;

    DECLARE @RowCount int = @@ROWCOUNT;

    IF @RowCount > @MaxRowCount
    BEGIN
        RAISERROR ('Attempt to update %d rows, more than the %d row(s) allowed.  Action not performed.', 16, 1, @RowCount, @MaxRowCount);
    END;

    COMMIT;

END TRY
BEGIN CATCH

        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;

END CATCH;
Related Question