Copyright

SQL: CREATE Index

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

Like an index in a reference book, the SQL Index points to a spot in the database. This lesson will cover the syntax of using CREATE INDEX to create an index, and provide examples.

What is an Index?

An SQL index is a special table that points to data inside a database table. For example, if you create an index for a band name in a music database, you can use that index in future queries to speed up the search.

In SQL, the index speeds up your SELECT queries and their subsequent WHERE statements. However, it slows down updates (UPDATE AND INSERT). You can create or drop indexes without affecting the underlying data.

Use the CREATE INDEX statement to create an index. This lets you name the index and define which table/columns are indexed. It also allows sorting the index in descending or ascending order. The index also allows the UNIQUE attribute, preventing any duplicate column information.

Considerations

The following should be kept in mind when deciding whether to index a table. It's best not to use an index on small tables. But, a table with only a few columns but 300,000 rows should probably be indexed! Also, if columns have a large number of NULL values in them, avoid indexing. It really bogs down the system when it has to process so many NULL values.

The basic syntax for creating an index is as follows:


CREATE INDEX name_of_index
ON name_of_table;


The previous syntax is used to create an index of the ENTIRE table.

If you want to create an index only of one column instead of the whole table, the following syntax can be used:


CREATE INDEX name_of_index
ON name_of_table (name_of_column);


If you want to add more columns, just separate those column names by commas.

Unique Index

A unique index ensures that duplicate values aren't entered. Use the UNIQUE keyword to specify that the index is unique:


CREATE UNIQUE INDEX name_of_index
ON name_of_table (column1, column2, ...);


Be careful with the UNIQUE clause! Every column that is specified will require unique values. This could be problematic if you set unique for both an artist name and an album title. There are probably many albums with the same title. The same could be said for artist name. However, a way around this is to include the artist's country in the name. Example: Cool Kids [US]; Cool Kids [JPN].

Examples

Now that we've covered the basics, let's look at some examples. We'll be using a fictional database that stores music information. The following columns are in the tblAlbum table:

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support