Post reply

Regarding Index

  • October 20, 2008 at 12:10 pm

    #96559

    Hi have a small question(indexes)

    I created a primary key on a table.

    There is clustered index created by default .

    BUT ....

    I want to create an index on some other column not where the primary key is . So, I

    tried to drop the index but it says "we cant delete an index created by primary key constraint"(from BOL)

    Is there any way we can drop the index without dropping the primary key?

    GUYS Please Help me.I am a learner.

    Thanks a Lot

  • October 20, 2008 at 1:08 pm

    #887285

    You can create a non-clustered index on the column(s) you want indexed. You don't need to drop the clustered index.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • October 20, 2008 at 1:10 pm

    #887287

    You'll have to drop and recreate the primary key. Which means if the table is acting as the parent in referential constraints, you'll need to drop and recreate those references too.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • October 22, 2008 at 8:28 am

    #888143

    THanks a lot guys !!!!!!!!!!

    IT worked

  • October 24, 2008 at 4:52 am

    #889122

    [font="Verdana"]

    tinku.reddy (10/20/2008)

    Hi have a small question(indexes)

    I created a primary key on a table.

    There is clustered index created by default .

    BUT ....

    I want to create an index on some other column not where the primary key is . So, I

    tried to drop the index but it says "we cant delete an index created by primary key constraint"(from BOL)

    Is there any way we can drop the index without dropping the primary key?

    GUYS Please Help me.I am a learner.

    Thanks a Lot

    Why don't you created NonClustered PK on your table then? i.e.

    Create Table myTable

    (SrInt Primary Key NonClustered

    ,UName VarChar(10))

    so that u can create Clustered index on some other column

    Mahesh

    [/font]

    MH-09-AM-8694

  • October 24, 2008 at 7:09 am

    #889189

    @mahesh,

    I know I can create a non clustered index but I just want to know

    If there is any other chance to create a clustered without dropping

    a PK.

    Thank you

  • October 24, 2008 at 7:41 am

    #889220

    Actually, you can change the PK from nonclustered to clustered without dropping it. Here's an example:

    CREATE TABLE t1

    (c1 INT NOT NULL);

    ALTER TABLE t1 ADD CONSTRAINT pk_t1 PRIMARY KEY NONCLUSTERED (c1);

    CREATE UNIQUE clustered INDEX pk_t1 ON t1(c1) WITH DROP_EXISTING

    You can't go the other way though

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • October 24, 2008 at 4:43 pm

    #889550

    Thanks Grant Thanks a lot

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply