The "B-BEAR" is one of my favorite techniques for testing a complicated query or stored procedure in SQL Server.
B-BEAR stands for Begin, Before, Execute, After, Rollback.
Here's the basic concept:
-- 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
I like this approach for several reasons:
- No setup : all you need is some existing data
- Safe: the rollback means you can test without worrying about side effects
- Idempotent: you can safely re-run the script as many times as you need
- Immediate feedback: in SSMS, you'll see the output of the SELECT queries in the Results tab and counts of the rows affected in the Messages tab
- Side-by-side comparison: the Before/After output is stacked vertically in the Results tab and Messages tab making direct comparisons easy
- Performance testing: the Messages tab shows a Completion time, making this a convenient way to iteratively test performance tweaks
How-To: Step by Step Directions
The most important thing is to start with the
BEGIN TRAN...ROLLBACK lines:
BEGIN TRAN ROLLBACK
As long as you start with that, the order of the rest of the steps is not important. Personally, though, I like to add the Before and After SELECT queries next:
BEGIN TRAN SELECT * FROM MyTable SELECT * FROM MyTable ROLLBACK
After that, I add the Execute section. This can be a single statement or a complicated set of steps. It could be the creation and execution of an entire stored procedure.
BEGIN TRAN SELECT * FROM MyTable DELETE FROM MyTable WHERE MyTableID BETWEEN 29 AND 42 SELECT * FROM MyTable ROLLBACK
Occasionally, I have to make changes to live production data. In those situations, I really like this technique because it allows me to confirm that the changes I want to make are going to work exactly as I intend. And once I've confirmed everything is working as intended, I change the last line from
COMMIT TRAN and apply the changes.
BEGIN TRAN SELECT * FROM MyTable DELETE FROM MyTable WHERE MyTableID BETWEEN 29 AND 42 SELECT * FROM MyTable COMMIT TRAN