I have these legacy table definitions (from here):

CREATE TABLE [dbo].[JobItems] (
   -- lots of other columns

ON [dbo].[JobItems]([ItemId] ASC);

which effectively yields two identical indexes. I want to get rid of one of them to reduce insertion time and free up disk space.

The table is stored in a production SQL Azure database, so I cannot rebuild the table and I cannot drop the clustered index (SQL Azure requires a clustered index for every table).

My requirement is I keep all the data integrity guarantees which were provided by the original definition and get rid of the non-clustered index. It looks like having a NOT NULL constraint and a unique index gets me the same guarantees as a PK constraint. So the PK constraint is redundant and I can drop it and the underlying index.

Can I just drop the PK constraint here without expecting anything to break?


2 Answers 2


which effectively yields two identical indexes.

The indexes are not identical. The CI includes all of the other columns, while the PK has only the ItemId key column on the leaf. So the Clustered Index is probably 100x the size of the non-clustered index.

For instance COUNT(*) and paging queries will be significantly more expensive with only the clustered index, as they will have to use table scans.


Right now, both your unique index and PK uniquely identify each row. This is redundant. The index can be remove and the cluster can be move into the PK. However it will fail if there are foreign keys on ItemId.

First look for foreign key(s):

Select OBJECT_NAME(constraint_object_id) From sys.foreign_key_columns where referenced_object_id = OBJECT_ID('dbo.JobItems') and COL_NAME(referenced_object_id, referenced_column_id) = 'ItemId'

Then just drop both of them (Index + PK) and recreate a clustered PK:

If Exists(Select 1 From sys.indexes Where object_id = OBJECT_ID('dbo.JobItems') and name = 'JobItemsIndex')
    Drop Index [JobItemsIndex] On [dbo].[JobItems]
If Exists(Select 1 From sys.indexes Where object_id = OBJECT_ID('dbo.JobItems') and name = 'PrimaryKey_GUID_HERE')
    Alter Table [dbo].[JobItems] Drop Constraint [PrimaryKey_GUID_HERE]

Alter Table [dbo].[JobItems] Add Constraint [PrimaryKey_GUID_HERE] PRIMARY KEY CLUSTERED ([ItemId] ASC)
  • I thought so at first, too. But they are in Azure and the CI cannot be dropped. Aug 10, 2015 at 14:45
  • Can you Create a new table B with a clustered PK, copy data from A to B and use sp_reneme A, B? Aug 10, 2015 at 14:51
  • Yes, that's the most efficient and easy way I think, if you want to drop the clustered CI and make the PK clustered. Suggested here. too: social.msdn.microsoft.com/Forums/azure/en-US/… Aug 10, 2015 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.