INDEX in SQL

Board Infinity -

Introduction

The database search engine can use indexes, which are specialised lookup tables, to speed up data retrieval. An index is simply a pointer to information in a table. An index in a database resembles a book's back matter index quite a bit.

For instance, if you want to find all the pages in a book that address a particular subject, you first go in the index, which alphabetically lists all the subjects, and then you find one or more specific page numbers.

While an index speeds up SELECT queries and WHERE clauses, it slows down UPDATE and INSERT statements that input data. The data is unaffected by the creation or deletion of indexes.

The CREATE INDEX statement is used to create an index. This statement enables you to name the index, specify the table and which column(s) to index, as well as whether you want the index to be in ascending or descending order.

In the same way that the UNIQUE constraint forbids duplicate entries in the column or combination of columns on which an index exists, indexes can also be unique.

Creating an index

Syntax:

CREATE INDEX index
ON TABLE column;

where the name of the index is the name provided to it, TABLE is the name of the table on which it was constructed, and column is the name of the column to which it was applied.

For multiple columns

CREATE INDEX index
 ON TABLE (column1, column2,.....);

Unique indexes

Unique indexes are used to maintain the accuracy of the data in the database and to speed up performance; they prevent the entry of multiple values into the table.

CREATE UNIQUE INDEX index
 ON TABLE column;

Composite indexes

An index on two or more table columns makes up a composite index. It has the following basic syntax.

CREATE INDEX index_name
on table_name (column1, column2);

The column(s) that you may utilise frequently as filter criteria in a query's WHERE clause should be taken into consideration when deciding whether to establish a single-column index or a composite index.

If only one column is required, a single-column index should be utilised. The composite index would be the ideal option if there were two or more columns that were commonly utilised as filters in the WHERE clause.

write your code here: Coding Playground

When should indexes be avoided?

Despite the fact that indexes are meant to improve a database's efficiency, there are some situations in which they ought to be avoided.

The following recommendations show when it's time to reevaluate using an index.

  • Small tables shouldn't use indexes.
  • tables that frequently see big batch inserts or changes.
  • Columns with a large amount of NULL values should not have indexes on them.
  • It is best not to index frequently changed columns.

Removing an Index

Remove an index from the data dictionary by using the DROP INDEX command.

Syntax:

DROP INDEX index;

write your code here: Coding Playground

Altering an Index

To modify an existing table’s index by rebuilding, or reorganizing the index.

ALTER INDEX Indexname
ON TableName REBUILD;

Confirming Indexes

You can verify the uniqueness of the various indexes that are present in a specific table that have been provided by the user or the server.

Syntax:

select * from USER_INDEXES;

It will show you all the indexes present in the server, in which you can locate your own tables too.

Renaming an index

Any database index can be renamed using the system stored function sp_rename.

Syntax:

EXEC sp_rename 
  index_name, 
  new_index_name, 
  N'INDEX'; 

write your code here: Coding Playground

Conclusion

A particular table called the Index in SQL is used to expedite searching for data in database tables. Additionally, it routinely retrieves a sizable amount of data from the tables. The hard disc must have a dedicated place for the INDEX.