I'm creating various unit tests for our database using the tSQLt framework. Many of our stored procedures insert records into various tables. These procedures directly call our sequences to create IDs for the records to be inserted:
SET tID = NEXT VALUE dbo.TableName_SEQ
When creating a unit test for procedures that reference this sequence, I tried to use tSQLt.RemoveObject, but receive the error:
Object '[dbo].[TableName_SEQ]' cannot be renamed because the object participates in enforced dependencies.
As the procedure I'm testing uses this sequence directly, how should I handle this? Is there any type of tSQLt object that can fake the sequence like 'FakeFunction' and input the desired ID every time it's ran?
At this point, there is not. Is the same issue as with indexed views that also use schema binding. I have started dealing with this in an automated fashion, but there is no ETA.
For now, you have two options. One is to break the schema binding manually. You can do that with the help of this statement:
SELECT
'ALTER TABLE dbo.mytable DROP CONSTRAINT '+QUOTENAME(DC.name)+';' [drop],
'ALTER TABLE dbo.mytable '+
'ADD '+
CASE WHEN DC.is_system_named = 1 THEN '' ELSE 'CONSTRAINT '+QUOTENAME(DC.name)+' ' END+
'DEFAULT '+DC.definition+' FOR '+QUOTENAME(C.name)+';' [recreate]
FROM sys.default_constraints DC
JOIN sys.columns C
ON DC.parent_object_id = C.object_id
AND DC.parent_column_id = C.column_id
WHERE parent_object_id = OBJECT_ID('dbo.mytable')
AND definition LIKE '%[[]dbo].[[]mytable_seq]%';
It will give you two statements that look like this:
ALTER TABLE dbo.mytable DROP CONSTRAINT [DF__mytable__id__42ACE4D4];
and
ALTER TABLE dbo.mytable ADD DEFAULT (NEXT VALUE FOR [dbo].[mytable_seq]) FOR [id];
or
ALTER TABLE dbo.mytable ADD CONSTRAINT [DF__mytable__id__42ACE4D4] DEFAULT (NEXT VALUE FOR [dbo].[mytable_seq]) FOR [id];
(Whether you get the first or second re-create command depends on if the constraint was a named constraint originally.)
In your test you can now execute the drop statement. Then fake the table. Then use tSQLt.RemoveObject to remove the sequence. Now you can create your mock sequence (with the same name as the original). Finally, execute the re-create statement.
Now, you are ready to execute the code under test.
Word of caution: While tSQLt executes all tests in a transaction and, with that, will automatically put things back in place at the end of the test, this is not foolproof. I suggest inserting the drop and creating statements in some table. That way, you can get to them should the automatic ROLLBACK fail.
Now, that is the long way. There is an easier way: Option 2. However, this way is potentially not side-effect-free. It is the ALTER SEQUENCE statement:
DROP TABLE IF EXISTS dbo.mytable;
IF(OBJECT_ID('dbo.mytable_seq')IS NOT NULL)DROP SEQUENCE dbo.mytable_seq;
GO
CREATE SEQUENCE dbo.mytable_seq AS INT START WITH 100 CACHE 10;
CREATE TABLE dbo.mytable(id INT DEFAULT NEXT VALUE FOR dbo.mytable_seq,col2 INT);
GO
INSERT INTO dbo.mytable DEFAULT VALUES;
INSERT INTO dbo.mytable DEFAULT VALUES;
GO
BEGIN TRAN;
GO
ALTER SEQUENCE dbo.mytable_seq RESTART WITH 1;
INSERT INTO dbo.mytable DEFAULT VALUES;
INSERT INTO dbo.mytable DEFAULT VALUES;
SELECT * FROM dbo.mytable;
GO
ROLLBACK;
GO
INSERT INTO dbo.mytable DEFAULT VALUES;
INSERT INTO dbo.mytable DEFAULT VALUES;
SELECT * FROM dbo.mytable;
With this option, your test would look like this:
CREATE PROCEDURE mytests.[test inserts rows using NEXT VALUE FOR dbo.mytable_seq]
AS
BEGIN
ALTER SEQUENCE dbo.mytable_seq RESTART WITH 1;
EXEC tSQLt.FakeTable 'dbo.mytable', @Defaults = 0; --make sure the proc doesn't just rely on the constraint.
EXEC dbo.myproc;
SELECT id FROM dbo.mytable INTO #Actual;
SELECT TOP(0) * FROM #Actual INTO #Expected;
INSERT INTO #Expected VALUES(1),(2);
EXEC tSQLt.AssertEqualsTable '#Expected','#Actual';
END;
In my limited testing, the rollback seems to put everything back in place, but according to the documentation, you might get gaps in the sequence. Also, should the ROLLBACK fail, it is going to be harder to get the system back into the original state.
That being said, you should not run tests in an environment that contains data you care about. If you follow that advice, I'd go the simpler route with option 2.