August 28, 2009 at 8:12 pm
Got a small confusion, can anyone please clarify?
Clustered index never contain any NULL values as Primary key is imposed to it.
But what about the Non-Clustered Index defined on a column? How many NULL values it can accept? I understand that the clustered index is used those columns which is used very frequently.
Can any one suggest something more on this please?
I ran sp_help
Picked up one particular row where the confusion arised:
SmtBillOfMaterialGroupIdnonclustered located on PRIMARYSmtBillOfMaterialGroupId
(index_name,index_description and index_keys).
The index_column: SmtBillOfMaterialGroupId contains many "NULL" values.
Does this mean non-clustered index column can hold as many NULL values as possible.
Your suggestion will be highly helpful.
Regards ....
Thanks.
August 28, 2009 at 8:41 pm
Clustered indexes and Primary Keys are two independant entities. It just turns out that most people build their primary keys also as clustered indexes, even when their queries would be better served by using a different clustered index than the primary key.
Here's the NULL options on indexes: (clustered or non-clustered):
- for a Primary Key constraint: 0 nulls allowed
- for a Unique index: treats NULL as a value for the purpose of determining uniqueness. (Single column unique indexes may have a single NULL value, multi-column unique indexes that allow NULLs may have a single row with all NULL values for columns that allow NULLs, and any number of rows with a mixture of NULL and values in the columns so long as they remain unique)
- non-unique: allows any number of NULLs.
-Eddie
Eddie Wuerch
MCM: SQL
August 29, 2009 at 7:21 am
Sourav (8/28/2009)
Clustered index never contain any NULL values as Primary key is imposed to it.
Clustered indexes are not primary keys and primary keys are not clustered indexes. A clustered index can contain any number of nulls. Same as a nonclustered index
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply