Test unique filtered index with tSQLt

Question

Currently I am trying to test the table that has unique filtered index. The only suggestions I've found so far is to use CONSTRAINT instead of index as tSQLt has ApplyConstraint SP. However I couldn't find anyway how to do filtered constraint... Any suggestions?

CREATE TABLE [dbo].[prime_rates]
(
    [prime_rate] DECIMAL(5, 3) NOT NULL
  , [start_date] DATE          NOT NULL
  , [end_date]   DATE          NOT NULL
        DEFAULT '12/31/2099'
);
GO

CREATE UNIQUE INDEX [UIX_prime_rates_end_date]
    ON [dbo].[prime_rates] ([end_date])
    WHERE [end_date] = '12/31/2099';
GO

CREATE PROCEDURE [Test Class1].[test when 2 active prime rates are inserted then raise error]
AS
    BEGIN

        EXEC tSQLt.FakeTable @TableName = N'prime_rates';

        EXEC tSQLt.ApplyConstraint 'dbo.prime_rates', 'UIX_prime_rates_end_date';

        INSERT INTO dbo.prime_rates (   prime_rate
                                      , start_date
                                      , end_date
                                    )
        VALUES (   1            -- prime_rate - decimal(5, 3)
                 , GETDATE()    -- start_date - date
                 , '2099-12-31' -- end_date - date
               );

        EXEC tSQLt.ExpectException @ExpectedSeverity = 16;

        INSERT INTO dbo.prime_rates (   prime_rate
                                      , start_date
                                      , end_date
                                    )
        VALUES (   1            -- prime_rate - decimal(5, 3)
                 , GETDATE()    -- start_date - date
                 , '2099-12-31' -- end_date - date
               );
    END;

Answer 1

Basically you are testing that there are no inadvertent schema changes that have broken (removed/disabled/changed the predicate) your unique filtered index.

If you fake the table you then need to add the unique filtered index to the fake yourself which negates the point.

This should work.

CREATE PROCEDURE [Test Class1].[test when 2 active prime rates are inserted then raise error]
AS
  BEGIN
      IF @@TRANCOUNT = 0
        THROW 50000, 'This must be run in a transaction and rolled back afterwards. Use "EXEC tSQLt.RunAll;" etc. Don''t run directly ', 1;


      TRUNCATE TABLE dbo.prime_rates;

      INSERT INTO dbo.prime_rates
                  (prime_rate,
                   start_date,
                   end_date)
      VALUES      ( 1 -- prime_rate - decimal(5, 3)
                    ,GETDATE() -- start_date - date
                    ,'2099-12-31' -- end_date - date
      );

      EXEC tSQLt.ExpectException @ExpectedSeverity = 16;

      INSERT INTO dbo.prime_rates
                  (prime_rate,
                   start_date,
                   end_date)
      VALUES      ( 1 -- prime_rate - decimal(5, 3)
                    ,GETDATE() -- start_date - date
                    ,'2099-12-31' -- end_date - date
      );
  END