locked
Changing the index type of primary key RRS feed

  • Question

  • Hi everyone,

    We have a table, which has one clustered index and one non clustered in index(primary). I want to drop the existing clustered index and make the primary key as clustered. Is there any easy way to do that. Will Drop_Existing help on this matter?

    Thanks


    A.G

    Thursday, February 5, 2015 3:21 AM

Answers

  • 1. Drop index that is a clustered

    2. Drop PK consraint

    3. Create PK constraint on the col and make it clusteted

    4. create NCI index on the col which was a clustered before

    DROP INDEX IX_t1 ON dbo.t1
    GO
    ALTER TABLE dbo.t1
    DROP CONSTRAINT PK_t1
    GO
    ALTER TABLE dbo.t1 ADD CONSTRAINT
    PK_t1 PRIMARY KEY CLUSTERED 
    (
    c
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO
    CREATE NONCLUSTERED INDEX IX_t1 ON dbo.t1
    (
    c2
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, February 5, 2015 6:09 AM