Nonclustered UNIQUE INDEX

Hi
I have read about "Nonclustered UNIQUE INDEX" on BOL and
it am not clear what exactly is Nonclustered Unique Index!
Plese note i do understand the difference between the
Clustered and Nonclustered Index!
What i don't understand is the "Nonclustered UNIQUE
INDEX"? What does it mean when you create a "Nonclustered
UNIQUE INDEX" on a column that it's NOT defined as UNIQUE
CONSRAINT and it allows duplicate values.
I appreciate if you could also tell me which columns are
usually good candidates for a Nonclustered UNIQUE INDEX.
Thank you,
Mitra> What i don't understand is the "Nonclustered UNIQUE
> INDEX"? What does it mean when you create a "Nonclustered
> UNIQUE INDEX" on a column that it's NOT defined as UNIQUE
> CONSRAINT and it allows duplicate values.
You've made some assumptions here. If you create a UNIQUE INDEX then it
will not allow duplicate values. Just because you didn't explicitly create
a constraint doesn't mean one isn't implicitly created for you. Try it!
CREATE TABLE blat(foo INT)
CREATE UNIQUE INDEX splunge ON blat(foo)
GO
INSERT blat SELECT 1
INSERT blat SELECT 2
INSERT blat SELECT 3
SELECT foo FROM blat
GO
-- you will see 1, 2, 3 in the resultset.
-- however, when you try this:
INSERT blat SELECT 1
-- you will get:
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'blat' with unique index
'splunge'.
The statement has been terminated.
So, essentially, a UNIQUE INDEX creates an index *and* it is enforced like a
unique constraint (though no constraint will show up using sp_help or
sp_helpconstraint).
Note that unless you include the word CLUSTERED the index will be
non-clustered.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Aaron,thank you for the clarification!
I guess I was confused because when I open the Design
Table window for a table in SQL Server 2000, in the
Properties dialog box there are two options for Create
UNIQUE setting: Constraint, and Index. Why is that?
Thanks,
Mitra
>--Original Message--
>> What i don't understand is the "Nonclustered UNIQUE
>> INDEX"? What does it mean when you create
a "Nonclustered
>> UNIQUE INDEX" on a column that it's NOT defined as
UNIQUE
>> CONSRAINT and it allows duplicate values.
>You've made some assumptions here. If you create a
UNIQUE INDEX then it
>will not allow duplicate values. Just because you
didn't explicitly create
>a constraint doesn't mean one isn't implicitly created
for you. Try it!
>
>CREATE TABLE blat(foo INT)
>CREATE UNIQUE INDEX splunge ON blat(foo)
>GO
>INSERT blat SELECT 1
>INSERT blat SELECT 2
>INSERT blat SELECT 3
>SELECT foo FROM blat
>GO
>-- you will see 1, 2, 3 in the resultset.
>-- however, when you try this:
>INSERT blat SELECT 1
>-- you will get:
>Server: Msg 2601, Level 14, State 3, Line 1
>Cannot insert duplicate key row in object 'blat' with
unique index
>'splunge'.
>The statement has been terminated.
>
>So, essentially, a UNIQUE INDEX creates an index *and*
it is enforced like a
>unique constraint (though no constraint will show up
using sp_help or
>sp_helpconstraint).
>Note that unless you include the word CLUSTERED the
index will be
>non-clustered.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>.
>|||A constraint doesn't add an index, it just enforces uniqueness. You might
want to have 30 constraints on a table but you will be very unlikely to have
30 indexes that are helpful.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mitra Fathollahi" <mitra928@.hotmail.com> wrote in message
news:c47401c43884$9dbfd590$a101280a@.phx.gbl...
> Aaron,thank you for the clarification!
> I guess I was confused because when I open the Design
> Table window for a table in SQL Server 2000, in the
> Properties dialog box there are two options for Create
> UNIQUE setting: Constraint, and Index. Why is that?
> Thanks,
> Mitra
>>--Original Message--
>> What i don't understand is the "Nonclustered UNIQUE
>> INDEX"? What does it mean when you create
> a "Nonclustered
>> UNIQUE INDEX" on a column that it's NOT defined as
> UNIQUE
>> CONSRAINT and it allows duplicate values.
>>You've made some assumptions here. If you create a
> UNIQUE INDEX then it
>>will not allow duplicate values. Just because you
> didn't explicitly create
>>a constraint doesn't mean one isn't implicitly created
> for you. Try it!
>>
>>CREATE TABLE blat(foo INT)
>>CREATE UNIQUE INDEX splunge ON blat(foo)
>>GO
>>INSERT blat SELECT 1
>>INSERT blat SELECT 2
>>INSERT blat SELECT 3
>>SELECT foo FROM blat
>>GO
>>-- you will see 1, 2, 3 in the resultset.
>>-- however, when you try this:
>>INSERT blat SELECT 1
>>-- you will get:
>>Server: Msg 2601, Level 14, State 3, Line 1
>>Cannot insert duplicate key row in object 'blat' with
> unique index
>>'splunge'.
>>The statement has been terminated.
>>
>>So, essentially, a UNIQUE INDEX creates an index *and*
> it is enforced like a
>>unique constraint (though no constraint will show up
> using sp_help or
>>sp_helpconstraint).
>>Note that unless you include the word CLUSTERED the
> index will be
>>non-clustered.
>>--
>>Aaron Bertrand
>>SQL Server MVP
>>http://www.aspfaq.com/
>>
>>.|||Uhm, Aaron?
A UNIQUE constraint always automatically adds an index, that's the only way
in SQL server you can implement it.
Mitra,
A constraint is part of your logical database design, an index is physical
construct. The effect of them is the same. It is best practice however to
enforce uniqueness via constraints, as it is an element of your logical
design, just like foreign keys for example. The only good reason to
implement a unique index without a unique constraint is if a subset of the
columns in the unique index is already covered by a unique constraint. One
example is when you have two columns with a unique constraint on it and for
performance reasons you also want an index with the columns in the opposite
order.
--
Jacco Schalkwijk
SQL Server MVP
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%233kkbVIOEHA.128@.TK2MSFTNGP12.phx.gbl...
> A constraint doesn't add an index, it just enforces uniqueness. You might
> want to have 30 constraints on a table but you will be very unlikely to
have
> 30 indexes that are helpful.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
> "Mitra Fathollahi" <mitra928@.hotmail.com> wrote in message
> news:c47401c43884$9dbfd590$a101280a@.phx.gbl...
> > Aaron,thank you for the clarification!
> >
> > I guess I was confused because when I open the Design
> > Table window for a table in SQL Server 2000, in the
> > Properties dialog box there are two options for Create
> > UNIQUE setting: Constraint, and Index. Why is that?
> >
> > Thanks,
> >
> > Mitra
> >
> >>--Original Message--
> >> What i don't understand is the "Nonclustered UNIQUE
> >> INDEX"? What does it mean when you create
> > a "Nonclustered
> >> UNIQUE INDEX" on a column that it's NOT defined as
> > UNIQUE
> >> CONSRAINT and it allows duplicate values.
> >>
> >>You've made some assumptions here. If you create a
> > UNIQUE INDEX then it
> >>will not allow duplicate values. Just because you
> > didn't explicitly create
> >>a constraint doesn't mean one isn't implicitly created
> > for you. Try it!
> >>
> >>
> >>CREATE TABLE blat(foo INT)
> >>CREATE UNIQUE INDEX splunge ON blat(foo)
> >>GO
> >>
> >>INSERT blat SELECT 1
> >>INSERT blat SELECT 2
> >>INSERT blat SELECT 3
> >>SELECT foo FROM blat
> >>GO
> >>
> >>-- you will see 1, 2, 3 in the resultset.
> >>-- however, when you try this:
> >>
> >>INSERT blat SELECT 1
> >>
> >>-- you will get:
> >>
> >>Server: Msg 2601, Level 14, State 3, Line 1
> >>Cannot insert duplicate key row in object 'blat' with
> > unique index
> >>'splunge'.
> >>The statement has been terminated.
> >>
> >>
> >>So, essentially, a UNIQUE INDEX creates an index *and*
> > it is enforced like a
> >>unique constraint (though no constraint will show up
> > using sp_help or
> >>sp_helpconstraint).
> >>
> >>Note that unless you include the word CLUSTERED the
> > index will be
> >>non-clustered.
> >>
> >>--
> >>Aaron Bertrand
> >>SQL Server MVP
> >>http://www.aspfaq.com/
> >>
> >>
> >>.
> >>
>|||Yes, much better explanation, sorry...
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OqBXTyMOEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Uhm, Aaron?
> A UNIQUE constraint always automatically adds an index, that's the only
way
> in SQL server you can implement it.
> Mitra,
> A constraint is part of your logical database design, an index is physical
> construct. The effect of them is the same. It is best practice however to
> enforce uniqueness via constraints, as it is an element of your logical
> design, just like foreign keys for example. The only good reason to
> implement a unique index without a unique constraint is if a subset of the
> columns in the unique index is already covered by a unique constraint. One
> example is when you have two columns with a unique constraint on it and
for
> performance reasons you also want an index with the columns in the
opposite
> order.