0

I have these legacy table definitions (from here):

CREATE TABLE [dbo].[JobItems] (
   [ItemId]            UNIQUEIDENTIFIER NOT NULL,
   -- lots of other columns
   CONSTRAINT [PrimaryKey_GUID_HERE] PRIMARY KEY NONCLUSTERED ([ItemId] ASC)
);

CREATE UNIQUE CLUSTERED INDEX [JobItemsIndex]
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?

3

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.

| improve this answer | |
-1

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')
Begin
    Drop Index [JobItemsIndex] On [dbo].[JobItems]
End
If Exists(Select 1 From sys.indexes Where object_id = OBJECT_ID('dbo.JobItems') and name = 'PrimaryKey_GUID_HERE')
Begin
    Alter Table [dbo].[JobItems] Drop Constraint [PrimaryKey_GUID_HERE]
End

Alter Table [dbo].[JobItems] Add Constraint [PrimaryKey_GUID_HERE] PRIMARY KEY CLUSTERED ([ItemId] ASC)
| improve this answer | |
  • I thought so at first, too. But they are in Azure and the CI cannot be dropped. – ypercubeᵀᴹ Aug 10 '15 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? – Julien Vavasseur Aug 10 '15 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/… – ypercubeᵀᴹ Aug 10 '15 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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