Unique indexes and unique constraints limit the values that are allowed in a table column to unique values.  This uniqueness is almost always a result of some type of business rule.  Programming these types of business rules are usually not overcomplicated to write.  However, SQL Server has an option that you can set when creating unique indexes that allows you to control how duplicate values are handled so that you can potentially get away from writing these rules by hand.

 The unique index option, IGNORE_DUP_KEY, allows you to indicate how records that would violate a unique index should be handled.  When the option is set to OFF, any records that violate the unique constraint will result in error and the entire statement will be rolled back.  This option will prevent any records that would not have violated the unique index from getting into the table as well.  This is typically where custom programming needs to be written to only insert records that are not already in the table.  When the option is set to ON, the records that do not violate the constraint are inserted while the records that do violate the constraint are ingored.  You can see where this option may come in quite handy.

To take a look at how IGNORE_DUP_KEY works, lets take a look at an example.  I'll contrast how SQL Server handles unique violations with the option set ON and OFF.  To start the example off, lets create a table.

IF OBJECT_ID('TestingDuplicateValues','U') IS NOT NULL
    DROP TABLE TestingDuplicateValues
GO
CREATE TABLE TestingDuplicateValues
(
    IDColumn SMALLINT NOT NULL
)
GO

 Now I'll create a unique index on my single column table.  By default the IGNORE_DUP_KEY option is set to OFF when a unique index is created I included it here to illustrate the TSQL syntax for how to create the index.

CREATE UNIQUE NONCLUSTERED INDEX idx_TestingDuplicateValues_IDColumn
ON TestingDuplicateValues(IDColumn)
WITH (IGNORE_DUP_KEY = OFF)
GO

To test our new unique index lets create a batch to violate the unique index.  Since the IGNORE_DUP_KEY option is set to OFF, I will recieve an error telling me that I cannot insert a duplicate value into the table.  Consequently, the entire batch is rolled back.

INSERT INTO TestingDuplicateValues(IDColumn)
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 2
G0

To validate that no records were inserted into the table, run the following query.  0 records should be returned.

SELECT * 
FROM TestingDuplicateValues

Now, lets drop our unique index and recreate it.  This time I will create the unique index with the IGNORE_DUP_KEY option set to ON. 

DROP INDEX TestingDuplicateValues.idx_TestingDuplicateValues_IDColumn
GO
CREATE UNIQUE NONCLUSTERED INDEX idx_TestingDuplicateValues_IDColumn
ON TestingDuplicateValues(IDColumn)
WITH (IGNORE_DUP_KEY = ON)
GO

When I run the same INSERT statement as before, 2 records are now inserted into the table.  The duplicate value from the statement is ignored.  No error is returned, rather I receive the informational message "Duplicate key was ignored."

INSERT INTO TestingDuplicateValues(IDColumn)
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 2
G0

You can validate that 2 records were inserted into the table w/ the following query.

SELECT * 
FROM TestingDuplicateValues

 The IGNORE_DUP_KEY is a very handy option when creating unique constraints.  However, it does not necessarily take the place of crafty programming when it is necessary.  Check out the option and determine if it works for your scenario.  If it does...GREAT, glad to help.  If not, don't worry, a little programming goes a long way.

Tim