Recover a non-clustered PC to a cluster PC?
For a Products table with NK-clustered PK and no clustered index at all: is it possible to use ALTER INDEX..REBUILD
(or any simple method) to change the NON CLUSTERED
PK to CLUSTERED
one? How? I would be happy to avoid DROP as there are other constrained tables that prevent me from dropping PK products.
EDIT:
I found that using TOAD for SQL Server (I have a free community version) I can right click on the table, select Alter Table, change Clustered from False to True and create a scary but working - script where it creates a new table, copies data, blobs and recreates / renames whatever is needed.
However, I still hope there is a recommended way to do this with a dedicated tool
source to share
You can create a clustered index using the parameter DROP_EXISTING = ON
. You can also create the primary key this way if you prefer to do so.
BEGIN TRAN
SELECT *
INTO o
FROM sys.objects
CREATE UNIQUE NONCLUSTERED INDEX sjhfg ON o (object_ID)
CREATE UNIQUE CLUSTERED INDEX sjhfg ON o (object_ID) WITH (DROP_EXISTING = ON)
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('o')
ROLLBACK
source to share
Most of the articles / answers indicate that this is not possible. However, as pointed out in @usr's answer, it is possible to convert a NonClustered index to a clustered index. Since we are dealing with a PC, this requires:
- first discards all FKs that refer to this PK
- Ignoring the PK constraint part and just recreating the PK index part as UNIQUE CLUSTERED
- Finally, recreate all FKs that were reset in step 1
If it is a very large table and you cannot afford the time it takes to lock that table, or if you cannot afford the possibility of orphaned data for operations that occur on linked tables during this operation, you should do to reduce the time it takes to switch:
- Create a duplicate table named [CurrentName_new] with a clustered PK
- Data transfer
- Drop and re-create the FK on dependent tables to point to the new table
- Rename the current table to [CurrentName_old]
- Rename the new table to [CurrentName]
source to share