Re: Index Question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Gert-Jan Strik (sorry_at_toomuchspamalready.nl)
Date: 04/23/04 

Date: Fri, 23 Apr 2004 19:21:30 +0200

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: Create Table Issues
    ... unique index on BookID. ... > CONSTRAINT PubPK PRIMARY KEY ... > CONSTRAINT PubFK FOREIGN KEY REFERENCES Publisher ...
    (microsoft.public.access.gettingstarted)