3

I have a table defined as follows, but I'm not sure how to cluster the table:

CREATE TABLE [Security].[UserGroups] (
  [UserId]  INT NOT NULL,
  [GroupId] INT NOT NULL,
  [IsAdmin] BIT NOT NULL,

  CONSTRAINT PK_UserGroups PRIMARY KEY CLUSTERED (UserId, GroupId), 

  CONSTRAINT FK_UserGroups_User FOREIGN KEY (UserId) REFERENCES [Security].Users(Id),
  CONSTRAINT FK_UserGroups_Group FOREIGN KEY (GroupId) REFERENCES [Security].Groups](Id),

  INDEX [IX_UserGroups_GroupId] (GroupId)
  );

As far as I am aware a clustered index should be:

  • unique
  • static
  • narrow
  • ever-increasing

My Primary Key meets the first three of those, but isn't ever-increasing.

  • Is it worth changing this table?

I could add an extra IDENTITY column for the CI (and if so, do I make it the PK too), or not cluster it at all and use a heap. Both of which seem bad, but so does the leaving the CI as is.

Oreo
  • 1,566
  • 1
  • 10
  • 22

0 Answers0