I have a table that is heavily used which inserts and deletes rows frequently during the day. This is just a placeholder to identify what the system should work on, so it just has a large key. It's currently a heap with a NC Primary Key, but I feel that it should be clustered to prevent duplication of data. The people that created this, thought that it would create too much contention and fragmentation, but in my opinion this would just be replicated on the index.
Here's the current structure of the table (with the names changed). Am I right? What or how should I test to give a certain answer?
CREATE TABLE SomeWorkTable(
MaintTableID int NOT NULL,
SomeCode char(1) NOT NULL ,
MilestoneID int NOT NULL DEFAULT 0,
Loaded bit NULL,
RuleID int NOT NULL DEFAULT 1,
SomeOtherID int NOT NULL DEFAULT 0,
);GO
ALTER TABLE SomeWorkTable ADD CONSTRAINT PK_SomeWorkTable PRIMARY KEY NONCLUSTERED(MaintTableID, SomeCode, MilestoneID, RuleID, SomeOtherID);
GO
Not sure what you mean by duplication of data, unless you mean that the nonclustered index is duplicating all the data from the table. My question would be how are the inserts and deletes being done? Is this table like a queue, first in first out? If so, I would make the index a clustered index and do it such that new data is inserted at the end and old data pulled from the front, just like a queue.