SQL INDEX

What is SQL INDEX?

An index is a schema item. It's used by the server to use a pointer to speed up row retrieval. It can reduce disc I/O (input/output) by using a rapid path access approach to locate data quickly. Select queries and where clauses run quicker with an index, however data entry in update and insert statements takes longer. When indexes are added or removed, they have no effect on the data. In this post, we'll look at how to create, delete, and use the INDEX in the database. The database search engine can use indexes as customised lookup tables to speed up data retrieval. An index is nothing more than a pointer to data in a table. An index in a database is similar to the index in the back of a book.

You go to the index first, which lists all the topics alphabetically, and then you go to one or more specific page numbers if you want to reference all pages in a book that discuss a given topic. While indexes improve the performance of SELECT queries and WHERE clauses, they slow down data input in UPDATE and INSERT statements. When indexes are added or removed, they have no effect on the data. To construct an index, use the CREATE INDEX statement. It lets you give the index a name, specify the table and column(s) to index, and specify whether the index should be sorted ascending or descending. Indexes, like the UNIQUE constraint, can prevent duplicate entries in the column or collection of columns on which the index is built.

Synatx for How to Create a INDEX

CREATE INDEX index
ON TABLE column;

TABLE is the name of the table on which the index is built, and column is the name of the column to which it is applied, where the index is given a name.

CREATE INDEX index
ON TABLE column1, coumn2, ...;

Unique indexes are used to maintain the database's data integrity as well as to increase performance by preventing duplicate entries in the table.

CREATE UNIQUE INDEX index
ON TABLE column;

When should indexes be created?

   A column can be filled with a wide range of values.
   🚀A There aren't a lot of null values in a column.
   🚀One or more columns are normally used together in a when clause or a join condition.

When should indexes be avoided?

Despite the fact that indexes are intended to improve database efficiency, there are times when they should be avoided.

When is it OK to use an index?
The following suggestions may assist you in making your decision.
   🚀Indexes should be avoided on small tables.
   🚀Tables that see a lot of large batch inserts or changes.
   🚀Indexes should not be utilised on columns with a substantial number of NULL values.
   🚀For regularly updated columns, indexing should be avoided.

How to Remove, Alter, Confirm and Rename an Index?

DROP INDEX  index;

To remove an index from the data dictionary, use the DROP INDEX command. To drop an index, you must be the index owner or have the DROP ANY INDEX ability.

ALTER INDEX  index
ON  table_name REBUILD;

You can alter an existing table's index by rebuilding or reorganising it.

SELECT * 
FROM  USER_INDEXES;

You can check the uniqueness of the different indexes in a table that the user or the server has provided.
It will show you all of the server's indexes, as well as your own tables if you have them.

EXEC sp_rename  
   index_name,  
   new_index_name,  
   N'INDEX';

The system stored function sp rename can be used to rename any index in the database.