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