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,478
  • 7
  • 20
Gareth Webb
  • 41
  • 4
  • 4
    NO you can keep CI as to what it is. What you suggested for CI is ideal scenario and I have seen lot of deviations but things work juts fine. Why would you add another Identity column and increase I/O and memory for the table – Shanky Sep 11 '15 at 11:55
  • Just confused conflicting views all over the internet on what a CI should be on. Won't inserts/deletes in the middle of the table cause fragmentation? – Gareth Webb Sep 11 '15 at 12:00
  • 2
    Yes that would but Logical fragmentation , which occurs when order of key mismatches order of data pages at leaf level, is more nasty as the one you are saying. You should mainly focus on logical fragmentation your case is internal fragmentation not logical or external. IMHO logical is more threatening. – Shanky Sep 11 '15 at 12:03
  • 1
    And even if you add an Identity and make it the CI, the PK (or Unique) index on `(UserID, groupID)` will still be fragmented, the same way as it is with this design (and it will be wider, because it will also include the 4 bytes for the identity column). And if you later need another index on `(GroupID, UserID)`, it will need more space than with this design. – ypercubeᵀᴹ Sep 11 '15 at 12:10
  • 3
    And in high-insert environments, clustering on an identity column can actually be worse, because all of those processes are fighting to write to the same page. Hitting this bottleneck isn't overly common, but it happens enough that you can't just say "every table should have an identity column and it should be the clustering key." There are always exceptions. – Aaron Bertrand Sep 11 '15 at 12:55
  • 1
    How many rows are in this table? Ostensibly, since this table relates users to groups, there aren't really going to be a huge number of inserts or updates, correct? If so, there will really be no benefits to changing the table design, as the others have said. – Hannah Vernon Sep 29 '15 at 02:32

0 Answers0