SQL Server Temp Table vs. Table Variable

This post was written by ChatGPT. I used ChatGPT as a natural language search engine to learn more about the modern differences between temp tables and table variables in SQL Server. This is ChatGPT's summary of that conversation.

 

Temp Tables vs. Table Variables in SQL Server: Stop Arguing, Start Measuring

Every SQL Server team eventually has this debate:

Should we use a temp table or a table variable?

And like most long-running arguments in engineering, the answer is:

It depends — but not in the way people think.

Let’s simplify this.

---

First: They Both Live in tempdb

There’s a persistent myth that table variables are “in memory” and temp tables are “on disk.”

That’s not how SQL Server works.

Unless you explicitly use memory-optimized objects, both temp tables and table variables use tempdb.

So if your argument starts with:

“Table variables are faster because they’re in memory…”

It’s already off track.

---

The Real Difference: Statistics

This is the dividing line.

Temp Tables (#Temp)

• Automatically create and maintain statistics

• Support indexes (clustered and nonclustered)

• Give the optimizer accurate cardinality estimates

• Can trigger recompiles when row counts change

In short: the optimizer understands them.

---

Table Variables (@Table)

• Historically had no meaningful statistics

• Often get a low row-count estimate

• Can produce bad join strategies under load

• Don’t trigger recompiles as data volume changes

This is where performance problems come from.

Not storage.
Not memory.
Not logging.

Cardinality estimation.

---

Why This Matters

SQL Server chooses join strategies based on row estimates.

If it thinks a table has 1 row when it actually has 50,000:

• You’ll get nested loops instead of hash joins

• You’ll scan more than you should

• You’ll watch CPU spike and blame the wrong thing

Temp tables give the optimizer better information.
Table variables often hide it.

---

SQL Server 2019/2022 Improvements

Recent versions improved table variable estimation (deferred compilation), which helps.

But it doesn’t magically make them equal to temp tables in complex workloads.

The rule still holds:

If the row count is non-trivial or the query is complex, temp tables usually produce more stable and predictable plans.

---

When Table Variables Make Sense

They’re not evil. They’re just specialized.

Use them when:

• Row counts are small (think dozens, maybe low hundreds)

• You’re inside a function and can’t use temp tables

• The data is truly simple and short-lived

• You want limited scope and simpler lifecycle

They shine in small, controlled scenarios.

They struggle in large, evolving pipelines.

---

When Temp Tables Are the Right Tool

Use temp tables when:

• You’re joining multiple large sets

• You need indexing

• You need reliable cardinality estimates

• You reuse intermediate results multiple times

• Plan stability matters

Yes, there’s tempdb overhead.

But modern SQL Server versions have significantly improved tempdb performance. For most real systems, the bottleneck isn’t tempdb allocation — it’s bad plans.

---

The Pattern I Trust

If I don’t know the final row count yet?

I default to a temp table.

Because I’d rather pay a small, predictable cost up front than debug a production incident caused by a bad estimate.

---

The Anti-Pattern

The worst pattern is this:

DECLARE @Results TABLE (...);

INSERT @Results
SELECT ...
FROM LargeTable
JOIN OtherLargeTable ...

It works in dev.
It works with 200 rows.
It melts under 200,000.

Then someone adds an index hint.
Then someone rewrites it.
Then you’re refactoring under pressure.

All because the optimizer was flying blind.

---

The Decision Framework

Instead of arguing syntax, ask:

1. How many rows might this hold?

2. Will I join against it?

3. Will I reuse it?

4. Does plan stability matter here?

If the answers trend toward “large / complex / reused,”
use a temp table.

If they trend toward “small / simple / scoped,”
a table variable is fine.

---

Final Thought

This isn’t about micro-optimizing.

It’s about giving the optimizer the information it needs to do its job.

Temp tables give it visibility.
Table variables limit it.

Pick accordingly.

And as always:

Measure first.
Optimize second.

 

Test-Driven Development in SQL Server

This blog post was originally published on the ThoroughTest website, back when that was a thing. As a co-founder and primary content contributor for ThoroughTest, I absolutely own the rights to this post and the source code to which it refers. I intend to reproduce each blog post here on my personal blog since the company is no longer in business.

We've heard from a few developers working with SQL Server that they're having a hard time justifying test-driven development of their "code" when a lot of their logic resides in stored procedures. That's a valid point: why use test-driven development (or write unit tests at all) if you can't cover the entire codebase? Well, that's a larger topic that we may discuss in a future post, but for now we can resolve the issue by introducing a method for unit testing SQL Server objects with the tSQLt testing framework.

For starters, you can check out the official documentation of tSQLt by visiting their website. We'll use the rest of this post to show a few simple ways we can do test-driven development using the framework. Keep in mind that this is a limited overview of what tSQLt provides and can do so just because you don't see a solution for your scenario here doesn't mean they don't have one. Review their official docs to get a more thorough understanding of what's possible.

For these examples we'll create a database called CookBook. You can create it on any instance of SQL Server (including Express) and any version since 2005. This database will be a repository for our recipes so we'll create two tables and one join table: Recipe, Ingredient, and RecipeIngredient. You can use the scripts below to follow along.

CREATE TABLE Recipe
  (
     Id          INT IDENTITY(1, 1),
     Name        VARCHAR(100) NOT NULL,
     DateCreated DATETIME2 DEFAULT GETDATE()
  )

ALTER TABLE Recipe
  ADD CONSTRAINT PK_Recipe_Id PRIMARY KEY CLUSTERED (Id)

CREATE TABLE Ingredient
  (
     Id          INT IDENTITY(1, 1),
     Name        VARCHAR(100) NOT NULL,
     DateCreated DATETIME2 DEFAULT GETDATE()
  )

ALTER TABLE Ingredient
  ADD CONSTRAINT PK_Ingredient_Id PRIMARY KEY CLUSTERED (Id)

CREATE TABLE RecipeIngredient
  (
     Id           INT IDENTITY(1, 1),
     RecipeId     INT NOT NULL,
     IngredientId INT NOT NULL,
     Amount       DECIMAL(4, 2) NOT NULL,
     Measurement  VARCHAR(100) NOT NULL
  )

ALTER TABLE RecipeIngredient
  ADD CONSTRAINT PK_RecipeIngredient_Id PRIMARY KEY CLUSTERED (Id)

ALTER TABLE RecipeIngredient
  ADD CONSTRAINT FK_RecipeIngredient_Ingredient FOREIGN KEY (IngredientId)
  REFERENCES Ingredient(Id)

ALTER TABLE RecipeIngredient
  ADD CONSTRAINT FK_RecipeIngredient_Recipe FOREIGN KEY (RecipeId)
  REFERENCESRecipe(Id) 

Now that we have our table structure we want to create a stored procedure that gets all the ingredients for a specific recipe in our cook book. We want to see the Ingredient Name, Amount, and Measurement for each item in the recipe and we want to specify the recipe by name. We know our requirements so now we can write our tests. Before we can start writing tests that will actually work we need to install the tSQLt framework, which is pretty simple to do. We just download the zip file, unzip it, and run SetClrEnabled.sql followed by tSQLt.class.sql on your database. Now that the framework is installed it's time to write a unit test.

The first thing we want to do is create a test class, which will be used to group our tests together. We prefer to create test classes with the name of the stored procedure followed by ".Tests" to be clear what's being tested and that it is a test class. Since a test class is just a schema we should see a new schema created after we take this action.

EXEC tsqlt.Newtestclass 'GetIngredientsByRecipeName.Tests' 

Now we have a test class for our stored procedure (which you'll notice we plan to name GetIngredientsByRecipeName) and we can create our first actual test. Tests in tSQLt are just stored procedures named a specific way so we can use our existing TSQL skills to create our tests. The first thing we need to do in our test stored procedure is setup our data, which we can do by having tSQLt create fake tables for the tables we'll need. Once we have our fake tables we can populate them with fake data. By using fake data we'll be sure that our tests will always pass without having to worry about what recipes are actually in the database. Here's what we have so far:

CREATE PROCEDURE
[GetIngredientsByRecipeName.Tests].[Test that all ingredients are returned]
AS
  BEGIN
      -- Arrange
      EXEC tsqlt.FakeTable
        'Recipe'

      EXEC tsqlt.FakeTable
        'Ingredient'

      EXEC tsqlt.FakeTable
        'RecipeIngredient'
  END 

The FakeTable procedure opens a transaction, renames the table being passed, then recreates a table with the same name and structure, but without any constraints, defaults, or triggers. After the code above runs, all three tables will be empty shells of their normal selves, allowing us to populate whatever data we want into them. We'll populate our fake tables with some fake data so we can anticipate the results of our stored procedure.

INSERT INTO Recipe(Id,Name) VALUES(1,'Grilled Cheese')

INSERT INTO Ingredient (Id, Name)
VALUES(1, 'Butter'), (2, 'Cheddar Cheese'), (3, 'White Bread')

INSERT INTO RecipeIngredient (RecipeId, IngredientId, Amount, Measurement)
VALUES(1, 1, 1, 'Tbsp'), (1, 2, 1, 'Slice'), (1, 3, 2, 'Slices')

INSERT INTO Recipe (Id, Name) VALUES(2, 'Quesadilla')

INSERT INTO Ingredient (Id, Name) VALUES(4, 'Tortilla')

INSERT INTO RecipeIngredient (RecipeId, IngredientId, Amount, Measurement)
VALUES(2, 4, 1, 'Tortilla'), (2, 2, .5, 'Cups')

Note: Even though we could normally exclude inserting values into the Id fields of Recipe and Ingredient (because they are identity fields and should automatically get the next number) we have to explicitly include them in our test because the fake tables are created without identity fields.

Now we have two recipes' worth of fake data and we know what we expect our stored procedure to do. We're going to want to compare the results of our stored procedure to what we expect so we'll create a temp table to store the results and a temp table containing our expected results.

CREATE TABLE #temp
  (
     IngredientName VARCHAR(100),
     Amount         DECIMAL (4, 2),
     Measurement    VARCHAR(100)
  )

CREATE TABLE #expected
  (
     IngredientName VARCHAR(100),
     Amount         DECIMAL (4, 2),
     Measurement    VARCHAR(100)
  )

INSERT INTO #expected
VALUES('Butter', 1, 'Tbsp'), ('Cheddar Cheese', 1, 'Slice'), ('White Bread', 2, 'Slices')

-- Act
INSERT INTO #temp
EXEC GetIngredientsByRecipeName 'Grilled Cheese' 

Finally, we'll actually compare the results of the two tables by executing the AssertEqualsTable procedure from the tSQLt framework. This procedure compares the contents of two tables for equality. Since we want to confirm multiple values across multiple rows, this option makes the most sense for us.

-- Assert
EXEC tsqlt.AssertEqualsTable '#temp', '#expected' 

Now we can create the stored procedure and run it using the Run procedure from tSQLt and passing either the test class or the test name to the procedure as a parameter. We'll use the test class name because going forward we'll want all of our tests to run whenever we make a change to our stored procedure. This is a good habit to get into now.

EXEC tsqlt.Run 'GetIngredientsByRecipeName.Tests' 

Good news; the test failed! There is no stored procedure named GetIngredientsByRecipeName yet so the test failed. We've established our first Red step in test-driven development! Create the procedure, but don't put anything in it yet.

CREATE PROCEDURE GetIngredientsByRecipeName
(
    @RecipeName VARCHAR(100)
)
AS
BEGIN
    PRINT 'Called'
END 

Run the test again and look at the output. This time, instead of getting an error message that it "could not find stored procedure 'GetIngredientsByRecipeName'" we see "(Failure) Unexpected/missing resultset rows!" and then a description of how the two tables failed to match. For more details on how to read this output, check out the tSQLt docs for AssertEqualsTable.

Let's finally modify our stored procedure to do what we want it to do: get the ingredients for the specified recipe.

CREATE PROCEDURE GetIngredientsByRecipeName
(
    @RecipeName VARCHAR(100)
)
AS
BEGIN
    SELECT
         Ingredient.Name
        ,RecipeIngredient.Amount
        ,RecipeIngredient.Measurement
    FROM Ingredient
    INNER JOIN RecipeIngredient
        ON Ingredient.Id = RecipeIngredient.IngredientId
    INNER JOIN Recipe
        ON RecipeIngredient.RecipeId = Recipe.Id
    WHERE  Recipe.Name = @RecipeName
END 

When we run our test one more time we see that it passed. Now we have our Green step so we'll review our stored procedure for any opportunities to improve. We don't see any so our Refactor step is complete without any changes.

We've got a new requirement that ingredient amounts should be summed up when the same ingredient is in the same recipe with the same measurement more than once. First we'll write the test:

CREATE PROCEDURE
  [GetIngredientsByRecipeName.Tests].
   [Test that ingredient amounts are summed]
AS
BEGIN
    -- Arrange
    EXEC tsqlt.FakeTable 'Recipe'

    EXEC tsqlt.FakeTable 'Ingredient'

    EXEC tsqlt.FakeTable'RecipeIngredient'

    INSERT INTO Recipe (Id, Name) VALUES(1, 'Salt Soup')

    INSERT INTO Ingredient (id, Name)
      VALUES (1, 'Salt'),
        (2, 'Chicken Broth'), (3, 'Carrots'), (4, 'Leather Boot')

    INSERT INTO RecipeIngredient(
      RecipeId,
      IngredientId,
      Amount,
      Measurement
    )
      VALUES (1, 1, 1, 'Tbsp'), (1, 2, 10, 'Cups'),
        (1, 3, 10, 'Carrots'), (1, 4, 1, 'Boot'), (1, 1, 16, 'Tbsp')

    CREATE TABLE #temp
      (
         IngredientName VARCHAR(100),
         Amount         DECIMAL (4, 2),
         Measurement    VARCHAR(100)
      )

    CREATE TABLE #expected
      (
         IngredientName VARCHAR(100),
         Amount         DECIMAL (4, 2),
         Measurement    VARCHAR(100)
      )

    INSERT INTO #expected
      VALUES ('Salt', 17, 'Tbsp'), ('Chicken Broth', 10, 'Cups'),
        ('Carrots', 10, 'Carrots'), ('Leather Boot', 1, 'Boot')

    -- Act
    INSERT INTO #temp
    EXEC GetIngredientsByRecipeName 'Salt Soup'

    -- Assert
    EXEC tsqlt.AssertEqualsTable '#temp', '#expected'
END 

Then we'll run all of the tests in the test class:

EXEC tsqlt.Run 'GetIngredientsByRecipeName.Tests' 

We get an exception: "(Failure) Unexpected/missing resultset rows!". We update the stored procedure:

ALTER PROCEDURE GetIngredientsByRecipeName
(
    @RecipeName VARCHAR(100)
)
AS
BEGIN
    SELECT
         Ingredient.Name
        ,SUM(RecipeIngredient.Amount)
        ,RecipeIngredient.Measurement
    FROM Ingredient
    INNER JOIN RecipeIngredient
        ON Ingredient.Id = RecipeIngredient.IngredientId
    INNER JOIN Recipe
        ON RecipeIngredient.RecipeId = Recipe.Id
    WHERE  Recipe.Name = @RecipeName
    GROUP BY
       Ingredient.Name
      ,RecipeIngredient.Measurement
END 

And finally we run all of our tests again:

EXEC tsqlt.Run 'GetIngredientsByRecipeName.Tests' 

This time our test summary shows that we have two tests that ran and both of them passed.

Stored procedures are an important part of database programming and sometimes play a large role in applications and architecture. Using the tSQLt framework we can realize the advantages of test-driven development even when we're working with SQL Server.

Unit Testing in SQL Server (Part 2a)

I mentioned in a previous post that I had written a bunch of tests to fully test the CustOrderHist stored procedure included in the Northwind database.  It turns out there were only two more.  Here they are:

CREATE PROCEDURE [CustOrderHist.Tests].[test Should Return Correct Quantity For Customer And Product]
AS
BEGIN

    -- Arrange
    -- Fake data insert statements moved to Setup stored procedure

    CREATE TABLE #Results (ProductName NVARCHAR(80), QuantityINT)

    -- Act
    INSERT INTO #Results
    EXEC dbo.CustOrderHist 'ABCDE'

    -- Assert
    DECLARE @Total INT
    SELECT @Total = QuantityFROM #Results WHERE ProductName = 'First Product'

    EXEC tSQLt.AssertEquals 513, @Total

END

CREATE PROCEDURE [CustOrderHist.Tests].[test Should Return Nothing For Customer With No Orders]
AS
BEGIN

    -- Arrange
    CREATE TABLE #Results (ProductName NVARCHAR(80), QuantityINT)

    -- Act
    INSERT INTO #Results
    EXEC dbo.CustOrderHist 'FGHIJ'

    -- Assert
    DECLARE @RowCount INT
    SELECT @RowCount = COUNT(*) FROM #Results

    EXECtSQLt.AssertEquals 0, @RowCount

END

Unit Testing in SQL Server (Part 2)

This is the second in a series of posts intended to get people up to speed using the tSQLt testing framework to test their stored procedures.  If you missed it, check out the first post here.  We'll be continuing where that one leaves off.

Now that we've got Northwind setup, we're ready to start learning what tSQLt does for us, and how it does it.  The main goal of tSQLt is to allow us to test our stored procedures.  Since stored procedures often rely on data and we can't really count on that data being in a specific state before our test, we're first going to learn how to setup our tests.

First up: test classes. A test class is just a schema in SQL Server. We use them to group our tests together. You should choose the best method for your organization when it comes to naming your test classes. At one client we decided to create a test class for each stored procedure. That way all of the tests for that stored procedure could be easily contained within a single test class. If something changed in the stored procedure we'd only have to modify the tests that were in that test class. I'll be following that convention here so I'm going to create a test class for all of the tests I write that test the functionality of dbo.CustOrderHist. I do that by running the following code on the Northwind database:

EXEC tSQLt.NewTestClass 'CustOrderHist.Tests'

Now that our test class exists we're ready to set up our data so we can test our stored procedure. When your tests run, tSQLt will first check the test class (in this case CustOrderHist.Tests) for a stored procedure named "setup" (case insensitive). If such a stored procedure is found, it is executed before each test in the test class. That makes it a great place to start setting up our data for our tests.

First, let's create the Setup procedure:

CREATE PROCEDURE [CustOrderHist.Tests].[Setup]
AS
BEGIN
END
GO

By examining CustOrderHist we can see that it relies on data from the Products, [Order Details], Orders, and Customers tables. Since we rely on that data, we'll need to set up that data before each of our tests run. Fortunately, tSQLt makes that really easy for us. We just have to execute tSQLt.FakeTable for each table. Here's the code:

CREATE PROCEDURE [CustOrderHist.Tests].[Setup]
AS
BEGIN

    EXEC tSQLt.FakeTable 'Products'
    EXEC tSQLt.FakeTable 'Order Details'
    EXEC tSQLt.FakeTable 'Orders'
    EXEC tSQLt.FakeTable 'Customers'

END

The FakeTable procedure opens a transaction, renames the table being passed, then recreates a table with the same name and structure, but without any constraints, defaults, or triggers. After the code above runs, all four tables will be empty shells of their normal selves, allowing us to populate whatever data we want into them.

The stored procedure we're testing is pretty basic so that's all the setup we're going to need.  Remember that from here on out all of the tests that we create in the CustOrderHist.Tests schema (test class) will first execute those FakeTable calls.  Now we're ready to create our first test.  For reference, this is what the CustOrderHist stored procedure actually looks like:

SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName

The first thing we're going to test is whether we get any results when we pass in a customer id that should have results.  Since a test in tSQLt is just a stored procedure we can create a test pretty darn easily.  Here's what it looks like before we actually have any contents in our test:

CREATE PROCEDURE [CustOrderHist.Tests].[test Should Return Results When Records Exist For Customer]
AS
BEGIN
END

As long as the stored procedure name starts with "test", tSQLt will pick it up as a test when the tests run.  Now that we have the test technically created, let's actually test something, shall we?  In our setup procedure we faked four tables.  Now we're going to populate those tables with some fake data to prove that our procedure under test does what we think it does:

CREATE PROCEDURE [CustOrderHist.Tests].[test Should Return Results When Records Exist For Customer]
AS
BEGIN

    -- Arrange
    INSERT INTO dbo.Products (ProductID, ProductName)
    VALUES (1, 'First Product'), (2, 'Second Product'), (3, 'Third Product')

    INSERT INTO dbo.Customers (CustomerID)
    VALUES ('ABCDE'), ('FGHIJ'), ('KLMNO'), ('PQRST')

    INSERT INTO dbo.Orders (OrderID, CustomerID)
    VALUES (1, 'ABCDE'), (2, 'ABCDE'), (3, 'PQRST'), (4, 'ABCDE')

    INSERT INTO dbo.[Order Details] (OrderID, ProductID, Quantity)
    VALUES (1, 1, 10), (2, 3, 100), (3, 2, 1000), (4, 1, 503)

    CREATE TABLE #Results (ProductName NVARCHAR(80), Quantity INT)

    -- Act

    -- Assert
END

You can see that in addition to populating fake data, I also created a temp table, which I'll use to store my results.  Now that we're all setup, we can go ahead and execute the stored procedure, putting the results into the temp table:

CREATE PROCEDURE [CustOrderHist.Tests].[test Should Return Results When Records Exist For Customer]
AS
BEGIN

    -- Arrange
    INSERT INTO dbo.Products (ProductID, ProductName)
    VALUES (1, 'First Product'), (2, 'Second Product'), (3, 'Third Product')

    INSERT INTO dbo.Customers (CustomerID)
    VALUES ('ABCDE'), ('FGHIJ'), ('KLMNO'), ('PQRST')

    INSERT INTO dbo.Orders (OrderID, CustomerID)
    VALUES (1, 'ABCDE'), (2, 'ABCDE'), (3, 'PQRST'), (4, 'ABCDE')

    INSERT INTO dbo.[Order Details] (OrderID, ProductID, Quantity)
    VALUES (1, 1, 10), (2, 3, 100), (3, 2, 1000), (4, 1, 503)

    CREATE TABLE #Results (ProductName NVARCHAR(80), Quantity INT)

    -- Act
    INSERT INTO #Results
    EXEC dbo.CustOrderHist 'ABCDE'

    -- Assert
END

The last step is to check whether what we have in our results temp table is what we expect to have there:

CREATE PROCEDURE [CustOrderHist.Tests].[test Should Return Results When Records Exist For Customer]
AS
BEGIN

    -- Arrange
    INSERT INTO dbo.Products (ProductID, ProductName)
    VALUES (1, 'First Product'), (2, 'Second Product'), (3, 'Third Product')

    INSERT INTO dbo.Customers (CustomerID)
    VALUES ('ABCDE'), ('FGHIJ'), ('KLMNO'), ('PQRST')

    INSERT INTO dbo.Orders (OrderID, CustomerID)
    VALUES (1, 'ABCDE'), (2, 'ABCDE'), (3, 'PQRST'), (4, 'ABCDE')

    INSERT INTO dbo.[Order Details] (OrderID, ProductID, Quantity)
    VALUES (1, 1, 10), (2, 3, 100), (3, 2, 1000), (4, 1, 503)

    CREATE TABLE #Results (ProductName NVARCHAR(80), Quantity INT)

    -- Act
    INSERT INTO #Results
    EXEC dbo.CustOrderHist 'ABCDE'

    -- Assert
    DECLARE @RowCount INT
    SELECT @RowCount = COUNT(*) FROM #Results

    EXEC tSQLt.AssertEquals 2, @RowCount

END

That last line runs a tSQLt procedure that compares the first parameter (expected result) to the second parameter (actual result). In this test we only wanted to see that the number of rows returned from the stored procedure was what we'd expect, and it was.
That's our first test. You run it like this:

EXEC tSQLt.Run CustOrderHist.Tests'

In order to fully test the CustOrderHist stored procedure, though, we'd need to write a few more tests. I've gone ahead and just written them and posted them here to give you an idea of what they would look like.

Unit Testing in SQL Server (Part 1)

I'm a huge advocate of unit testing your .NET code, but until recently I didn't really know it was possible to test your SQL stored procedures as well. Now I'm a huge advocate of testing those, too.  Hopefully at the end of this tutorial you'll have a solid understanding of how to use the tSQLt framework to write unit tests for your SQL code.

A couple of things to keep in mind as you consider writing unit tests for SQL:

• It's really easy to over-test in SQL; much easier than in .NET because it's tempting to test for impossible scenarios. If scenarios are impossible (because of database constraints, business rules, or some other restriction), strongly consider whether you need to test them before diving in
• Until I wrote unit tests I never considered writing stored procedures in the same way I write .NET code. I would write monolithic stored procedures instead of breaking them into reusable (and testable) pieces. It's a lot easier to test a bunch of small stored procedures than it is to test a single huge stored procedure

First I want to send you to the tSQLt website to get their rundown of this whole thing. You can check it out here.

Now that you've done that, let's get you squared away with the Northwind database. Yes, seriously. Don't look at me like that. Northwind has everything we need and it's still pretty small. It's perfect for this. You can download Northwind from here. That should have given you a backup file that you'll want to restore to a server somewhere. If you have SQLExpress installed that'll work just fine. Before you can run any tests, though, you need to run one command against the Northwind database after you restore it.

DECLARE @Command VARCHAR(MAX)


SELECT @Command = REPLACE(REPLACE@Command, '<<DatabaseName>>', sd.[name]), '<<LoginName>>', sl.[name])
FROM master..sysdatabases sd
INNER JOIN master..syslogins sl
    ON sd.[sid] = sl.[sid]
WHERE sd.[name] = DB_NAME()

Update: I went back and tried to follow these instructions and found that the above script no longer worked.  I'm leaving it here for historic purposes, but below is the script that I ran this time around.

DECLARE @User VARCHAR(50)


SELECT @User = QUOTENAME(sl.[name])
FROM master..sysdatabases sd
INNER JOIN master..syslogins sl
    ON sd.[sid] = sl.[sid]
WHERE sd.[name] = DB_NAME()

All that does is change the database so that you're the owner.

Update: I forgot to include a step here. You need to run the next bit of SQL before you run tSQLt.class.sql or it won't work properly. This is for SQL Server 2008. If you have a different version, search for SP_DBCMPTLEVEL to find out which value you should use.

EXEC SP_DBCMPTLEVEL 'Northwind', 100

OK, now that you have that, you're ready to install tSQLt. Go ahead and get the .zip file from here and extract it somewhere you can find. First run SetClrEnabled.sql on Northwind, then run tSQLt.class.sql.

At this point, tSQLt is installed on Northwind and you should be good to go to write tests. I made a couple of small changes to my instance that I'll share here. We have many users on our environment and we started to see situations where people were renaming tables outside of transactions (we'll get to that, don't worry) and it was causing us some headaches. In order to find the offenders and coach them on what they should do differently I modified a few objects. You can run the below code to make these same changes.

IF OBJECT_ID('tSQLt.Private_RenamedObjectLog') IS NOT NULL
BEGIN
    DROP TABLE tSQLt.Private_RenamedObjectLog
END
GO

CREATE TABLE tSQLt.Private_RenamedObjectLog (
    ID INT IDENTITY(1, 1) CONSTRAINT pk__private_renamedobjectlog_id PRIMARY KEY CLUSTERED
    ,ObjectId INT NOT NULL
    ,OriginalName NVARCHAR (MAX) NOT NULL
    ,[NewName] NVARCHAR (MAX) NULL
    ,RenamedBy VARCHAR(1000) NULL
    ,RenamedOn DATETIME2
)
GO

IF OBJECT_ID('tSQLt.Private_MarkObjectBeforeRename') IS NOT NULL
BEGIN
    DROP PROCEDURE tSQLt.Private_MarkObjectBeforeRename
END
GO

---Build+
CREATE PROCEDURE tSQLt.Private_MarkObjectBeforeRename
(
     @SchemaName NVARCHAR(MAX)
    ,@OriginalName NVARCHAR(MAX)
    ,@NewName NVARCHAR(MAX) = NULL
)
AS
BEGIN

    INSERT INTO tSQLt.Private_RenamedObjectLog (ObjectId, OriginalName, [NewName], RenamedBy, RenamedOn)
    VALUES (OBJECT_ID(@SchemaName +'.' + @OriginalName), @OriginalName, @NewName, SYSTEM_USER, GETDATE())

END
GO

In the next installment, we'll actually check out the framework a little more and write a very basic test.