工作需要筆記

-- Case 2 Primary Key Non-clustered Index
USE TempDB
GO
-- Create table
CREATE TABLE TestTable
(ID INT NOT NULL PRIMARY KEY NONCLUSTERED,
Col1 INT NOT NULL)
GO
-- Check Indexes
SELECT OBJECT_NAME(OBJECT_ID) TableObject,
[name] IndexName,
[Type_Desc]
FROM sys.indexes
WHERE OBJECT_NAME(OBJECT_ID) = 'TestTable'
GO
-- Clean up
DROP TABLE TestTable
GO

http://blog.sqlauthority.com/2013/02/10/sql-server-primary-key-and-nonclustered-index-in-simple-words/

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

DROPINDEX TableX.IX_TableX_FieldB

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

ALTERTABLE TableX
    ADDCONSTRAINT UQ_TableX UNIQUE(FieldA)

3) Drop the PRIMARY KEY

ALTERTABLE TableX
    DROPCONSTRAINT PK_TableX

4) Recreate the PRIMARY KEY as CLUSTERED

ALTERTABLE TableX
   ADDCONSTRAINT PK_TableX PRIMARYKEYCLUSTERED(FieldA)

5) Drop the temporary UNIQUE constraint

ALTERTABLE TableX
   DROPCONSTRAINT UQ_TableX

6) Add the IX_TableX_FieldB back on as NONCLUSTERED

CREATENONCLUSTEREDINDEX IX_TableX_FieldB ON TableX(FieldB)

http://stackoverflow.com/questions/2297355/change-a-primary-key-from-nonclustered-to-clustered