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.