B-BEAR: Quick and Dirty SQL Server Testing

Mike Wolfe -

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 ROLLBACK to 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