AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (15 Blogs)
  • MySQL (55 Blogs)
  • SQL Essentials Training and Certification (9 Blogs)
SEE MORE

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-widget-banner-bg

What is an index in SQL?

Published on Oct 31,2019 62 Views
Aayushi Johari
A technophile who likes writing about different technologies and spreading knowledge. A technophile who likes writing about different technologies and spreading knowledge.
33 / 37 Blog from Introduction to SQL

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assessment report and video recording

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.

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.

IndexDefinitionSyntax
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.

Comments
0 Comments

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
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.