Re: Index Question

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 04/23/04 

Date: Fri, 23 Apr 2004 14:08:29 -0400

Actually, you can do a create index with drop_existing on a primary key
constraint. 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message
news:4089509A.159F120B@toomuchspamalready.nl...
One of the few benefits is, that you can use CREATE INDEX ... WITH
DROP_EXISTING to move the index/table to a different filegroup. With a
primary key constraint this is not possible, and moving a tables
requires much more statements and processing...
Gert-Jan
JLS wrote:
>
> But what is your opinion on this?  I can't replicate this table due to the
> lack of a primary key, so what is the benefit of creating the index over
the
> constraint?  Any?  I'm just trying to understand the logic behind what
they
> chose.
>
> Thanx!
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:unbPKlUKEHA.2452@TK2MSFTNGP09.phx.gbl...
> > You can create a unique clustered index on a table.  Alternatively - and
> > preferably - you can create a primary key constraint.  Behind the
scenes,
> a
> > unique index is created to support the primary key.  Looks like whoever
> > created the table chose to create the index instead of the constraint.
> >
> > --
> > Tom
> >
> > ---------------------------------------------------------------
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com/sql
> >
> >
> > "JLS" <jlshoop@hotmail.com> wrote in message
> > news:%23X3YtXUKEHA.2808@TK2MSFTNGP11.phx.gbl...
> > I have a table which has no primary key, yet when I script it the
> > Create Unique Clustered Index (PK_Table_Name) ...... is present in the
> > script.
> >
> > I have looked at books online, and I can't seem to find an explanation
for
> > why someone would put a unique clustered index on a table, but not a
> primary
> > key, which would result in the creation of the index.
> >
> > Please shed some light for me, this just isn't clicking....
> >
> > Thanx!
> >
-- 
(Please reply only to the newsgroup)

Relevant Pages

  • Re: Table Design Advice
    ... I will be creating db in SQL server though. ... MaterialCost (Currency) ... CONSTRAINT pk_products PRIMARY KEY, ...
    (microsoft.public.sqlserver.setup)
  • Re: Indexes and primary keys, from Delaney
    ... > One of the issues here is that the Primary Key is a logical concept, ... > constraint mechanism to define a set of columns as the PK, ... > asking SQL Server to enforce the uniqueness of the columns. ... >> SSN and account number, ...
    (microsoft.public.sqlserver.programming)
  • Re: Table Design Advice
    ... Are you sure you are using SQL Server? ... CONSTRAINT pk_products PRIMARY KEY, ...
    (microsoft.public.sqlserver.setup)
  • Re: Table Design Advice
    ... Are you sure you are using SQL Server? ... CONSTRAINT pk_products PRIMARY KEY, ...
    (microsoft.public.sqlserver.setup)
  • Re: Clustered Index question
    ... Columnist, SQL Server Professional ... PACKET_CONTRACT,PACKET_TIME resulted in the fastest query. ... > SQL Server MVP ... even though I never query for the primary key column? ...
    (microsoft.public.sqlserver.programming)