Sql-server – What Columns to create Indexes on

indexprimary-keysql server

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

Best Answer

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.

Related Question