2

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)

  • 1
    I do question char(50) for the value column. Are these values always going to be exactly 50 characters? If not, I strongly recommend varchar and verifying that 50 is the right upper limit. I'd also verify that you will never need to support Unicode in this column - I would probably use nvarchar to be safe. On 2008 R2 and up, data compression will help you negate the extra costs of storing non-Unicode data in nvarchar columns. – Aaron Bertrand Feb 17 '12 at 13:39
7

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.

  • +1 for the inclusion of the DATETIME field in a nonclustered index, and having a more complete answer than mine. – Matt M Feb 17 '12 at 13:24
  • How about making an (extra) index for (ParentID, CustomFieldID,Value). Does it make sense? (It certainly improves read speed). – jaraics Feb 19 '12 at 12:56
  • @jaraics What query would that satisfy better? You know that the clustered index includes both Value and Log_CreateDate, right? – Aaron Bertrand Feb 19 '12 at 13:51
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.