Identifying Fields for Clustered Indexes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Identifying Fields for Clustered Indexes

Author

Topic

JacobPressures
Posting Yak Master

112 Posts

Posted - 2005-07-26 : 13:33:41

From what I understand I should try to create a clustered index on all tables in the DB. Having an index on the primary key is not always a best practice. However, I’ve not read anything that tells me when having it on the primary key is ok.

I was also told that looking at the fields in the where clause could identify good candidates for indexes.

Below is a list of tables and the fields i'm thinking about placing an index on. For tables with no Clustered? notes, I had no ideas.

To see the entity relationship diagram, go to
http://www.geocities.com/jacobpressures/

Employees
EmpID*
FirstName Composite Clustered?
LastName Composite Clustered?
BadgeNumber Clustered?
DeptID
CenterID

Centers
CenterID*
Center

Departments
DeptID*
Department

Tests
TestID*
TestName

Categories
CategoryID*
TestID Clustered?
Category
CategoryPurpose

Resources
ResourceID*
CategoryID
ResourceTitle
FileName
FileType
LastUpdated

Learning
LearningID*
CategoryID
LearningObjective
LastUpdated

Questions
QID*
CategoryID Clustered?
Question
References
LastUpdated

Answers
AnswerID*
QID
Answer
Correct

Diagrams
DiagramID*
QID
FileName
DiagramDesc
LastUpdated

EmployeeTests
EmployeeTestID*
TestID Clustered?
EmpID Clustered?
EmpTestStartDate Clustered?
EmpTestFinishDate

Completed_Categories
CCID*
EmployeeTestID Clustered?
CategoryID
StartTime
FinishTime
Comment

Completed_Questions
CQID*
QID
CCID Clustered?
Comment

Responses
ResponseID*
CQID Clustered?
AnswerID

Smallest Tables
Centers, Departments, Tests, Categories, Resources, Diagrams will all probably be less than 100 rows.

Employees table will have no more than 300 entries.

Here are some queries from this section.
"SELECT * From Employees WHERE BadgeNumber='" & sBadge & "'"

"SELECT e.EmpID From Employees e WHERE e.BadgeNumber='" & gEmployee.BadgeNumber & "' AND e.FirstName = '" & gEmployee.FirstName & "' AND e.LastName = '" & gEmployee.LastName & "'"

I am assuming that the following queries provide no value at all in regards to indexes since they serve as lookups with no Where statement
"SELECT * From Departments"
"SELECT * From Centers"
"SELECT Department FROM Departments"
"SELECT Center FROM Centers"

Questions
I was thinking about placing a Clustered Index on First and Last Name but I wonder if BadgeNumber might be more appropriate, since it is the most often used in where and BadgeNumber is the most unique.

The other tables seem so small I wonder if an index is necessary. We only have about 11 centers, less than 30 departments, 5 tests. Does it really make sense to place an index on the small tables?

The Questions and Answers Tables
These tables are larger.
There are at present 200 questions with the potiential of getting larger. For each Question, there are at the most 4 or 5 possible answers (at least 1,000 total possible answers so far).

Since the Clustered Index should be as unique as possible, I’m debating whether the Index should be on the PK or the FK.
There are at least 20 questions per category. The FK in the Questions table is CategoryID. There can be about 100 answers for each QID, FK, in Answers Table.

What should the Clustered Index be?

Here are some sample queries.
"SELECT c.CategoryID, c.TestID, c.Category, c.CategoryPurpose, c.MaterialsNeeded, l.LearningObjective FROM Categories c LEFT JOIN Learning l ON c.CategoryID = l.CategoryID WHERE c.TestID = 1"

"SELECT CategoryID, Category FROM Categories WHERE TestID =" & intTestID(intTestCounter)

"SELECT q.QID, q.Question, a.AnswerID, a.Answer, a.Correct FROM Questions q LEFT JOIN Answers a ON q.QID = a.QID WHERE CategoryID =" & gTestCategory(giCategoryNumber).CategoryID

