Uniqueness And Multiple NULL Values

Dennes Torres shows how to allow an indefinite number of NULL values  while guaranteeing non-NULL values are unique:

Regardless of using unique constraint or unique index, the field can accept null values, however the uniqueness will result in only accepting a single row with null value.

The solution to allow nulls in unique fields is create a unique filtered index excluding the nulls of the index, due to that the uniqueness of the nulls will not be validated and multiple rows with nulls will be accepted.

Click through for the code.  I enjoy asking this as an interview question.  It’s a non-trivial problem with a non-trivial solution and isn’t a trick question.

Related Posts

Compress Those Indexes

Pamela Mooney reminds us that if you’re going to compress your heap or clustered index, remember those non-clustered indexes as well: We compress to gain space, correct? So why sacrifice it to our indexes? Here is a script (complete with demo databases and tables) that will find the culprits and fix them for you.  Put […]

Read More

READPAST In Action

Erik Darling shows how READPAST is no panacea: Locking hints can be really handy in these situations, especially the READPAST hint. The documentation for it says that it allows you to skip over row level locks (that means you can’t skip over page or object level locks). What it leaves out is that your READPAST […]

Read More

Categories

December 2016
MTWTFSS
« Nov Jan »
 1234
567891011
12131415161718
19202122232425
262728293031