SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 Unique index on Nullable field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

olibara
Yak Posting Veteran

89 Posts

Posted - 05/11/2013 :  13:23:31  Show Profile  Reply with Quote
Hello

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

Mark W
Starting Member

2 Posts

Posted - 05/11/2013 :  14:56:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3704 Posts

Posted - 05/11/2013 :  16:35:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/12/2013 :  17:00:17  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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

USA
5072 Posts

Posted - 05/14/2013 :  09:47:14  Show Profile  Visit russell's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000