Is there a way to convert a non-clustered index on a Primary Key to a clustered one? (SQL Server 2008)

With Management Studio I can't alter the index. Dropping it doesn't work because it's being used as a foreign key in a lot of other tables. Can I somehow alter it? 开发者_运维问答Or how would you do this?

---

If your existing PK is referenced by many other tables then you're going to spend many tedious and error-prone minutes writing the script to drop all the FK references and recreate them.

SQL Server Management Studio can do this for you. What you may not have realized is that you can only have one clustered index on a table, because the clustered index represents the physical ordering of rows; this means that you first have to bring up the clustered index and turn off clustering. Then and only then can you pull up another index and turn on clustering.

You need to do this from the table designer, then right-click and choose Indexes/Keys.... First find the existing clustered index (probably the primary key) and change Create as Clustered to No. Then go to the other index and change Create as Clustered to Yes for that. If the table is large, the operation is liable to time out while you save; you can get around this by having SSMS generate a change script (right-click on the designer after changing the indexes and you'll see the option). Then you can run this script in a query window with no timeout.

If you look at this change script, you'll see all of the work it's doing creating staging tables and switching keys around; it's a pain to write this manually. Let SSMS do it for you.

---

You can't convert it in-place - you need to drop the primary key constraint first (which will also automatically drop the nonclustered index "behind" the primary key constraint), and then re-create it as a clustered index:

ALTER TABLE dbo.YourTable
DROP CONSTRAINT PK_YourTable

and then re-create it as clustered:

ALTER TABLE dbo.YourTable
ADD CONSTRAINT PK_YourTable
PRIMARY KEY CLUSTERED (YourPKField)

---

You can actually alter a non-clustered PK and make it Clustered with this syntax

CREATE UNIQUE CLUSTERED INDEX [PK_Customer] on Customer(CustomerID) WITH DROP_EXISTING

You need to clean up your constraints first :S

---

You'll have to also drop the FK constraints, change the PK and then recreate the FK constraints after the new PK is in place. Foreign key constraints require the referenced key to be unique, a primary key is, by definition, unique, so dropping the PK is not allowed while FK constraints are in place referencing the PK.

Changing to a clustered index rewrites the table. Any time I consider moving to/from a clustered index or changing the clustered index, I would reconsider its necessity and choice of the keys to the clustered index - particularly if it's not going to be unique or increasing (like a datetime for a timestamp). Remember, a clustered index is not really an index in the first place - it is the order of data in the pages. This is why clustering on an increasing key helps with page splits when you add data to the table.

---

Due to a data modeler setting oversight done years ago by a long-gone developer, I had a whole database full of tables with non-clustered indexes on the PK and no clustered indexes. I found a StackOverflow article with code that did the FK drops, dropped the PK constraint; re-added the PK constraint as a clustered index and then re-added the FKs: Change a Primary Key from Nonclustered to Clustered I revised that code to incorporate several of the great StackOverflow comments on it; made it loop through all the tables in my DB (except for two that I made the code exclude); found it created non-trusted foreign keys (per sp_Blitz); fixed that and voila! the code below that seems to work quite well to add clustered indexes to all the heap tables in a db - if all tables in the DB have identity field PKs except the excluded ones.

/* Script to take tables with a primary key but not a clustered index to being tables
with a primary key and a clustered index on the PK field.  Foreign keys are dropped and recreated.
Much borrowing from a great piece of code from StackOverflow

What if you have a table or two you don't want a clustered index on?
Lines 38 and 39 are for tables you want excluded from the process.  Input your own table names here,
if any.

After it finishes, test the altered db against an unaltered version of the db and check
that it recreated all constraints/FKs.  The only diff should be the clustered index vs non-clustered.

Revisions made by Ed Z 10/2019:
Original script processes one table only - I wrapped it in a loop that processes all heap tables in a database.
Original script prints the commands - this script both prints them and runs them.  If you want to be safe - 
just comment out the 5 exec statements and inspect the printed commands; then run them if you wish.  
Original script creates untrusted foreign keys (per sp_Blitz) - this script creates trusted foreign keys.
*/

SET NOCOUNT ON;

DECLARE @PKTableName VARCHAR(100), 
        @PKName varchar(100),
        @FKName varchar(100),
        @sql varchar(max),
        @PKcolumnName varchar(30),
        @table VARCHAR(100),
        @FKColumnName VARCHAR(100)

SELECT @PKTableName = ''

-- loop thru all the Heap tables; assumes they have a primary key identity field.
WHILE 1 = 1
BEGIN
    select @PKTableName = MIN(so.name) 
    from sys.indexes si inner join sys.objects so on si.object_id = so.object_id
    where so.is_ms_shipped = 0  
    and si.type_desc = 'Heap'
    and so.name not like '<a table name you want excluded>'  -- exclude a couple of tables that lack a unique key
    and so.name not like '<a table name you want excluded>'
    and so.name > @PKTableName

    IF @PKTableName is null
        BREAK

    --initialize
    select @PKName = '', @FKName = '', @PKcolumnName = '', @sql = '', @table = '', @FKColumnName = ''

    IF  EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'[dbo].[FKAgainstTableList]'))
    BEGIN 
        DROP TABLE FKAgainstTableList
    END
    --CREATE TABLE FKAgainstTableList (ForeignKey VARCHAR(30),[Table] VARCHAR(30))
    --SET @PKTableName = 'MYTABLE'
    set @PKName = (SELECT name FROM sys.indexes WHERE OBJECT_NAME(object_id) = @PKTableName AND is_primary_key = 1)
    set @PKcolumnName = (SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = @PKTableName AND is_identity =1)
    /* OR use, if you are not sure there is only one column in the primary key or for primary keys that are not identity fields:
    SELECT @PKcolumnName=column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = @PKTableName
    */
    -- PRINT @PKcolumnName  -- debug

     SELECT  OBJECT_NAME(sys.foreign_key_columns.parent_object_id) [Table],sys.columns.name [FKColumnName],sys.foreign_keys.name [FKName] 
        INTO FKAgainstTableList
        FROM sys.foreign_keys INNER JOIN sys.foreign_key_columns 
        ON sys.foreign_keys.object_id = sys.foreign_key_columns.constraint_object_id
        INNER JOIN sys.columns ON sys.columns.object_id = sys.foreign_keys.parent_object_id AND sys.columns.column_id = sys.foreign_key_columns.parent_column_id
        WHERE OBJECT_NAME(sys.foreign_keys.referenced_object_id) = @PKTableName

    DECLARE table_cur1 CURSOR  FOR
        SELECT  * FROM FKAgainstTableList

        PRINT @sql

    -------------------------------Disable constraint on FK Tables
    OPEN table_cur1
    FETCH NEXT FROM table_cur1 INTO @table,@FKColumnName,@FKName
    WHILE   @@FETCH_STATUS = 0
        BEGIN
            SET @sql ='ALTER TABLE '+@table+' DROP CONSTRAINT '+ @FKName
            PRINT @sql
            EXEC(@sql)
            FETCH NEXT FROM table_cur1 INTO @table,@FKColumnName,@FKName
        END
    CLOSE table_cur1
    DEALLOCATE table_cur1
    --------------------------------DROP AND recreate CLUSTERED pk
    IF  EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(@PKTableName) AND name = @PKName)
    BEGIN
        SET @sql = 'ALTER TABLE '+@PKTableName+' DROP CONSTRAINT '+ @PKName
        PRINT @sql
        EXEC(@sql)
    END
    SET @sql = 'ALTER TABLE '+@PKTableName +' ADD  CONSTRAINT '+@PKName+' PRIMARY KEY CLUSTERED ('+@PKcolumnName+' ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]'
    PRINT(@sql)
    EXEC(@sql)
    --------------------------------Enable FK constraints on FK tables.
    DECLARE table_cur2 CURSOR  FOR
        SELECT  * FROM FKAgainstTableList
    OPEN table_cur2
    FETCH NEXT FROM table_cur2 INTO @table,@FKColumnName,@FKName
    WHILE   @@FETCH_STATUS = 0
        BEGIN
            SET @sql = 'ALTER TABLE '+@table+' WITH NOCHECK ADD  CONSTRAINT  '+ @FKName+' FOREIGN KEY(['+@FKColumnName+'])
            REFERENCES ['+@PKTableName+'] (['+@PKcolumnName+'])'
            PRINT(@sql)
            EXEC(@sql)

--          SET @sql = 'ALTER TABLE '+@table+' CHECK CONSTRAINT  '+@FKName  -- this created untrusted foreign keys
            SET @sql = 'ALTER TABLE '+@table+' WITH CHECK CHECK CONSTRAINT  '+@FKName  -- assumes all FK relations are in order
            PRINT(@sql)
            EXEC(@sql)

            FETCH NEXT FROM table_cur2 INTO @table,@FKColumnName,@FKName

             END
    CLOSE table_cur2
    DEALLOCATE table_cur2
    DROP TABLE FKAgainstTableList
END  -- end while loop

SET NOCOUNT OFF