primary key vs unique key, and indexing

Hi,
the main question is : are unique constraint indexed automatically or
do i have to create an index too?
i'm wondering how can i get the best performance from a table that as 3
fields to be unique
should i put a primary key on the 3 fields , and sql will index it
or should i create a unique constraint ?
if so do i need to create an index on the 3 fields too or the unique
constraint is being indexed anyway ?
ThanksA unique constraint WILL automaitically create a unique index on the
fields included in the definition.
Whether to use a unique constraint or a primary key requires
consideration of a few things. Can you give more details about what you
are trying to accomplish?
frederic.nourry@.gmail.com wrote:
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks|||Wow, very loaded question. And it has my favorite answer: it depends.
A unique constraint (or just creating a primary key constraint) will not
automatically create an index. If you create it through the GUI, the options
to create indexes are often checked by default.
As far as what to use for the primary key - my personal preference is to use
an artificial key (an integer with the identity property enabled), use that
for table joins, then include the unique constraint on the 3-column natural
key.
Index only what will be involved in queries or joins. Don't create a 3
column index unless that is the way you would commonly query the table.
There are plenty of great sources out there on the subject of index
creation - do a little research. You'll get differing opinions (some will
disagree with what I'm saying here), and most of those differing opinions
have validity to them. You may need to experiment with a couple of different
techniques. It all depends on what you're database's main role is (insert,
update, delete or select only) and its size.
Others, please chime in.
Hope that helps a little.
<frederic.nourry@.gmail.com> wrote in message
news:1152283825.853943.82490@.m79g2000cwm.googlegroups.com...
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks
>|||frederic.nourry@.gmail.com,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
No, SQL Server implement a unique constraint by creating a unique index
(nonclustered by default).
Example:
-- sql 2k
create table dbo.t1 (
c1 int not null,
constraint uq_t1_c1 unique (c1)
)
go
select [name]
from sysindexes
where [id] = object_id('dbo.t1')
go
drop table dbo.t1
go
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
Do those columns accept NULL value?
Is there a primary key defined already?
A primary key can not be NULL by definition and a table can have just one.
AMB
"frederic.nourry@.gmail.com" wrote:
> Hi,
> the main question is : are unique constraint indexed automatically or
> do i have to create an index too?
> i'm wondering how can i get the best performance from a table that as 3
> fields to be unique
> should i put a primary key on the 3 fields , and sql will index it
> or should i create a unique constraint ?
> if so do i need to create an index on the 3 fields too or the unique
> constraint is being indexed anyway ?
> Thanks
>|||I stand correct - yes, a unique constraint will force the creation of a
unique index to enforce it. Sorry bout that, thanks SQLPoet.
"SQLPoet" <sqlpoet@.gmail.com> wrote in message
news:1152285705.793579.309650@.s16g2000cws.googlegroups.com...
>A unique constraint WILL automaitically create a unique index on the
> fields included in the definition.
> Whether to use a unique constraint or a primary key requires
> consideration of a few things. Can you give more details about what you
> are trying to accomplish?
>
> frederic.nourry@.gmail.com wrote:
>> Hi,
>> the main question is : are unique constraint indexed automatically or
>> do i have to create an index too?
>> i'm wondering how can i get the best performance from a table that as 3
>> fields to be unique
>> should i put a primary key on the 3 fields , and sql will index it
>> or should i create a unique constraint ?
>> if so do i need to create an index on the 3 fields too or the unique
>> constraint is being indexed anyway ?
>> Thanks
>|||More discussion about this question.
http://support.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.programming&mid=17b7877f-6978-4038-bc16-0c426b6cf7eb&sloc=en-us&sloc=en-us
AMB
"Alejandro Mesa" wrote:
> frederic.nourry@.gmail.com,
> > the main question is : are unique constraint indexed automatically or
> > do i have to create an index too?
> No, SQL Server implement a unique constraint by creating a unique index
> (nonclustered by default).
> Example:
> -- sql 2k
> create table dbo.t1 (
> c1 int not null,
> constraint uq_t1_c1 unique (c1)
> )
> go
> select [name]
> from sysindexes
> where [id] = object_id('dbo.t1')
> go
> drop table dbo.t1
> go
> > i'm wondering how can i get the best performance from a table that as 3
> > fields to be unique
> >
> > should i put a primary key on the 3 fields , and sql will index it
> >
> > or should i create a unique constraint ?
> Do those columns accept NULL value?
> Is there a primary key defined already?
> A primary key can not be NULL by definition and a table can have just one.
>
> AMB
> "frederic.nourry@.gmail.com" wrote:
> > Hi,
> >
> > the main question is : are unique constraint indexed automatically or
> > do i have to create an index too?
> >
> > i'm wondering how can i get the best performance from a table that as 3
> > fields to be unique
> >
> > should i put a primary key on the 3 fields , and sql will index it
> >
> > or should i create a unique constraint ?
> > if so do i need to create an index on the 3 fields too or the unique
> > constraint is being indexed anyway ?
> >
> > Thanks
> >
> >|||Yes, I stand corrected again :) Thank you. Brain went to Primary Key instead
originally. That and a lack of caffeine.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:25284DF0-5FBC-47B7-865E-72F1D4AD128F@.microsoft.com...
> frederic.nourry@.gmail.com,
>> the main question is : are unique constraint indexed automatically or
>> do i have to create an index too?
> No, SQL Server implement a unique constraint by creating a unique index
> (nonclustered by default).
> Example:
> -- sql 2k
> create table dbo.t1 (
> c1 int not null,
> constraint uq_t1_c1 unique (c1)
> )
> go
> select [name]
> from sysindexes
> where [id] = object_id('dbo.t1')
> go
> drop table dbo.t1
> go
>> i'm wondering how can i get the best performance from a table that as 3
>> fields to be unique
>> should i put a primary key on the 3 fields , and sql will index it
>> or should i create a unique constraint ?
> Do those columns accept NULL value?
> Is there a primary key defined already?
> A primary key can not be NULL by definition and a table can have just one.
>
> AMB
> "frederic.nourry@.gmail.com" wrote:
>> Hi,
>> the main question is : are unique constraint indexed automatically or
>> do i have to create an index too?
>> i'm wondering how can i get the best performance from a table that as 3
>> fields to be unique
>> should i put a primary key on the 3 fields , and sql will index it
>> or should i create a unique constraint ?
>> if so do i need to create an index on the 3 fields too or the unique
>> constraint is being indexed anyway ?
>> Thanks
>>|||Hi thanks for the answers
Colums do not accept null
and a primary key is set on the first column which is an identity.
also i'm using the interface to create the key and constraints.
i guess i can simply set my primary key and create a unique constraint
on the 3 columns that i need to be unique.
this will also create 2 indexes , 1 for the primary key and the other
for the unique constraint.
thanks for all the answer , it did help to clarify things