Using PRINT to Improve Safety of Live SQL Server Updates
One of my more underrated posts was the B-BEAR technique I use to test changes to production SQL Server data before making them:
I now use this technique whenever I adjust live data.
Here's the tl;dr version:
-- BEGIN: Begin a transaction
BEGIN TRAN
-- BEFORE: One or more SELECT queries to show the starting state of the data
SELECT * FROM MyTable
SELECT * FROM MySubTable
-- EXECUTE: Execute some stored procedure or INSERT/UPDATE/DELETE query
EXEC dbo.MySproc
-- AFTER: Re-run the same SELECT queries as above to show the end state
SELECT * FROM MyTable
SELECT * FROM MySubTable
-- ROLLBACK: Roll back the transaction so we can
-- tweak the Execute step and re-test
ROLLBACK Checking the "Rows Affected" Counts
One of the benefits of using this technique is you can make sure you're not accidentally changing more data than you intend:
To that end, I always check the "Messages" tab of SSMS while developing my T-SQL script (i.e., while using ROLLBACK TRAN). The "Messages" tab shows how many records are affected by each SQL statement.
For example, let's say I have five records I want to update. Using the B-BEAR technique, I would execute three SQL statements:
- BEFORE: A SELECT query that returns the five records as they currently exist in the database
- EXECUTE: An UPDATE statement that makes changes to those five records
- AFTER: The same SELECT query as above, showing the changes were applied as I expected
When I look at the Messages tab, I should see this:
(5 rows affected)
(5 rows affected)
(5 rows affected)If I see something like this, I know there's a problem (and I'm really glad I tested first):
(5 rows affected)
(794 rows affected)
(5 rows affected)Executing Multiple Statements
Sometimes I need to make changes to multiple tables.
If I'm updating three tables, I suddenly have nine messages:
- 3 messages for the SELECTs (BEFORE)
- 3 messages for the UPDATEs (EXECUTE)
- 3 messages for the SELECTs (AFTER)
In those situations, though, I often include additional queries in the BEFORE section. And I'll often have INSERT or DELETE queries in the EXECUTE section. Before long, I've got more than a dozen messages with different numbers of records being INSERTed, DELETEd, or UPDATEd. Keeping track of which counts go with which statements gets a bit hairy.
Here's a recent example from a particularly thorny issue I'm trying to resolve:
(5 rows affected)
(5 rows affected)
(5 rows affected)
(10 rows affected)
(2 rows affected)
(5 rows affected)
(24 rows affected)
(5 rows affected)
(5 rows affected)
(5 rows affected)
(5 rows affected)
(29 rows affected)Most of those messages pertain to SELECT queries, but in the middle of all that are two UPDATE statements and an INSERT statement. Which ones are they? Beats me.
PRINT Statement to the Rescue
Rather than rely on counting SQL statements and matching things up manually, I add PRINT statements to my B-BEAR script.
The T-SQL PRINT statement is equivalent to the VBA Debug.Print statement.
Here's an example:
PRINT 'About to update 5 records in the Memberships table'
UPDATE Memberships ...
PRINT 'About to update 5 "DD" records in the Invoices table'
UPDATE Invoices ...
PRINT 'About to insert 5 Adjustment records ("AJ") in the Payments table'
INSERT INTO Payments ...Now, when I execute this test script, this is what my Messages tab looks like:
(5 rows affected)
(5 rows affected)
(5 rows affected)
(10 rows affected)
(2 rows affected)
(5 rows affected)
(24 rows affected)
About to update 5 records in the Memberships table
(5 rows affected)
About to update 5 "DD" records in the Invoices table
(5 rows affected)
About to insert 5 Adjustment records ("AJ") in the Payments table
(5 rows affected)
(5 rows affected)
(29 rows affected)Unfortunately, SSMS inserts a blank line before each row count line in the Messages pane, so the PRINT message doesn't group nicely with the row count.
You could get around this by rewriting the PRINT messages ("About to update..." > "Just updated...") and placing them after their respective SQL statements. I don't think it's worth the tradeoff of having the PRINT statement come after its SQL statement in the SQL editing pane, though.