Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    Texas
    Posts
    1

    Unanswered: How to change the primary key to a clustered index

    Hi,

    I am new to SQL Server. I just transferred a database from MS Access 2000 to SQL Server. This causes the primary key to be imported as a non-clustered index. What would be the fastest and easiest way to change the primary key of each table in the database from non-clustered to clustered.

    I know that SQL Server creates a clustered index for the primary keys by default but this is different since I imported the database from Access 2000.

    Is there a stored procedure I can use??

    Any help would be greatly appreciated.

    Thank you.
    Chirag Parikh

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ALTER TABLE?

    I would create the structures the way I want them first, then transfer the data..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Posts
    522
    also, make sure that you really need every pk to be clustered. remember that you won't have a luxury of having a single clustered index on any table after that.

  4. #4
    Join Date
    Oct 2003
    Location
    Pune, India
    Posts
    14
    Go to Enerprise Manager. Right Click the table on which you want to create the clustered index. Select All Tasks -> Manage Indexes.

    In the Manage Index Window, click on NEW Tab. Select the column in the "Create New Index Window". Then check the "Clustered Index" check box. Also you will need to give an index name.

    However, because clustered indexes affect the physical order of the data as stored on disk, each table can have only one clustered index. After all, you can’t physically arrange people, for example, in order both by their age and their height, you have to pick one.

    In fact, every SQL Server table has a clustered index, whether you know it or not. If you don’t explicitly create a clustered index, SQL server uses a phantom clustered index that simply places rows in order by their ordinal number, which is basically the order in which the rows were created.
    Thanks & Regards,
    Dipayan Sarkar
    (dipayan@covisible.com)
    (CoVisible Solutions - A Knowledge Management Software company)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •