• What you are requesting, a filtered index, is available under SQL Server 2008 but not under 2005

    The 2008 syntax for a filtered index would be:

    CREATE UNIQUE NONCLUSTERED INDEX [IX_X] ON [dbo].[X]

    ([XName] ASC,[XUsersID] ASC)

    WHERE [IsFilter] = 0

    Under 2005, an alternative solution to a trigger, is to create a view that contains the appropriate where clause and then create a unique clustered index on the columns.

    CREATE TABLE dbo.Foo

    ( FooIdINTEGERNOT NULL

    , FooNameVARCHAR(255) NOT NULL

    , FooIndCHAR(1)NOT NULL

    , CONSTRAINT Foo_P PRIMARY KEY (FooId)

    , CONSTRAINT Foo_C_FooInd CHECK (FooInd IN ('Y','N' ) )

    )

    go

    CREATE VIEW FOO_FooNameUnique WITH SCHEMABINDING

    AS

    SELECTFoo.FooName

    FROMdbo.Foo

    WHEREFoo.FooInd = 'Y'

    WITH CHECK OPTION

    go

    CREATE UNIQUE CLUSTERED INDEX FOO_FooNameUnique_X

    ON dbo.FOO_FooNameUnique (FooName)

    go

    Test cases:

    delete from dbo.Foo

    INSERT INTO dbo.Foo

    (FooId, FooName, FooInd)

    SELECT 1 , 'OK', 'Y' union all

    SELECT 2 , 'Duplicate', 'Y'

    -- Not a duplicate due to filter

    INSERT INTO dbo.Foo

    (FooId, FooName, FooInd)

    SELECT 3 , 'Duplicate' , 'N'

    -- Not a duplicate due to filter

    INSERT INTO dbo.Foo

    (FooId, FooName, FooInd)http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif

    SELECT 4 , 'Duplicate' , 'N'

    -- This is a duplicate and should cause an error

    INSERT INTO dbo.Foo

    (FooId, FooName, FooInd)

    SELECT 5 , 'Duplicate', 'Y'

    The error message for the last test condition is:

    Msg 2601, Level 14, State 1, Line 1

    Cannot insert duplicate key row in object 'dbo.FOO_FooNameUnique' with unique index 'FOO_FooNameUnique_X'.

    The statement has been terminated.

    SQL = Scarcely Qualifies as a Language