August 16, 2010 at 10:35 pm
is non clustered index can store NULL value? If yes then why?
August 16, 2010 at 10:57 pm
Yes a non-clustered index can be on columns that contain null values. An NC Index is not for referential integrity. Sometimes, depending on queries and data usage, an index on a column that has null values is more efficient than not having an index on that column.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 16, 2010 at 11:30 pm
jobs.chayan (8/16/2010)
is non clustered index can store NULL value? If yes then why?
Yes, and it's true for the clustered index as well. Indexes don't prevent values from been inserted. A unique index (clustered or nonclustered) will allow one null (because for the purposes of uniqueness in indexes, NULL does = NULL). A non-unique index (clustered or nonclustered) will allow any number of nulls.
As for why, it's how they are designed.
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
August 16, 2010 at 11:36 pm
CirquedeSQLeil (8/16/2010)
An NC Index is not for referential integrity.
Indexes in general are not for referential integrity.
Foreign keys are for referential integrity.
Check constraints are for domain integrity
Primary keys (not indexes) are for entity integrity
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 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply