All,
To my understanding, the primary key is automatically indexed.
But in Enterprise Mananger (Design table/ indexes keys). It shows:
(x) Create UNIQUE
O Constraint <-- this is select
O Index
and you can NOT change the selection.
So I am confused that if an index is created or not. Do I need to
create the (unique) index on the primary key column so I can search on
this column with best performance.
Thanks
John
Primary Keys are automatically indexed. you do not have to create the index
manually.
Note: This is NOT the case for Foreign Keys
Note2: By Default SQL Server will create Primary Keys as "Clustered"
indexes. You need to be careful as the Clustered index may be better placed
on another column or columns.
Cheers,
Greg Jackson
PDX, Oregon
|||A Primary Key is a Constraint, which is an element of your logical data
model, rather than an Index, which is a feature of your table's physical
implementation. Behind the scenes the result is the same - the PK constraint
is implemented as a unique index and you don't need to create another index
explicitly.
David Portas
SQL Server MVP
|||Thanks for the clarification from Greg and David.
How about the Unique Contraint? I think it is just like PK, isn't it?
|||In My mind, a unique constrain is mainly there for you alternate keys, where a primary key
constraint is there for your primary key.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q. John Chen" <nonospam@.wowway.com> wrote in message
news:8488de58.0406170656.4e4dd32b@.posting.google.c om...
> Thanks for the clarification from Greg and David.
> How about the Unique Contraint? I think it is just like PK, isn't it?
|||A Unique Constraint is applied to force Non PKey Items Unique.
Pkey is defined for referential integrity purposes, etc.
Cheers
GAJ
|||Thank for your response.
My question is that whether an index will be automatically created for
a Unique constraint. So I don't have create index for the performance
reason.
Thanks again.
John.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message news:<#$zxjzIVEHA.3656@.TK2MSFTNGP11.phx.gbl>...
> A Unique Constraint is applied to force Non PKey Items Unique.
> Pkey is defined for referential integrity purposes, etc.
>
> Cheers
>
> GAJ
|||On 21 Jun 2004 09:35:50 -0700, Q. John Chen wrote:
>Thank for your response.
>My question is that whether an index will be automatically created for
>a Unique constraint. So I don't have create index for the performance
>reason.
>Thanks again.
>John.
Hi John,
If you define a UNIQUE constraint, SQL Server will indeed create an index
that is used for enforcing the constraint, but can also be used to
optimize queries.
The index created will be nonclustered by default, but you can override
this if you prefer a clustered index - check BOL for details.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Showing posts with label indexed. Show all posts
Showing posts with label indexed. Show all posts
Monday, March 26, 2012
PrimaryKey and Index
All,
To my understanding, the primary key is automatically indexed.
But in Enterprise Mananger (Design table/ indexes keys). It shows:
(x) Create UNIQUE
O Constraint <-- this is select
O Index
and you can NOT change the selection.
So I am confused that if an index is created or not. Do I need to
create the (unique) index on the primary key column so I can search on
this column with best performance.
Thanks
JohnPrimary Keys are automatically indexed. you do not have to create the index
manually.
Note: This is NOT the case for Foreign Keys
Note2: By Default SQL Server will create Primary Keys as "Clustered"
indexes. You need to be careful as the Clustered index may be better placed
on another column or columns.
Cheers,
Greg Jackson
PDX, Oregon|||A Primary Key is a Constraint, which is an element of your logical data
model, rather than an Index, which is a feature of your table's physical
implementation. Behind the scenes the result is the same - the PK constraint
is implemented as a unique index and you don't need to create another index
explicitly.
David Portas
SQL Server MVP
--|||Thanks for the clarification from Greg and David.
How about the Unique Contraint? I think it is just like PK, isn't it?|||In My mind, a unique constrain is mainly there for you alternate keys, where
a primary key
constraint is there for your primary key.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q. John Chen" <nonospam@.wowway.com> wrote in message
news:8488de58.0406170656.4e4dd32b@.posting.google.com...
> Thanks for the clarification from Greg and David.
> How about the Unique Contraint? I think it is just like PK, isn't it?|||A Unique Constraint is applied to force Non PKey Items Unique.
Pkey is defined for referential integrity purposes, etc.
Cheers
GAJ|||Thank for your response.
My question is that whether an index will be automatically created for
a Unique constraint. So I don't have create index for the performance
reason.
Thanks again.
John.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message news:<#$zxjzIVEHA.3656@.TK2MSFTNGP11.p
hx.gbl>...
> A Unique Constraint is applied to force Non PKey Items Unique.
> Pkey is defined for referential integrity purposes, etc.
>
> Cheers
>
> GAJ|||On 21 Jun 2004 09:35:50 -0700, Q. John Chen wrote:
>Thank for your response.
>My question is that whether an index will be automatically created for
>a Unique constraint. So I don't have create index for the performance
>reason.
>Thanks again.
>John.
Hi John,
If you define a UNIQUE constraint, SQL Server will indeed create an index
that is used for enforcing the constraint, but can also be used to
optimize queries.
The index created will be nonclustered by default, but you can override
this if you prefer a clustered index - check BOL for details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
To my understanding, the primary key is automatically indexed.
But in Enterprise Mananger (Design table/ indexes keys). It shows:
(x) Create UNIQUE
O Constraint <-- this is select
O Index
and you can NOT change the selection.
So I am confused that if an index is created or not. Do I need to
create the (unique) index on the primary key column so I can search on
this column with best performance.
Thanks
JohnPrimary Keys are automatically indexed. you do not have to create the index
manually.
Note: This is NOT the case for Foreign Keys
Note2: By Default SQL Server will create Primary Keys as "Clustered"
indexes. You need to be careful as the Clustered index may be better placed
on another column or columns.
Cheers,
Greg Jackson
PDX, Oregon|||A Primary Key is a Constraint, which is an element of your logical data
model, rather than an Index, which is a feature of your table's physical
implementation. Behind the scenes the result is the same - the PK constraint
is implemented as a unique index and you don't need to create another index
explicitly.
David Portas
SQL Server MVP
--|||Thanks for the clarification from Greg and David.
How about the Unique Contraint? I think it is just like PK, isn't it?|||In My mind, a unique constrain is mainly there for you alternate keys, where
a primary key
constraint is there for your primary key.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q. John Chen" <nonospam@.wowway.com> wrote in message
news:8488de58.0406170656.4e4dd32b@.posting.google.com...
> Thanks for the clarification from Greg and David.
> How about the Unique Contraint? I think it is just like PK, isn't it?|||A Unique Constraint is applied to force Non PKey Items Unique.
Pkey is defined for referential integrity purposes, etc.
Cheers
GAJ|||Thank for your response.
My question is that whether an index will be automatically created for
a Unique constraint. So I don't have create index for the performance
reason.
Thanks again.
John.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message news:<#$zxjzIVEHA.3656@.TK2MSFTNGP11.p
hx.gbl>...
> A Unique Constraint is applied to force Non PKey Items Unique.
> Pkey is defined for referential integrity purposes, etc.
>
> Cheers
>
> GAJ|||On 21 Jun 2004 09:35:50 -0700, Q. John Chen wrote:
>Thank for your response.
>My question is that whether an index will be automatically created for
>a Unique constraint. So I don't have create index for the performance
>reason.
>Thanks again.
>John.
Hi John,
If you define a UNIQUE constraint, SQL Server will indeed create an index
that is used for enforcing the constraint, but can also be used to
optimize queries.
The index created will be nonclustered by default, but you can override
this if you prefer a clustered index - check BOL for details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
PrimaryKey and Index
All,
To my understanding, the primary key is automatically indexed.
But in Enterprise Mananger (Design table/ indexes keys). It shows:
(x) Create UNIQUE
O Constraint <-- this is select
O Index
and you can NOT change the selection.
So I am confused that if an index is created or not. Do I need to
create the (unique) index on the primary key column so I can search on
this column with best performance.
Thanks
JohnPrimary Keys are automatically indexed. you do not have to create the index
manually.
Note: This is NOT the case for Foreign Keys
Note2: By Default SQL Server will create Primary Keys as "Clustered"
indexes. You need to be careful as the Clustered index may be better placed
on another column or columns.
Cheers,
Greg Jackson
PDX, Oregon|||A Primary Key is a Constraint, which is an element of your logical data
model, rather than an Index, which is a feature of your table's physical
implementation. Behind the scenes the result is the same - the PK constraint
is implemented as a unique index and you don't need to create another index
explicitly.
--
David Portas
SQL Server MVP
--|||Thanks for the clarification from Greg and David.
How about the Unique Contraint? I think it is just like PK, isn't it?|||In My mind, a unique constrain is mainly there for you alternate keys, where a primary key
constraint is there for your primary key.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q. John Chen" <nonospam@.wowway.com> wrote in message
news:8488de58.0406170656.4e4dd32b@.posting.google.com...
> Thanks for the clarification from Greg and David.
> How about the Unique Contraint? I think it is just like PK, isn't it?|||A Unique Constraint is applied to force Non PKey Items Unique.
Pkey is defined for referential integrity purposes, etc.
Cheers
GAJ|||Thank for your response.
My question is that whether an index will be automatically created for
a Unique constraint. So I don't have create index for the performance
reason.
Thanks again.
John.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message news:<#$zxjzIVEHA.3656@.TK2MSFTNGP11.phx.gbl>...
> A Unique Constraint is applied to force Non PKey Items Unique.
> Pkey is defined for referential integrity purposes, etc.
>
> Cheers
>
> GAJ|||On 21 Jun 2004 09:35:50 -0700, Q. John Chen wrote:
>Thank for your response.
>My question is that whether an index will be automatically created for
>a Unique constraint. So I don't have create index for the performance
>reason.
>Thanks again.
>John.
Hi John,
If you define a UNIQUE constraint, SQL Server will indeed create an index
that is used for enforcing the constraint, but can also be used to
optimize queries.
The index created will be nonclustered by default, but you can override
this if you prefer a clustered index - check BOL for details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
To my understanding, the primary key is automatically indexed.
But in Enterprise Mananger (Design table/ indexes keys). It shows:
(x) Create UNIQUE
O Constraint <-- this is select
O Index
and you can NOT change the selection.
So I am confused that if an index is created or not. Do I need to
create the (unique) index on the primary key column so I can search on
this column with best performance.
Thanks
JohnPrimary Keys are automatically indexed. you do not have to create the index
manually.
Note: This is NOT the case for Foreign Keys
Note2: By Default SQL Server will create Primary Keys as "Clustered"
indexes. You need to be careful as the Clustered index may be better placed
on another column or columns.
Cheers,
Greg Jackson
PDX, Oregon|||A Primary Key is a Constraint, which is an element of your logical data
model, rather than an Index, which is a feature of your table's physical
implementation. Behind the scenes the result is the same - the PK constraint
is implemented as a unique index and you don't need to create another index
explicitly.
--
David Portas
SQL Server MVP
--|||Thanks for the clarification from Greg and David.
How about the Unique Contraint? I think it is just like PK, isn't it?|||In My mind, a unique constrain is mainly there for you alternate keys, where a primary key
constraint is there for your primary key.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q. John Chen" <nonospam@.wowway.com> wrote in message
news:8488de58.0406170656.4e4dd32b@.posting.google.com...
> Thanks for the clarification from Greg and David.
> How about the Unique Contraint? I think it is just like PK, isn't it?|||A Unique Constraint is applied to force Non PKey Items Unique.
Pkey is defined for referential integrity purposes, etc.
Cheers
GAJ|||Thank for your response.
My question is that whether an index will be automatically created for
a Unique constraint. So I don't have create index for the performance
reason.
Thanks again.
John.
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message news:<#$zxjzIVEHA.3656@.TK2MSFTNGP11.phx.gbl>...
> A Unique Constraint is applied to force Non PKey Items Unique.
> Pkey is defined for referential integrity purposes, etc.
>
> Cheers
>
> GAJ|||On 21 Jun 2004 09:35:50 -0700, Q. John Chen wrote:
>Thank for your response.
>My question is that whether an index will be automatically created for
>a Unique constraint. So I don't have create index for the performance
>reason.
>Thanks again.
>John.
Hi John,
If you define a UNIQUE constraint, SQL Server will indeed create an index
that is used for enforcing the constraint, but can also be used to
optimize queries.
The index created will be nonclustered by default, but you can override
this if you prefer a clustered index - check BOL for details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Friday, March 23, 2012
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
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
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:
>|||More discussion about this question.
http://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> frederic.nourry@.gmail.com,
>
> 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
>
> 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:
>|||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...[vbcol=seagreen]
> frederic.nourry@.gmail.com,
>
> 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
>
> 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 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
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:
>|||More discussion about this question.
http://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> frederic.nourry@.gmail.com,
>
> 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
>
> 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:
>|||Yes, I stand corrected again
Thank you. Brain went to Primary Key insteadoriginally. That and a lack of caffeine.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:25284DF0-5FBC-47B7-865E-72F1D4AD128F@.microsoft.com...[vbcol=seagreen]
> frederic.nourry@.gmail.com,
>
> 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
>
> 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 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
Subscribe to:
Posts (Atom)