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.
| Author |
Topic |
|
olibara
Yak Posting Veteran
94 Posts |
 Posted - 2013-05-11 : 13:23:31
|
| HelloIs 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 fromcreating 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. |
 |
|
|
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 |
|
|
|
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 columnHowever, 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 CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
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 |
|
|
|
|
|
|
|
|
Unique index on Nullable field