conditional unique constraint

by

Behold, the filtered index. From the documentation (emphasis mine):

A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

And here’s an example combining a unique index with a filter predicate:

create unique index MyIndex
on MyTable(ID)
where RecordStatus = 1;

This essentially enforces uniqueness of ID when RecordStatus is 1.

Following the creation of that index, a uniqueness violation will raise an arror:

Msg 2601, Level 14, State 1, Line 13
Cannot insert duplicate key row in object ‘dbo.MyTable’ with unique index ‘MyIndex’. The duplicate key value is (9999).

Note: the filtered index was introduced in SQL Server 2008. For earlier versions of SQL Server, please see this answer.

More Related Contents:

  1. Insert results of a stored procedure into a temporary table
  2. How can I do an UPDATE statement with JOIN in SQL Server?
  3. Select top 10 records for each category
  4. The multi-part identifier could not be bound
  5. How important is the order of columns in indexes?
  6. Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
  7. sql query to return differences between two tables
  8. SQL Server – transactions roll back on error?
  9. Difference between a User and a Login in SQL Server
  10. Fastest way to update 120 Million records
  11. For Nvarchar(Max) I am only getting 4000 characters in TSQL?
  12. Is it possible to select sql server data using column ordinal position
  13. How to find current transaction level?
  14. How to insert multiple records and get the identity value?
  15. How do I group on continuous ranges
  16. How can a LEFT OUTER JOIN return more records than exist in the left table?
  17. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  18. Dynamic SQL to generate column names?
  19. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  20. Column name or number of supplied values does not match table definition
  21. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  22. SQL Server PIVOT perhaps?
  23. How to find consecutive rows based on the value of a column?
  24. Script to save varbinary data to disk
  25. How to escape square brackets inside square brackets for field name
  26. How do you copy a record in a SQL table but swap out the unique id of the new row?
  27. How can I find Unicode/non-ASCII characters in an NTEXT field in a SQL Server 2005 table?
  28. Is there a quick way to check if ANY column is NULL?
  29. Why do SQL Server Scalar-valued functions get slower?
  30. SQL query for parent-child chain

Leave a Comment