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 table is given.

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

Below the syntax for creating a Unique index on 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 Create Unique Index-

Here We create a table "College" and Then add 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 !!!Above Tutorial on SQL INDEX helpful for you...

QA acharya

Tags: INDEX in SQL, CREATE Index in SQL , DROP index IN SQL 

Post a Comment