How to apply an index to a faked table in tSQLt

Question

Using the excellent tSQLt testing framework (v1.0.5137.39257) for MS SQL Server (2012), I'm trying to build a test to check that a UNIQUE INDEX works and generates an exception when a duplicate value is inserted.

After extensive searching, I cannot find a way to apply an index to a table that has been faked (built-in tSQLt proc or extra code). Something like tSQLt.ApplyIndex would be required. Has anyone managed to do this?

Another possibility would be to fork the tSQLt code and add a proc based on the code at http://gallery.technet.microsoft.com/scriptcenter/SQL-Server-Generate-Index-fa790441 to re-create the index on the faked table. However this would be quite a bit of work...

Test conditions (assuming tSQLt has been installed in the database):

-- Create a sample table with a UNIQUE INDEX
SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON
GO
CREATE TABLE dbo.tblTestUniqueIndex (
  id INT NOT NULL IDENTITY (1, 1),  TheField varchar(50) NOT NULL,
  CONSTRAINT PK_tblTestUniqueIndex PRIMARY KEY CLUSTERED (id ASC)
) ON [PRIMARY];
GO
CREATE UNIQUE NONCLUSTERED INDEX UX_TestUniqueIndex ON dbo.tblTestUniqueIndex
(TheField ASC)
ON [PRIMARY];
GO

Creating the test class, test and running it (of course it fails because the procedure call ApplyIndex does not exist):

EXEC tSQLt.NewTestClass 'tests';
GO
CREATE PROCEDURE tests.[test that inserting a duplicate value in tblTestUniqueIndex raises an error]
AS
BEGIN
  EXEC tSQLt.FakeTable @TableName='dbo.tblTestUniqueIndex';

  -- WE NEED SOMETHING LIKE THIS
  --EXEC tSQLt.ApplyIndex @TableName='dbo.tblTestUniqueIndex', @ConstraintName='UX_TestUniqueIndex' 

  EXEC tSQLt.ExpectException;

  INSERT dbo.tblTestUniqueIndex (TheField) VALUES ('Cape Town');
  INSERT dbo.tblTestUniqueIndex (TheField) VALUES ('Cape Town');

END;
GO
EXEC tSQLt.Run 'tests.[test that inserting a duplicate value in tblTestUniqueIndex raises an error]'
GO

Of course the above test fails without the index working.

Clean-up:

DROP PROCEDURE tests.[test that inserting a duplicate value in tblTestUniqueIndex raises an error]
GO
EXEC tSQLt.DropClass 'tests'
GO
DROP TABLE dbo.tblTestUniqueIndex
GO

Thanks

Answer 1

Could you not create a unique constraint instead? You'll still get the desired index under the hood. tSQLt.ApplyConstraint works for unique keys the same as it does for primary keys - but only in the very latest version IIRC. For example:

Start with a similar version of the table you have created above, with enough columns for one of each type of unique constraint

-- Create a sample table with a UNIQUE INDEX
set ansi_nulls, quoted_identifier, ansi_padding on
go

if object_id('dbo.StackTable') is not null
    drop table dbo.StackTable;

create table dbo.StackTable
(
  Id int not null identity(1, 1)
, UniqueKeyColumn varchar(50) null
, UniqueIndexColumn int null
);
go

if object_id('PK_StackTable') is null
    alter table dbo.StackTable add constraint [PK_StackTable]
        primary key clustered (Id);
go

if object_id('AK_StackTable_UniqueKeyColumn') is null
    alter table dbo.StackTable add constraint [AK_StackTable_UniqueKeyColumn]
        unique nonclustered  (UniqueKeyColumn);
go

if object_id('NCI_StackTable_UniqueIndexColumn') is null
    create unique nonclustered index [NCI_StackTable_UniqueIndexColumn]
        on dbo.StackTable (UniqueIndexColumn);
go

Create a new test class (assumes that the latest version 1.0.5325.27056 has already been installed)

if schema_id('StackTableTests') is null
    exec tSQLt.NewTestClass @ClassName = 'StackTableTests';
go

This first test confirms that the Id is constrained to be unique and that constraint is a primary key

if object_id('[StackTableTests].[test id is unique]') is not null
    drop procedure [StackTableTests].[test id is unique];
go

