Best Way to Change Clustered Index (Pk) in SQL 2005

Best way to change clustered index (PK) in SQL 2005

If your table is getting up to 1 TB in size and probably has LOTS of rows in it, I would strongly recommend NOT making the clustered index that much fatter!

First of all, dropping and recreating the clustered index will shuffle around ALL your data at least once - that alone will take ages.

Secondly, the big compound clustered index you're trying to create will significantly increase the size of all your non-clustered indices (since they contain the whole clustered index value on each leaf node, for the bookmark lookups).

The question is more: why are you trying to do this?? Couldn't you just add another non-clustered index with those columns, to potentially cover your queries? Why does this have to be the clustered index?? I don't see any advantage in that....

For more information on indexing and especially the clustered index debate, see Kimberly Tripp's blog on SQL Server indexes - very helpful!

Marc

How to change the primary key to be non-clustered?

Drop the clustered index, then recreate the primary key as non-clustered:

ALTER TABLE dbo.Config DROP CONSTRAINT PK_Config
go
ALTER TABLE dbo.Config ADD CONSTRAINT PK_Config 
    PRIMARY KEY NONCLUSTERED (ConfigID)

TSQL Alter PRIMARY KEY Cluster Index MSSQL2008r2

Here is what I've done in the past to change a primary key on a table:

BEGIN TRANSACTION doStuff

DECLARE @isValid bit
SET     @isValid = 1

DECLARE @pkName varchar(50)
SET     @pkName = (
    SELECT TOP 1 name
    FROM sys.key_constraints
    WHERE type ='pk'
        AND OBJECT_NAME(parent_object_id) = N'TableName'
)

DECLARE @sql nvarchar(2000)
SET @sql = N'
ALTER TABLE dbo.TableName
DROP CONSTRAINT ' + @pkName

EXEC (@sql)

IF (@@ERROR <> 0)
    BEGIN
        PRINT 'Error deleting primary key'
        SET @isValid = 0
    END

ALTER TABLE dbo.TableName
ADD PRIMARY KEY (primary key columns separated by comma)

IF (@@ERROR <> 0)
    BEGIN
        PRINT 'Error creating primary key'
        SET @isValid = 0
    END

IF (@isValid = 1)
    BEGIN
        PRINT 'Commit'
        COMMIT TRANSACTION doStuff
    END
ELSE
    BEGIN
        PRINT 'Rollback'
        ROLLBACK TRANSACTION doStuff
    END

Note as pointed out in: Best way to change clustered index (PK) in SQL 2005 this will reorder the data in your table throughout the operation, so depending on the size of the table it could take a significant amount of time.

Change a Primary Key from Nonclustered to Clustered

1) Drop the existing clustered index first (IX_TableX_FieldB):

   DROP INDEX TableX.IX_TableX_FieldB

2) Create a (temporary) UNIQUE constraint on the unique fields referenced in the primary key

    ALTER TABLE TableX
    ADD CONSTRAINT UQ_TableX UNIQUE(FieldA)

3) Drop the PRIMARY KEY

    ALTER TABLE TableX
    DROP CONSTRAINT PK_TableX

4) Recreate the PRIMARY KEY as CLUSTERED

   ALTER TABLE TableX
   ADD CONSTRAINT PK_TableX PRIMARY KEY CLUSTERED(FieldA)

5) Drop the temporary UNIQUE constraint

   ALTER TABLE TableX
   DROP CONSTRAINT UQ_TableX

6) Add the IX_TableX_FieldB back on as NONCLUSTERED

   CREATE NONCLUSTERED INDEX IX_TableX_FieldB ON TableX(FieldB)

How to convert clustered primary key to non-clustered without dropping referring foreign keys in SQL Server 2005

You could try creating the unique nonclustered NC index first, then drop the clustered PK. The FK should recognise this other index (but might not: never tried it).

When you run ALTER TABLE to drop the clustered PK use the ONLINE option. However, it's only available in Enterprise edition.

ALTER TABLE Mytable DROP CONSTRAINT PK_Mytable WITH (ONLINE = ON)

You can't use ONLINE for the ADD CONSTRAINT bit.

Basically, your options are limited without blocking, or creating another table first and moving data over...

Smart choice for primary key and clustered index on a table in SQL 2005 to boost performance of selecting single record or multiple records

Based on the current table layout you want some indexes like this.

CREATE INDEX IX_YourTable_1 ON dbo.YourTable
(EntryDate, Id)
INCLUDE (SLug)
WITH (FILLFACTOR=90)

CREATE INDEX IX_YourTable_2 ON dbo.YourTable
(EntryDate, Slug)
INCLUDE (Id)
WITH (FILLFACTOR=80)

Add any other columns you are returning to the INCLUDE line.

Change your second query to something like this.

Select Id, Slug, ..., EntryDate
From TableName
Where Slug = @slug 
    AND EntryDate BETWEEN CAST(CAST(@EntryYear AS VARCHAR(4) + CAST(@EntryMonth AS VARCHAR(2)) + '01' AS DATE) AND DATEADD(mm, 1, CAST(CAST(@EntryYear AS VARCHAR(4) + CAST(@EntryMonth AS VARCHAR(2)) + '01' AS DATE))

The way your second query is currently written the index will never be used. If you can change the Slug column to a related table it will increase your performance and decrease your storage requirements.

Composite clustered index and non clustered index in sql server 2005

It depends on what the query look like, what columns are accessed, is it covering etc

A simple GROUP BY on type will most likely use the NC index. If you use other columns you may get a bookmap/key lookup or the index will be ignored and you'll have an inefficient PK scan

What happens when I drop a clustered primary key in SQL 2005

Clustered index is not "just a constraint", it's a storage method.

When you drop it, your data are being reordered from clustered storage to heap storage

Other indexes are being updated to refer to RID's instead of PRIMARY KEY values.

SQL 2005: Keys, Indexes and Constraints Questions

Clustered indexes are, as you put it correctly, the definition as to how data in a table is stored physically, i.e. you have a B-tree sorted using the clustering key and you have the data at the leaf level.

Non-clustered indexes on the other hand are separate tree structures which at the leaf level only have the clustering key (or a RID if the table is a heap), meaning that when you use a non-clustered index, you'll have to use the clustered index to get the other columns (unless your request is fully covered by the non-clustered index, which can happen if you request only the columns, which constitute the non-clustered index key columns).

When should you use one or the other ? Well, since you can have only one clustered index, define it on the columns which makes most sense, i.e. when you look up clients by ID most of the time, define a clustered index on the ID. Non-clustered indexes should be defined on columns which are used less often.

Regarding performance, inserts or updates that change the index key are always painfull, regardless of whether it is a clusted on non-clustered index, since page splits can happen, which forces data to be moved between pages (moving the pages of a clustered index hurts more, since you have more data in the leaf level). Thus the general rule is to avoid changing the index key and inserting new values so that they would be sequencial. Otherwise you'll encounter fragmentation and will have to rebuild your index on a regular basis.

Finally, regarding constraints, by definition, they have nothing to do with indexes, yet SQL server has chosen to implement them using indexes. E.g. currently, a unique constraint is implemented as an index, however this can change in a future version (though I doubt that will happen). The type of index (clustered or not) is up to you, just remember that you can have only one clustered index.

If you have more questions of this type, I highly recommend reading this book, which covers these topics in depth.

---

Related Topics