tsqlt FakeTable not rolling back after unit test executed

I have an extremely simple unit test where I am testing the functionality of a tsqlt.FakeTable. However, after running the command and inserting data, it does not roll back to the previous state.

Current table:

[product01].[batch]

After executing the below code, the table is not rolled back to the above. It overrides the data and I can see the temp object created under tables.

CREATE PROCEDURE [test_product].[test Given batch size of 1000 and batch qty of 2 When sp_insert_batch is called Then two entries populated in batch table]

AS

BEGIN

DECLARE @batch_count INT = 2
DECLARE @batch_size INT = 1000
DECLARE @job_number NVARCHAR(10) = '1000000000'


DECLARE @actual_batch_count INT
DECLARE @actual_batch_size INT

-- Arrange

EXEC tSQLt.FakeTable '[product01].[batch]'

INSERT INTO product01.batch(batch_id,batch_qty,date_from, date_to, job_number)
VALUES('6003',100,'2020-06-01', '2020-06-30','5000000000')


    
-- Act


-- Assert


END

Selecting the batch table after the above code has run:

I haven’t written the actual assert logic, but I am confused as to why the table is not being rolled back. Any thoughts on this?

Answer

tSQLt tests cannot be executed directly. You need to always use one of the run methods like tSQLt.Run

If you call the test directly as in EXEC your.test;, no rollback will happen and other tSQLt features won’t work either.