?
Solved

Help verifying correct index/key implementation.

Posted on 2005-05-04
2
Medium Priority
?
249 Views
Last Modified: 2010-03-19
I am in the process of building a SQL database which will contain standard rolodex information.  I would like to understand if I am implementing my indices and keys correctly.

I have a Company table with a primary key on the company_id(int) column.  I have a Division table which has a  division_id(int) column and a foreign key relation to the company table on the company_id column. I created a clusterd primary key on both the company_id and division_id columns.

Company
      Company_id <PK>
      Company_name
      ...
Divisions
      Division_ID<PK>
      Company_id<PK>
      Division_name
      ...

Say I have a company with a company_id of 100 and that company has five divisions (1-5).  In the divisions table, the data would be arranged like:

Division_id            Company_id            Division_Name
1                  100                  A
2                  100                  B
3                  100                  C
4                  100                  D
5                  100                  E


The company_id and division_id together identify a unique record.  These should never be a duplicate company_id/division_id combination in either of this table.  

Is it best to create clustered primary keys on the company_id and the division_id for the division  table?  Should I also contain the division name, as that will be a frequently searched column and there should not be any duplicate division names for a given company?
0
Comment
Question by:abenage
2 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13927754
>> Is it best to create clustered primary keys on the company_id and the division_id for the division  table? <<

A clustered primary key on the company_id and division_id is a good choice to avoid duplicates on the company_id/division_id and to speed up the join to the company table.

>> Should I also contain the division name <<

Since your index is already clustered, there is no need to include the division name.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 13928070
>>? Is it best to create clustered primary keys on the company_id and the division_id for the division table? << 

You should definitely create a unique index on those columns.  Whether or not it should be clustered is a different, and more complex, question.

In general, an index should be clustered if:

*) you often select a range of those values; for example, companies 1 thru 5
*) you often sort on those values; for example, ORDER BY company_id, division_id is used a lot
*) or group on those values; for example, GROUP BY company_id [, division_id] is used fairly frequently


>> Should I also contain the division name, as that will be a frequently searched column <<

It's almost certainly best to place it in it's own index, especially if you do make the (company_id, division_id) index clustered (because the clustered index keys will always be included in every non-clustered index, so the division name index would then automatically contain the company_id and division_id).
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question