What Columns to create Indexes on?

Question

I have a table , which contains upto 20 columns. The data is selected, inserted, and deleted by 3 columns: Username, StartDate, and EndDate.

These 3 columns are NOT unique.

The table is a standalone table - no joins to other tables.

Do I create a clustered index on these 3 columns? Or do I create a UniqueID that autoincrements as a PK, and then create a non-clustered index on those 3 columns? Or is a Primary Key required at all?

SELECT StartDate, EndDate, Username, Value1, Value2, Value3, Value4, Value5, Value6
FROM  myTbl
WHERE Username = @Username
AND StartDate = @StartDate
AND EndDate = @EndDate;


DELETE FROM  myTbl
WHERE Username = @Username
AND StartDate = @StartDate
AND EndDate = @EndDate

Answer 1

From the limited information given, a suitable non-unique clustered index is:

CREATE CLUSTERED INDEX CX_dbo_myTbl_StartDate_EndDate_Username
ON dbo.myTbl (StartDate, EndDate, UserName);

That said, there are all sorts of other considerations that might mean a different choice would be optimal for the workload as a whole. If you improve your question with additional detail, it will be possible to give a better answer.

Also, in general, a PRIMARY KEY (clustered or not) is highly desirable. It is a fundamental principle of relational design that each row (tuple) of a table should be capable of being uniquely identified.

Answer 2

One clustered index is not enough, because it will do ClusteredIndexScan(logical operator in Exeution Plan)

So, if your version of SQL Server is above 2008 then this may help:

CREATE INDEX Idx_Name ON myTbl(UserName, StartDate, EndDate) 
  INCLUDING(Value1, Value2, Value3, Value4, Value5, Value6) 

this will allow Query Optimizer to use IndexSeek

If your version is 2005 or lower, yu can create covered index with all columns (but only if their total length is less than 900 bytes) like this:

CREATE INDEX Idx_Name ON 
myTbl(UserName, StartDate, EndDate, Value1, Value2, Value3, Value4, Value5, Value6)