MS SQL Training (32 Blogs) Become a Certified Professional
AWS Global Infrastructure


Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (15 Blogs)
  • MySQL (30 Blogs)
  • SQL Essentials Training and Certification (4 Blogs)

What is an index in SQL?

Last updated on Mar 18,2024 7.2K Views

Aayushi Johari
A technophile with a passion for unraveling the intricate tapestry of the... A technophile with a passion for unraveling the intricate tapestry of the tech world. I've spent over a decade exploring the fascinating world of...
33 / 37 Blog from Introduction to SQL

Whenever you read a book, you must have noticed the index in it! Indexes in SQL are the same. Indexes are simply special lookup tables. The database search engine uses these tables to speed up the data retrieval process. There are a lot of provisions under SQL while working with indexes. You can select queries, update, create indexes using specific commands
The agenda for this topic is as follows:Index in SQL-Edureka

    1. What is an index in SQL?
    2. How to create an index?
    3. How to drop an index?
    4. How to alter an index?
    5. Types of indexes in SQL
    6. When should you avoid indexes?

What is an index in SQL?

As I mentioned initially, indexes are special lookup tables. SQL indexes are used in relational databases to retrieve data. An index acts as a pointer to data in a specific table. It works in the same way as the indexes you see in any book you read. You can perform a lot of functions by using them. Learn more about SQL Server and its framework from the SQL Training.

How to create an index?

In order to create an index, follow the given syntax.

CREATE INDEX index_name ON table_name;

Now there are several indexes that can be created. Have a look.

Single-column indexIt is created on only one table column.CREATE INDEX index_name ON table_name (column_name);
Composite indexesThese indexes are used on two or more columns of a table.CREATE INDEX index_name;
on table_name (column_name);
Unique indexesThese are used for Data integrity. No duplicate values are allowed to be inserted into the table.CREATE UNIQUE INDEX index_name
On table_name (column_name);

The next segment is on how to drop an index in SQL!

How to Drop an index?

SQL DROP command is used in order to drop and index. The syntax is as follows:

DROP INDEX index_name;

Now let us see how to alter an index using SQL command!

How to alter an index?

Use the following syntax to alter an index.

ALTER INDEX index_name on object_name;

There are three terms that can be used while altering and index.

  • Rebuild: The computer index will be recreated using the rebuild option.
  • Recognize: The leaf nodes of the b-tree will be re organized using the recognize option.
  • Disable: The disable option will disable the index.

There are different types of indexes in SQL. Let us study them!

Types of index in SQL

There are two types of indexes in SQL. 

  • Clustered index

  • Non-clustered index

Clustered index

  1. Clustered index helps in arranging the rows physically in the memory.

  2. The search for the range of values is fast.

  3. This is maintained by using a b tree Data structure leaf node, the nodes of the indexes point to the table directly.

Non clustered index

  1. Non clustered index will not arrange rows physical in the memory in sorted order.

  2. The maximum number of indexes that can be created is 999. 

  3. This index is also maintained by a b-tree data structure but the leaf nodes of the index do not point to the table data directly.

Moving ahead with SQL indexes, let’s see when to avoid them.

When should you avoid indexes?

  • Indexes should not be used on small tables.

  • You should avoid the columns that have a high number of NULL values.

  • Indexes should be avoided in the tables that possess a large number of update or insert operations.

This is all about indexes in SQL. I hope the content explained added value to your knowledge. Keep reading, keep exploring!

If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of this “Index in SQL” article and we will get back to you.

Upcoming Batches For SQL Certification Course
Course NameDateDetails
SQL Certification Course

Class Starts on 15th June,2024

15th June

SAT&SUN (Weekend Batch)
View Details

Join the discussion

Browse Categories

webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.

image not found!
image not found!

What is an index in SQL?