create procedure [StackTableTests].[test id is unique]
as
begin
    exec tSQLt.FakeTable @TableName = 'dbo.StackTable';
    exec tSQLt.ApplyConstraint @TableName = 'dbo.StackTable', @ConstraintName = 'PK_StackTable';

    --! Add the row we're going to duplicate
    insert dbo.StackTable (Id) values (-999);

    --! If we insert the same value again, we should expect to see an exception
    exec tSQLt.ExpectException @ExpectedErrorNumber = 2627
        , @ExpectedMessagePattern = 'Violation of PRIMARY KEY constraint%';

    insert dbo.StackTable (Id) values (-999);
end
go

This next test also uses ApplyConstraint to confirm that the UniqueKeyColumn is also constrained to be unique using a unique constraint

if object_id('[StackTableTests].[test UniqueKeyColumn is unique]') is not null
    drop procedure [StackTableTests].[test UniqueKeyColumn is unique];
go

create procedure [StackTableTests].[test UniqueKeyColumn is unique]
as
begin
    exec tSQLt.FakeTable @TableName = 'dbo.StackTable';
    exec tSQLt.ApplyConstraint
        @TableName = 'dbo.StackTable', @ConstraintName = 'AK_StackTable_UniqueKeyColumn';

    --! Add the row we're going to duplicate
    insert dbo.StackTable (UniqueKeyColumn) values ('Oops!');

    --! If we insert the same value again, we should expect to see an exception
    exec tSQLt.ExpectException @ExpectedErrorNumber = 2627
        , @ExpectedMessagePattern = 'Violation of UNIQUE KEY constraint%';

    insert dbo.StackTable (UniqueKeyColumn) values ('Oops!');
end
go

Currently, the only way to test a unique index would be against the real, un-faked table. In this example, the UniqueKeyColumn is a varchar(50) and as this is the real table, it may already contain data. So we need to be able to specify two unique values so that our test doesn't break the wrong constraint. The simplest way to do this is with a couple of GUIDs.

if object_id('[StackTableTests].[test UniqueIndexColumn is unique]') is not null
    drop procedure [StackTableTests].[test UniqueIndexColumn is unique];
go

create procedure [StackTableTests].[test UniqueIndexColumn is unique]
as
begin
    --! Have to use the real table here as we can't use ApplyConstraint on a unique index
    declare @FirstUniqueString varchar(50) = cast(newid() as varchar(50));
    declare @NextUniqueString varchar(50) = cast(newid() as varchar(50));

    --! Add the row we're going to duplicate
    insert dbo.StackTable (UniqueKeyColumn, UniqueIndexColumn) values (@FirstUniqueString, -999);

    --! If we insert the same value again, we should expect to see an exception
    exec tSQLt.ExpectException @ExpectedErrorNumber = 2601
        , @ExpectedMessagePattern = 'Cannot insert duplicate key row in object ''dbo.StackTable'' with unique index%';

    insert dbo.StackTable (UniqueKeyColumn, UniqueIndexColumn) values (@NextUniqueString, -999);
end
go

exec tSQLt.Run '[StackTableTests]';
go

The potential problem with testing against the real table is that it may have foreign key references to other tables which in turn may have other FK references to other tables and so on. There is no easy way to deal with this but I have implemented a version of the Test Data Builder pattern. The idea behind this is a stored procedure for each entity which automagically takes care of those dependencies (i.e. adds any necessary rows to parent & grand parent tables). Typically, having created all the dependencies, the TDB sproc would make the resulting IDs available as output parameters so the values could be reused.

So if I was using the Test Data Builder pattern, my test might look like this:

create procedure [StackTableTests].[test UniqueIndexColumn is unique]
as
begin
    --! Have to use the real table here as we can't use ApplyConstraint on a unique index
    declare @FirstUniqueString varchar(50) = cast(newid() as varchar(50));
    declare @NextUniqueString varchar(50) = cast(newid() as varchar(50));
    declare @NewId int;

    --! Add the row we're going to duplicate
    -- The TDB should output the ID's of any FK references so they
    --! can be reused on the next insert
    exec TestDataBuilders.StackTableBuilder
              @UniqueKeyColumn = @FirstUniqueString
            , @UniqueIndexColumn = -999

    --! If we insert the same value again, we should expect to see an exception
    exec tSQLt.ExpectException @ExpectedErrorNumber = 2601
        , @ExpectedMessagePattern = 'Cannot insert duplicate key row in object ''dbo.StackTable'' with unique index%';   

    insert dbo.StackTable (UniqueKeyColumn, UniqueIndexColumn) values (@NextUniqueString, -999);
end
go

exec tSQLt.Run '[StackTableTests]';
go

I wrote a blog post on using the Test Data Builder pattern for SQL a couple of years ago. I hope this helps explain my thinking.