"SELECT q.QID, q.CategoryID, q.Question, q.References, a.AnswerID, a.Answer, a.Correct FROM Questions q LEFT JOIN Answers a ON q.QID = a.QID" ' WHERE q.CategoryID IN (" & sCategoryIDs & " )"

Based on these queries, i would probably put the clustered index on TestID in the Categories table and CategoryId in the Questions table.

The Largest Tables
The largerest tables are EmployeeTests, Completed_Categories, Completed_Questions and Responses.

They increase in size respectively.
The 200+ employees can take numerous tests with up to 10 categories apiece to complete with numerous questions.
The question here is similar to the one above. Which field is the best? PK or one of the FKs?

Here are some queries.
"SELECT * FROM EmployeeTests WHERE EmpID = " & gEmployee.EmployeeID & " AND TestID = " & gTestCategory(giCategoryNumber).TestID & " ORDER BY EmpTestStartDate ASC"

"SELECT EmployeeTestID, EmpTestFinishDate FROM EmployeeTests WHERE EmpID = " & gEmployee.EmployeeID & " AND TestID = " & intTestID(x) & " ORDER BY EmpTestFinishDate ASC"

“SELECT CQID, QID FROM Completed_Questions WHERE CCID = " & intID(x, 0)

"SELECT CQID FROM Completed_Questions WHERE CCID = " & IDHolder & " AND QID = " & gEmpCompletedCat(x).TestQuestion(y).QuestionID

"SELECT AnswerID FROM Responses WHERE CQID = " & intID2(y, 0)

"SELECT CCID, StartTime, FinishTime FROM Completed_Categories WHERE EmployeeTestID = " & IDHolder & " AND CategoryID = " & gTestCategory(giCategoryNumber).CategoryID & " ORDER BY StartTime ASC"

Thanks very much. If anyone knows of a list of guidelines for selecting Clustered Indexes, please let me know. Thanks again.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-26 : 15:01:52

You should read this article:
http://www.sql-server-performance.com/clustered_indexes.asp

CODO ERGO SUM

JacobPressures
Posting Yak Master

112 Posts

Posted - 2005-07-26 : 16:43:39

Thanks!

JacobPressures
Posting Yak Master

112 Posts

Posted - 2005-07-26 : 16:45:07

Guys I can't get any suggestions on clustered indexes? I didn't think it would be that hard.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-27 : 09:42:02

a good candidate for a clustered index is a unique growing value... like an identity...

Go with the flow & have fun! Else fight the flow

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-27 : 10:54:42

>>I was thinking about placing a Clustered Index on First and Last Name but I wonder if BadgeNumber might be more appropriate

it seems to me that clustered index on BadgeNumber is the most appropriate, and no more indexes are needed

>>The other tables seem so small I wonder if an index is necessary.

if I'm not mistaken, there is no point to create index on a table if the size this table is less then 64 Kb (because Server needs to read whole extent anyway)

>>The Largest Tables

seems like the same case as the small tables, put clusterd index on EmpID,CCID,CQID,EmployeeTestID for appropriate table.

Anyway, after creating indexes look through execution plan for each query. If there are table scans, consider another index.
Also you may use Index Tuning wizard

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-07-27 : 11:59:43

"if I'm not mistaken, there is no point to create index on a table if the size this table is less then 64 Kb (because Server needs to read whole extent anyway"

there is...
a) good DBA habits
b) indexes often support constraints re uniqueness!

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-27 : 12:09:03

>>b) indexes often support constraints re uniqueness!

I meant indexes for searching result, of cause if you need uniqueness you have no choice !

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-27 : 12:10:29

>>a) good DBA habits
what do you meen ?

JacobPressures
Posting Yak Master

112 Posts

Posted - 2005-07-28 : 09:34:00

Thanks guys for the info.