CCNC/CCNC Module 5/Designing and creating tables/Table Design and Layout/Adding Indexes

From WikiEducator
Jump to: navigation, search
Tutorial.png Table Design and Layout 

Field Design and Properties | Adding Columns | Adding Indexes | Database Schemas | Self Assessment | Summary & FAQs

Adding indexes to a Table

Icon objectives.jpg


Upon completion of this tutorial the learner will be able to:
  • Add an new index to a table
  • Rename an index

Adding indexes to a table

Figure 1. Index design

As mentioned in the previous section it is important to add indexes to tables as they increase data retrieval performance. After adding new fields to the PhoneNumbers table it is beneficial to add indexes to certain fields.

Icon activity.jpg
Reviewing the modified PhoneNumbers table;
  • Can you identify which fields could benefit from indexes?
  • What would be the rationale for applying each index?

Adding the index

Figure 2. Adding Indexes

Once the Index design dialog is available the new index button should be pressed to create a new index. When you open the index designer you may notice indexes are already present. These are indexes created when you set the primary and foreign keys.


Naming the index

Figure 3. Renaming the index
Figure 4. Renamed

Once you have created the new index the field needs to be chosen and the sort order set. A meaningful name should be given to the index where the name of the field being indexed is a part of the indexes name. This will be helpful in identifying the index. You can create many indexes and they will all be saved once you press the 'Save' button on the index design dialog.

Test your knowledge

Icon qmark.gif



1. Indexes are also created when you add primary and foreign keys?

Absolutely Correct!
Incorrect, primary and foreign keys are essentially indexes. Click here to review the topic..

2. Which of the following would be good as parts of names for indexes?

(a) _idx_
Almost correct, all these suffixes could be good in naming indexes.
(b) _pk_
Almost correct, all these suffixes could be good in naming indexes.
(c) _fk_
Almost correct, all these suffixes could be good in naming indexes.
(d) all of the above would be good suffixes
Absolutely Correct!
(e) None of the above
Incorrect, click here to review the topic.

Your score is 0 / 0