Best index for this scenario

Question

There is a table in our database

tblCustomText(
  ParentID int NOT NULL,
  CustomFieldID int NOT NULL,
  Value char(50) NOT NULL, 
  Log_CreateDate datetime NOT NULL
)

(ParentID+CustomFieldID) is unique.

Read operations are like: SELECT Value WHERE ParenID=@p1 AND CustomFieldID = @p2 (sometimes instead of WHERE, JOIN is used)

Write operations: 85% is INSERT, 13% is UPDATE, 2% DELETE. (By ParentID+CustomFieldID)

Now, I'd like to know what indexes should be defined for this table? (It contains ~6M rows)

Answer 1

Seems like the following would be sufficient:

CREATE TABLE dbo.tblCustomText
(
  ParentID int NOT NULL,
  CustomFieldID int NOT NULL,
  Value char(50) NOT NULL, 
  Log_CreateDate datetime NOT NULL,
  PRIMARY KEY CLUSTERED (ParentID, CustomFieldID)
);

You may also find later that you are querying by date, so you may want a non-clustered index on the Log_CreatedDate column.

Answer 2

Since ParentID + CustomFieldID are unique AND NOT NULL, I would suggest you make a clustered primary key on the combination of those two fields.