Instead Of

I learned something nifty today about triggers. @dyfhid had a scenario where they wanted to make a unique constraint for only a subset of the data. Not running SQL Server 2008 on the VM I already had spun up, I didn't get to play with the nifty filtered index solutions others proposed. Below is my quick and dirty solution to allow the text to be as duplicated as they want but to keep the serialnum column unique for numeric values.

create table dbo.production3
(
    production3_id int identity(1,1) NOT NULL PRIMARY KEY
,   serialnum varchar(50) NOT NULL
)

GO

INSERT INTO
    dbo.production3
SELECT
    'Hi mom Pack with NO SN' AS serialnum
UNION ALL
SELECT
    'Cactus Pack with NO SN' AS serialnum
UNION ALL
SELECT
    'Cactus Pack with NO SN' AS serialnum
UNION ALL
SELECT
    '01234567890' AS serialnum
UNION ALL
SELECT
    '12345678901' AS serialnum
UNION ALL
SELECT
    'Cactus Pack with NO SN' AS serialnum

GO
-- Return 1 if the item is unique enough
-- return 0 if it's not unique enough (numbers only)
CREATE FUNCTION dbo.MostlyUnique
(
    @serialNum varchar(50)
)
RETURNS bit
BEGIN
    DECLARE @retval bit
    SET @retval = 1
    -- We only care if it looks like a number
    if isnumeric(@serialNum) = 1 AND EXISTS (select serialnum from production3 where SerialNum = @serialnum)
    BEGIN
        SET @retval = 0
    END

    RETURN @retval
END

GO


CREATE TRIGGER DBO.MostlyUniqueTrigger
ON dbo.production3
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO
        dbo.production3
    SELECT
        I.serialnum
    FROM
        INSERTED I
    WHERE
        -- this probably sucks for performance
        -- but it keeps the dupes out
        dbo.MostlyUnique(I.serialnum) = 1
END

GO


-- readd the original set, should only see the NO SN stuff

INSERT INTO
    dbo.production3
SELECT
    'Hi mom Pack with NO SN' AS serialnum
UNION ALL
SELECT
    'Cactus Pack with NO SN' AS serialnum
UNION ALL
SELECT
    'Cactus Pack with NO SN' AS serialnum
UNION ALL
SELECT
    '01234567890' AS serialnum
UNION ALL
SELECT
    '12345678901' AS serialnum
UNION ALL
SELECT
    'Cactus Pack with NO SN' AS serialnum
UNION ALL
SELECT
    '23456789012' AS serialnum


SELECT * FROM production3


--production3_id  serialnum
--1   Hi mom Pack with NO SN
--2   Cactus Pack with NO SN
--3   Cactus Pack with NO SN
--4   01234567890
--5   12345678901
--6   Cactus Pack with NO SN
--7   Hi mom Pack with NO SN
--8   Cactus Pack with NO SN
--9   Cactus Pack with NO SN
--10  Cactus Pack with NO SN
--11  23456789012

Alternate solution (there are many):
http://markvsql.com/index.php/2009/05/enforcing-uniqueness-on-a-nullable-column/

References:
http://msdn.microsoft.com/en-us/library/ms189799.aspx