1

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
1

2 Answers 2

6

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.

-3

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)
2
  • I've mentioned that only in 2008 SQLServer is possible to use INCLUDE clause. And i didn't write "CLUSTERED" with "INCLUDE". "CREATE INDEX" command creates nonclstered index by default. Please, mind what you're about Nov 18, 2014 at 10:55
  • Ok, "INCLUDE", i've got it. How about clustered index scan? Nov 18, 2014 at 11:04

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.