Nulls in Unique Key Constraints and Index

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

SQL Server Administration (2008)

Nulls in Unique Key Constraints and Index

Author

Topic

krishnakantj
Starting Member

2 Posts

Posted - 2011-03-16 : 03:18:43

HI, I am trying to understand the below matter please help.

When we apply Unique Key constraints on any column it allows null values ( Value not Known), in that column. It does treat nulls as duplicates.

Now to create Clustered Index we need to have Unique values in column

So can we create a clustered index on a column which has been provided with Unique Key constraints.

What in the case of Non Clustered Index? Explain.

Krishnakant Joshi
Software Engineer

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-16 : 04:03:27

Clustered indexes don't have to be unique at all. You can create a clustered index on a column with duplicate values, multiple nulls, no problem. Same with nonclustered index.
Neither has a uniqueness requirement unless you declare the index unique (CREATE UNIQUE CLUSTERED INDEX or CREATE UNIQUE NONCLUSTERED INDEX)

--
Gail Shaw
SQL Server MVP

Sachin.Nand

2937 Posts

Posted - 2011-03-16 : 04:41:36

quote:
When we apply Unique Key constraints on any column it allows null values ( Value not Known), in that column. It does not treat nulls as duplicates.

Now to create Clustered Index we need to have Unique values in column ( Not even multiple nulls, as allowed in Unique Key constraints)

Its a totally wrong assumption.

It will only allow a SINGLE Null value and not multiple Null values.
So indirectly it assumes that 2 Nulls are infact duplicates.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources