Create unique filtered index on a table with complex conditions in SQL Server using Triggers

Question

Please consider this code:

CREATE UNIQUE NONCLUSTERED INDEX [idx1] 
ON dbo.Table1 ([Year] ASC,
               [City] ASC,
               [Region] ASC,
               [Sequence] ASC)
WHERE [Region] IN (1, 20) 
  AND [City] NOT LIKE N'C_341%'
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

The problem is, it seems that creating filtered index with complex condition is not possible. and I get this error:

Incorrect syntax near the keyword 'LIKE'

Is there any way to create uniqueness on those columns in SQL Server(for example using TRIGGER?

Thanks

Answer 1

The other answers already pointed out that LIKE is not supported in filtered indexes. So I will focus on the alternative way to force the uniqueness. Yes, you can do this with a trigger. You need to define after insert and after update trigger. In it you must check the contents of your table, keeping in mind that the rows inserted with this statements (they can be more than one) are already there. If you detect a duplicated values, you rollback the transaction and raise an error. The code of the trigger could look like this (assuming ID is your primary key field, which will allow us to identify newly inserted records):

CREATE TRIGGER [FORCE_UNIQUENESS] on [dbo].[Table1]
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    if exists(select *
        from dbo.Table1 t
        inner join inserted i on
            i.[Year] = t.[Year] and 
            i.[City] = t.[City] and 
            i.[Region] = t.[Region] and 
            i.[Sequance] = t.[Sequance] and 
            t.ID <> i.ID
        where i.Region in (1, 20) and i.[City] NOT LIKE N'C_341%')
    begin
        ROLLBACK TRANSACTION
        RAISERROR('Duplicated values detected', 16, 1);
    end
END

Instead of raising errors, you can create INSTEAD OF trigger. In this case the trigger will be responsible for actually saving the data in the table. You can decide to insert all rows, part of the rows, or none of them, to raise an error, or to silently skip the duplicated values, etc.

Answer 2

From documentation:

Filtered indexes are defined on one table and only support simple comparison operators. If you need a filter expression that references multiple tables or has complex logic, you should create a view.

Answer 3

Based on the documentation LIKE operator is not supported. Check this part

[comparison] ::=  
    column_name [comparison_op] constant  

[comparison_op] ::=  
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }