| Author |
Topic |
|
sixsigma1978
Starting Member
25 Posts |
 Posted - 2009-10-30 : 20:29:32
|
Am trying to understand the difference between when to add a clustered vs non clustered indexIn the following schema - Department has one or more employees, employee has a unique id and a unique SSN number. An employee can have one or more project. Can someone suggest which columns would be good candidates for Clustered indexes and which for non clustered?http://img3.imageshack.us/img3/5315/relation.jpg |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-10-30 : 20:42:58
|
| You can only have one clustered index on each table. You can have many non-clustered indexes though. Typically your clustered index would be on a column or columns that are sequentially increasing or decreasing. This would be true of an identity column. A DBA where I work likes the clustered index to be the Date/Time column + the identity column. Which non-clustered indexes you choose would depend upon the queries.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
sixsigma1978
Starting Member
25 Posts |
Posted - 2009-10-30 : 20:51:46
|
quote:
Originally posted by tkizer
You can only have one clustered index on each table. You can have many non-clustered indexes though. Typically your clustered index would be on a column or columns that are sequentially increasing or decreasing. This would be true of an identity column. A DBA where I work likes the clustered index to be the Date/Time column + the identity column. Which non-clustered indexes you choose would depend upon the queries.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong."
Thanksfor the quick reply- In the scenario above - was trying to figue out whether to put non-clustered index on BOTH the columns onmapping table Employee_Proj and a clusterd index on the SSN column in Employee (since its always unique and potentially searchable - instead of the ID). IS this a sound design? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-10-30 : 22:58:30
|
| Your database design is flawed. You shouldn't have Emp_ID in the Department table. Employee table shouldn't have Emp_Proj_ID as you can figure out what projects employees are assigned to by looking at the Employee_Proj table. I don't see a point to the ID column in the Employee_Proj table. A linking table usually won't have an identity column, but rather just a composite PK.SSN is a good candidate for the clustered index, but I would make it non-clustered to avoid fragmentation.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
sixsigma1978
Starting Member
25 Posts |
Posted - 2009-10-30 : 23:06:40
|
| You're right - I dropped the emp_id in department as well.I have two linking tables now Department_Employee (dept_id, emp_id) and Employee_Project (emp_id and project_id)Department <-> Department_Employee <-> Employee <->Employee_Project <-> ProjectSo If I put NonClustered indexes on each column in the linking tables (Department_Employee and Employee_Project) - and leave the default clustered indexes on the primary keys on the Primary tables( Department, Employee and Project) - do I achieve the best-case scenario? I feel that would be true since all the joins are indexed (non clustered) and they point to the primary keys (clustered) hence speeding up queries!! Please correct me if I'm wrong? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sixsigma1978
Starting Member
25 Posts |
Posted - 2009-10-30 : 23:15:53
|
quote:
Originally posted by tkizer
It sounds right, but I'd need to see an updated ERD. Indexes really depend on the queries though and not necessarily on the database design.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blogWell - I expect queries like :Get all employes that dont have a project (spanning two tables)or Get all departments whose employees are not on any project (spanning all the tables)Since these queries would necessarily go through the joins - i'm hoping the indexing design is ok.."Let's begin with the premise that everything you've done up until this point is wrong."
 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sixsigma1978
Starting Member
25 Posts |
Posted - 2009-10-30 : 23:22:17
|
Sounds good Thanks!! You've been a ton of help!!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Clustered vs Non Clustered Index