Indexes are special lookup tables that need to be used by the database search engine to speed up data retrieval. An index is simply a reference to data in a table. A database index is similar to the index in the back of a journal. It cannot be viewed by the users and just used to speed up the database access.

For example, in order to reference all pages in a book that address a particular subject, you go to the index first, which lists all the topics alphabetically, and then you go to one or more specific page numbers.

Indexes prevent duplicate entries in the column or combination of columns on which it is created. Since SQL indexes are primarily a performance tool, they are most useful when a database grows in size. The clustered index is one of the most popular types of indexes supported by SQL server. This type of index is automatically created with a primary key.

Two primary methods to use the index in SQL are:

  1.     CREATE INDEX Command 
  2.     DROP INDEXCommand 

The CREATE INDEX Command

This command is used for generating the table index in SQL using the build index expression. At the time of creating the table, it will allow you to accept the duplicated data on to the table. Indexing makes columns faster to query by creating pointers to where data is stored within a database.

Assume you need to locate a specific piece of data in a huge database. To retrieve this data from the database, the machine will search every row until it finds it. This query will take a long time to run if the data you're searching for is towards the end.

Syntax:

CREATE INDEX name_of_Index ON name_Of_Table(Attribute1, Attribute2,...);

Code:

create INDEX ind_1 on studentdet(regno,dept);

Explanation:

We can generate sorted lists using indexes in SQL instead of having to create new sorted tables, which would take up a lot of storage space.

INDEX - is the keyword to specify the index creation.

Ind_1 is the index name.

Studentdet is the table name.

In the above example, ind_1 has been created with the fields of regno and dept from the table of studentdet.

Output:

IndexinSQL_1

FREE Course: Introduction to Data Analytics

Mastery to Data Analytics Basics is a Click Away!Start Learning
FREE Course: Introduction to Data Analytics

Single-Column Indexes

Just one table column is used to construct a one-way-column index. To create the index for a one-way column from the table, use the below syntax.

Syntax:

CREATE INDEX name_of_Index ON name_Of_Table(Attribute1);

Code:

create INDEX ind_reg on studentdet(regno);

Here ind_reg is the index created with the attribute regno.

Output:

IndexinSQL_2

Unique Indexes

According to Index in SQL, unique indexes are used for data integrity as well as accuracy. A unique index prevents duplicate values from being entered into the table.

Syntax:

CREATE UNIQUE INDEX name_index on tables_na (Attribute_name);

Code:

CREATE UNIQUE INDEX sidindex  on Student_DB (L_Name);

The table name is Student_DB with the field L_Name. The sidindex is the unique index that has been created for the table Student_DB.

Output:

IndexinSQL_3.

Composite Index

According to Index in SQL, an index on two or more columns of a table is known as a composite index. It may create the same index with a different number of columns.

Syntax:

CREATE INDEX Name_index ON db_table(column1,Column2…….);

Code:

CREATE INDEX stu_index ON Student_DB(S_ID,L_Name);

Output:

IndexinSQL_4

Implicit Indexes

The sorted lists can be generated using indexes rather than creating new sorted tables, which lot of storage space. When a column is identified as primary key or special, the implicit indexes are generated automatically. On such columns, we don't need to build an index.

Syntax:

CREATE INDEX IN_Name ON TN_Name(Attribute1..);

Code:

CREATE INDEX ind_stu_det ON Student_DB(Regno);

The table student_db has attributes like regno, name, dept, and so on. The column or attribute may identify the primary key constraints or other unique constraints that indexes are generated automatically without any interference.

Output:

IndexinSQL_5

The DROP INDEX Command

The SQL DROP command can be used to remove an index. When lowering an index, be cautious because performance may either slow down or improve. This command is generally used to delete the index on the specified data table.

Syntax:

DROP INDEX name_of_index on Table_name;

Code:

DROP INDEX index_name;

Output:

IndexinSQL_6 

When Should Indexes Be Avoided

While indexes are designed to improve the efficiency of a database, there are occasions where they should be avoided, for instance:

  • On small tables
  • Tables that receive a lot of big batch updates or inserts
  • Columns that have large numbers of null values
  • Columns that are frequently manipulated
  • Where columns are manipulated regularly.
  • When the attribute or field is updated frequently
  •  The attributes aren't often used as a query state

Post Graduate Program in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
Post Graduate Program in Business Analysis

Advantages of Index in SQL

  • Speed up select query
  • Helps to make a row special or without duplicates (primary, unique)
  • We can check against broad string values if the index is set to full-text index and find a word from a sentence.

Disadvantages of Index in SQL

  • Indexes take more disc space.
  • INSERT, UPDATE, and DELETE are all slowed by indexes, but UPDATE is speed up if the WHERE condition has an indexed field. Since the indexes must be modified with each process, INSERT, UPDATE, and DELETE become slower.
Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Certification Program. Enroll now!

Conclusion

SQL Server is mainly used to store records in a database schema in the form of pages. Mostly, the page size of entries is approximately 8KB. Every database has at least two files: one for the data, which has the default file type of .mdf, and one for the log, which has the default file type of .ldf. There are one or more pages for each table in the database. SQL Server uses a special collection of pages called IAM (Index Allocation Map) to keep track of those pages.

In this article, you learned about indexes with syntax and examples. To gain expertise in SQL programming language, you can join our Simplilearn's SQL Certification Training Course. Start learning for free today's most in-demand skills. Courses emphasized the development of good core skills for future career advancement. Explore free courses here. You can find free guides on various career paths, wages, interview tips, and more.

Have any questions for us? Leave them in the comments section of this article and our experts will get back to you on them, as soon as possible!

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.