Italian Trulli

Index In SQL With Example - CREATE , DROP , Advantage , Disadvantage

Test Tribe

 SQL INDEX Statement 

In SQL, an index is a database structure that improves the speed of data retrieval operations on database tables. It allows for faster querying and sorting of data by creating a separate data structure that references the values in a specific column or set of columns. 

Indexes work by creating a copy of the indexed column(s) and storing it in a separate structure, such as a B-tree or a hash table. This structure organizes the data in a way that facilitates efficient searching and retrieval. 

CREATE INDEX - How To Create an Index on a Table?

Indexes are used to retrieve data from the database more quickly than otherwise.

Below the syntax for creating an index on the table is given.

CREATE INDEX index_name ON table_name (column1, column2, ...);

Below the syntax for creating a Unique index on the table is given.

CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

Example of Create Index
For instance, let's say you have a table called Customers with columns CustomerID, FirstName, and LastName, and you want to create an index on the LastName column. You would execute the following SQL statement:

CREATE INDEX idx_Customers_LastName ON Customers (LastName);

Example of Creating a Unique Index

Here We create a table "College" and Then add a Unique Index on the College code.

CREATE TABLE Colleges ( college_id INT PRIMARY KEY, college_code VARCHAR(20) NOT NULL, college_name VARCHAR(50) ); \

Table With Unique Index 

CREATE UNIQUE INDEX college_index ON Colleges(college_code);

Here, the SQL command creates a unique index named college_index on the Colleges table using the college_code column.

DROP INDEX - How To Remove Index in a Table?
In SQL Drop Index Statements are used to delete the index in the table.

Syntax of DROP INDEX 

Drop Index Syntax for MS Access
The basic Sytanx of the Drop index for MS access is given below.

DROP INDEX index_name ON table_name;
Example of Drop Index - MS Access

DROP INDEX Colleges.college_index;

Drop Index Syntax for SQL Server
The basic Sytanx of the Drop index for SQL Server is given below.

DROP INDEX table_name.index_name;

Drop Index Syntax for Oracle
The basic Sytanx of the Drop index for Oracle is given below.

DROP INDEX index_name;

Drop Index Syntax for My SQL
The basic Sytanx of the Drop index for My SQL is given below.

ALTER TABLE table_name DROP INDEX index_name;

Types of Index in SQL
There are different types of indexes in SQL 
  • Clustered Index
  • Non-Clustered Index
  • Unique Index
  • Filtered Index
  • Columstore Index
  • Hash Index

Why do we Use Index?
When We Use Index?
When Should Indexes Be Avoided?
Advantage of Index
Disadvantage of the index.

Hope !!! The above Tutorial on SQL INDEX is helpful for you...

QA acharya

Index In SQL With Example

Post a Comment