none
Checking how many records will be affected by an update

    Question

  • Is there a "non-destructive" method for seeing how many records will be affected by a specific update query?

    I'd rather not edit the query itself for this, or roll back a transaction - the idea is to "test" various queries before committing. 

    Perhaps a T-SQL parameter can be set?

    Thanks,

    Kevin

     

     

    Wednesday, December 01, 2010 6:10 PM

Answers

  • I'd like for it to function automatically - guess I could write some code to parse and modify the SQL but hopefully there's an easier way...

    Maybe use SET IMPLICIT_TRANSACTIONS ON before running, then look at @@rowcount before a rollback?

    In essence, what I want to do is create a "shell" that will help to identify problem update queries before committing...

    Thanks,

    Kevin 


    you can use SET IMPLICIT_TRANSACTION ON and rollback later  no problem assuming if you want to play around and have fun in your developement database 

    However, I would discourage you to do this in your production environment, implicit transaction are evil in terms of blocking it creates so they are turned off by default in SQL server.

    As Ken already suggested, in my production enviornment I change my delete/update query to a select query just to make sure that is indeed what I want to do before running a delete/update

     

    Wednesday, December 01, 2010 7:24 PM
  • Hi,

     

    You could consider using trigger if you want to check updates, although this will of course hurt your performance. Triggers can be disabled though. Inside the trigger you can have logic that test the update before executing it.

     

    Cheers

    Wednesday, December 01, 2010 9:34 PM
  • It is in your list of what you don't want, but copy your update statement, convert it to a SELECT COUNT(*) statement and execute that.
    Wednesday, December 01, 2010 6:12 PM
    Moderator

All replies

  • It is in your list of what you don't want, but copy your update statement, convert it to a SELECT COUNT(*) statement and execute that.
    Wednesday, December 01, 2010 6:12 PM
    Moderator
  • I'd like for it to function automatically - guess I could write some code to parse and modify the SQL but hopefully there's an easier way...

    Maybe use SET IMPLICIT_TRANSACTIONS ON before running, then look at @@rowcount before a rollback?

    In essence, what I want to do is create a "shell" that will help to identify problem update queries before committing...

    Thanks,

    Kevin

     

     

     

    Wednesday, December 01, 2010 7:12 PM
  • I'd like for it to function automatically - guess I could write some code to parse and modify the SQL but hopefully there's an easier way...

    Maybe use SET IMPLICIT_TRANSACTIONS ON before running, then look at @@rowcount before a rollback?

    In essence, what I want to do is create a "shell" that will help to identify problem update queries before committing...

    Thanks,

    Kevin 


    you can use SET IMPLICIT_TRANSACTION ON and rollback later  no problem assuming if you want to play around and have fun in your developement database 

    However, I would discourage you to do this in your production environment, implicit transaction are evil in terms of blocking it creates so they are turned off by default in SQL server.

    As Ken already suggested, in my production enviornment I change my delete/update query to a select query just to make sure that is indeed what I want to do before running a delete/update

     

    Wednesday, December 01, 2010 7:24 PM
  • Hi,

     

    You could consider using trigger if you want to check updates, although this will of course hurt your performance. Triggers can be disabled though. Inside the trigger you can have logic that test the update before executing it.

     

    Cheers

    Wednesday, December 01, 2010 9:34 PM