July 11, 2011 at 10:19 pm
I would like to test a delete statement, but I don't want to commit it yet.
I know I can use a select statment to do exactly the records that delete will do, so that I can confirm.
Can I also use:
Begin tran;
then run the delete statement, then I can see how many rows are deleted, and I also can do a select from the table to see if those rows are deleted,
Above is a test.
SO then I want to rollback the delete.
I use Rollback,
is this the right way to test a delete?
And when everything is confirmed, then I ran commit.
July 11, 2011 at 10:27 pm
That's exactly what I do.
The only other option I can think of involves restoring to a point in time prior to your delete - definitely more cumbersome than using BEGIN TRANS ; DELETE;ROLLBACK and the restore option also requires more privileges.
July 11, 2011 at 10:46 pm
You can, but remember the old irish saying "There is many a slip between the cup and the lip"
Set up a test database or sandbox as I call it
Test your T-SQL
If it works as you require it to work
Backup the production database
then run the T-SQL in the production database.
July 12, 2011 at 7:34 am
One thing you can also do is something like:
CREATE TABLE #Test
(
ID INT IDENTITY PRIMARY KEY,
ID2 INT
)
INSERT INTO #Test (ID2)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
DECLARE @TempDeletedTable TABLE
(
ID INT,
ID2 INT
)
BEGIN TRANSACTION
BEGIN TRY
DELETE FROM #Test OUTPUT DELETED.* INTO @TempDeletedTable WHERE ID2 > 4
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
ROLLBACK
SELECT * FROM #Test
SELECT * FROM @TempDeletedTable
The cool thing about using a table variable is that a ROLLBACK does not remove the records from it. So, you can keep what was deleted. You can even then insert it into a real table outside of the TRANSACTION block, so you can store the records for later analysis.
One very important thing though - if you use the BEGIN TRANSACTION - ROLLBACK approach, MAKE SURE YOU ALSO PUT A TRY-CATCH BLOCK IN! If for whatever reason an error occurs in the statements being executed inside the transaction block, the ROLLBACK statement will never be reached, which means the transaction will not roll back!
July 12, 2011 at 8:11 am
kramaswamy (7/12/2011)
If for whatever reason an error occurs in the statements being executed inside the transaction block, the ROLLBACK statement will never be reached, which means the transaction will not roll back!
In that case the transaction will just remain open and you can run rollback manually. SQL doesn't automatically commit if there was a begin transaction and no commit or rollback.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 12, 2011 at 8:26 am
Oh? cool, didn't know that. But does the transaction remain open only for the current session in question? IE, if you were using SSMS, and you began a transaction which then stopped because of an error, then you closed the window you were looking at, would the transaction automatically rollback or commit, or what would happen? The first ROLLBACK or COMMIT it encountered would trigger it, regardless of the context?
July 12, 2011 at 8:33 am
transactions are connection based so if the connection is closed before the commit then everything is rolled back.
The probability of survival is inversely proportional to the angle of arrival.
July 12, 2011 at 4:22 pm
Here is a neat form of the DELETE query shown above that I find useful at times:
WITH cte
AS (
SELECT *
FROM #Test
WHERE ID2 > 4
)
DELETE FROM cte
OUTPUT DELETED.*
INTO @TempDeletedTable ;
It looks a little odd at first but it allows us to highlight just the SELECT portion in the CTE and run it to see which rows will be affected by the delete without making any code changes...and it has the exact same plan as the standard DELETE syntax.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 12, 2011 at 4:58 pm
Thanks all, great tips.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy