Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
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
add comment

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer
    
+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
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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