Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Unique index on Nullable field

Author  Topic 

olibara
Yak Posting Veteran

94 Posts

Posted - 2013-05-11 : 13:23:31
Hello

Is it possible on SQL Server 2008 to create an unique index on a nullable Field ?

Mark W
Starting Member

2 Posts

Posted - 2013-05-11 : 14:56:19
I think the short answer is “yes” depending on what you mean by “unique” index,
rather than just plain old “index”.
Here is the long answer. An index, of a column, is a structure that we ask the database to create for us,
so that it can more quickly find rows for which that column contains a specific value.
It works more or less just like the index in the back of a book that has been printed on ordinary paper.
If a column is “Nullable” that just means it is permissible for some of the rows of that column to be left blank.
Just as nothing is preventing the database from creating an index entry, that documents the internal row identifiers,
of all rows for which a phone_number column contain the symbol “(123) 456-7890” nothing prevents it from
creating a different entry in that same index, that documents the identities of all of the rows for which
the phone_number column contains nothing.

If a symbol , including NULL, appears in more than one row of a column that has been indexed,
there will exist entries in the index, which reference more than one row. This is like an index entry in the
back of a book, which tells you that the name “Rodney Brooks” occurs on more than one page of the book. So,
if your definition of the term “unique index” is an index comprised of entries that each reference precisely
one row ID, then the presence of NULL in precisely one row of the indexed column would not prevent
the index from conforming to that definition, however the presence of NULL,
or for that matter any other symbol, in more than one row of that column would.

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-11 : 16:35:57
I obviously don't know your business requirements, but you may want to look up filtered indexes to see if that would be of any use to you: http://technet.microsoft.com/en-us/library/cc280372.aspx
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-12 : 17:00:17
I believe that you *can* declare a UNIQUE constraint on a nullable column -- however you'll then only be able to store one (1) such row with a null for that column

However, I can't think of any reason why you'd want to do that -- unless maybe for a dimension table in a star schema where you want to be able to represent a missing element for one dimension....

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-05-14 : 09:47:14
quote:
Originally posted by Transact Charlie

I believe that you *can* declare a UNIQUE constraint on a nullable column -- however you'll then only be able to store one (1) such row with a null for that column



^^ Exactly
Go to Top of Page
   

- Advertisement -