in Technology by
What is an Index? Explain its different types.

1 Answer

0 votes
by

A database index is a data structure that provides quick lookup of data in a column or columns of a table. It enhances the speed of operations accessing data from a database table at the cost of additional writes and memory to maintain the index data structure.

CREATE INDEX index_name 	 /* Create Index */
ON table_name (column_1, column_2);

DROP INDEX index_name; 	 /* Drop Index */

There are different types of indexes that can be created for different purposes:

  • Unique and Non-Unique Index:

    Unique indexes are indexes that help maintain data integrity by ensuring that no two rows of data in a table have identical key values. Once a unique index has been defined for a table, uniqueness is enforced whenever keys are added or changed within the index.

    CREATE UNIQUE INDEX myIndex
    ON students (enroll_no);
    

    Non-unique indexes, on the other hand, are not used to enforce constraints on the tables with which they are associated. Instead, non-unique indexes are used solely to improve query performance by maintaining a sorted order of data values that are used frequently.

  • Clustered and Non-Clustered Index:

    Clustered indexes are indexes whose order of the rows in the database correspond to the order of the rows in the index. This is why only one clustered index can exist in a given table, whereas, multiple non-clustered indexes can exist in the table.

    The only difference between clustered and non-clustered indexes is that the database manager attempts to keep the data in the database in the same order as the corresponding keys appear in the clustered index.

    Clustering index can improve the performance of most query operations because they provide a linear-access path to data stored in the database.

Related questions

+1 vote
    Explain in brief the different types of output observed of an API....
asked Oct 15, 2020 in Technology by JackTerrance
0 votes
    What is a Join? List its different types....
asked Dec 8, 2020 in Technology by JackTerrance
0 votes
    .Computer can do different types of task, it show its which characteristics? A.. Fast B.. Accuracy C.. Diligence D.. Versatility Select the correct answer from above options...
asked Dec 31, 2021 in Education by JackTerrance
0 votes
    What do you mean by Trojan and explain its types?...
asked Feb 15, 2021 in Technology by JackTerrance
0 votes
    What is Nationalism? Explain its types. Please answer the above question....
asked Aug 4, 2022 in Education by JackTerrance
0 votes
    What is Nationalism? Explain its types. Please answer the above question....
asked Aug 3, 2022 in Education by JackTerrance
0 votes
    Explain Different Types of Constructors in C#?...
asked Dec 31, 2020 in Technology by JackTerrance
0 votes
    Which of this access specifies can be used for a class so that its members can be accessed by a different class ... & Packages of Java Select the correct answer from above options...
asked Feb 23, 2022 in Education by JackTerrance
0 votes
    Which of these access specifiers can be used for a class so that its members can be accessed by a different ... & Packages of Java Select the correct answer from above options...
asked Feb 23, 2022 in Education by JackTerrance
0 votes
    explain the different types of data that are used in Access database Select the correct answer from above options...
asked Dec 16, 2021 in Education by JackTerrance
0 votes
    explain the different types of high level language for class 5 short answer Select the correct answer from above options...
asked Nov 26, 2021 in Education by JackTerrance
0 votes
    State the different types of bio-geochemical cycles and explain the importance of those cycles. Select ... proposed by,electromagnetic theory engineering physics,Science nptel...
asked Nov 7, 2021 in Education by JackTerrance
0 votes
    Which of these method is used to make a bit zero specified by the index? (a) put() (b) set() (c ... - The Collections Framework of Java Select the correct answer from above options...
asked Mar 1, 2022 in Education by JackTerrance
...