unique key - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

unique key

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-06-01 : 01:18:46

Dear experts,
if i've created one unique key on one column, will it creates an index on that? if so, is there any way to find how every index was created?

i mean wether it was created with create index or it was created while the primary key or unique key creation.

thank you very much....i've been using the PBGUY query

Select * from

(select object_name(si.id) [Object],index_col(object_name(si.id), si.indid, sk.keyno) [Column_Name],

case when (si.status & 16)<> 0 then 'clustered'

else 'nonclustered' end [Index Type]

from sysindexes si join sysindexkeys sk

on si.id = sk.id and si.indid = sk.indid and si.indid between 1 and 254 and (si.status & 64)=0 ) as b
where Column_Name is not null

thank you very much

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-01 : 01:22:40

Unique constraint automatically creates a non-clustered index. You can see it by using

sp_help 'table-name'

command.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-06-01 : 03:59:41

is there any way to find wether the index was created by using create index <index_name>.....or that was created automaticlly by unique or primary key?

thank you very much

Vinod
Even you learn 1%, Learn it with 100% confidence.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-01 : 05:21:32

System automatically creates index on the unique/Primary key column...the create index is for user....

I found this below info from SQL server help

CLUSTERED | NONCLUSTERED

Are keywords to indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints default to CLUSTERED and UNIQUE constraints default to NONCLUSTERED.

You can specify CLUSTERED for only one constraint in a CREATE TABLE statement. If you specify CLUSTERED for a UNIQUE constraint and also specify a PRIMARY KEY constraint, the PRIMARY KEY defaults to NONCLUSTERED

--------------------------------------------------
S.Ahamed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources