1

I would like to run a query but just to test it and have the possibility to go back to previous state I saw rollback transaction.

do I have just to run this command before ?

delete * from myTables
where colName = "***"

4 Answers 4

2

Yes, you can use BEGIN TRANSACTION ... ROLLBACK TRANSACTION to check certain queries.

I changed the double quotes (") to the valid single quotes, or apostrophes ('), and the delete * to only delete as to match the SQL Server syntax.

CREATE TABLE mytables(id INT identity(1,1) ,colName NVARCHAR(255));
INSERT INTO mytables(colName)
VALUES('***');
INSERT INTO mytables(colName)
VALUES('bla');

BEGIN TRANSACTION
DELETE FROM myTables
WHERE colName = '***';
SELECT * FROM myTables; -- Result #1
ROLLBACK TRANSACTION

SELECT * FROM myTables; -- Result #2

Result #1

id  colName
2   bla

Result #2

id  colName
1   ***
2   bla

However, sepupic is entirely right about the risk of issuing a delete on a big table. I would only do this if I wanted to test if I needed to know exactly how long the delete would take, logging included. In most cases the select is better for testing purposes.

2

You can't rollback a transaction once it has been committed, so you can't run this and change your mind some time later. I do sometimes use this pattern to test the results of ad-hoc update statements in SSMS before I run them properly:

BEGIN TRANSACTION
SET XACT_ABORT OFF
GO
-- statements to test the current state of the data for instance
SELECT COUNT(*) FROM TableThatShouldBeEmpty
-- statements to update data
DELETE TableThatShouldBeEmpty
-- statements to test the current state of the data after the changes
SELECT COUNT(*) FROM TableThatShouldBeEmpty
GO
ROLLBACK TRANSACTION

Once you are happy with the results change ROLLBACK to COMMIT.

This is far from 100% safe though:

Still be very careful with this in production or shared dev/test/other instances though: long-running updates will still consume resources (IO, CPU, memory) so may slow down other users and locks will be held as needed so you can still cause the delays and other issues (potentially deadlocks) that your data changing statements normally would.

Also be careful if the code you are testing calls other procedures that may cause your transaction to be rolled back and don't include extra batches (extra GOs) - you might find a called procedure kills the transaction but the next batch runs leaving there no transaction for your ROLLBACK to affect so some of your tests end up getting applied permanently and not rolled back.

As always, if you can test in a separate environment that would be much safer.

To be able to rollback after the transaction is committed, you need to take a backup first and your rollback plan is to restore the DB from that. Unfortunately that will rollback all other activity too so it is probably not suitable in shared or production environments.

2

Are you sure you are using SQL Server? The correct syntax of delete should look like this:

delete from myTables
where colName = '***'

To test the result you need not to delete smth at all, you can use an appropriate select, for your question the select would be this one:

select *
from myTables
where colName <> '***'

While it can be not a problem to begin tran, do a delete and then rollback for a table with 10 rows in test environment, it can be a problem on large volumes of data or in concurrent environment, where you'll lock your table in absence of index on colname, and delete (and its rollback) will generate a lot of transaction log since delete is fully logged operation in any recovery model.

1

If you just want to see affected records change delete to select and examine the output. If you want to use transactions, make sure autocommit is not set in your database (just to be sure). Then depending on SQL engine you are using it will be something like

begin transaction
delete from t1 where attribute1 = 'xy'
select * from t1

-- commit 
OR
--rollback

if you are ok with the result type commit to confirm the transaction, if not rollback the transaction. If you're toying with something like this please always do it on non-production database.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.