> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# B-BEAR: Quick and Dirty SQL Server Testing
- URL: https://nolongerset.com/quick-and-dirty-sql-server-testing/
- Published: 2023-04-15T03:18:41.000Z
- Updated: 2026-05-08T12:50:30.000Z
- Description: Begin, Before, Execute, After, Rollback. This technique makes testing your SQL Server statements a snap.
- Author: Mike Wolfe
- Tags: SQL Server, Testing, Debugging, #Import 2026-05-20 02:59

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:

```sql
-- 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](https://nolongerset.com/idempotence-vs-determinism/)**: 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:

```sql
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:

```sql
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.

```sql
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.

```sql
BEGIN TRAN

SELECT * FROM MyTable

DELETE FROM MyTable WHERE MyTableID BETWEEN 29 AND 42

SELECT * FROM MyTable

COMMIT TRAN
```