CREATE and DROP INDEX Statement in SQL

The CREATE INDEX statement will create indexes in tables.  Indexes are used for data procurement from the databases faster. The users cannot see the indexes, they are running in the background of queries, used to speed up searches/queries. 

 Create an index on a table :

Syntax:

CREATE INDEX indexname

ON tablename (columnname1, columnname2, …);

Create a unique index on a table:

Syntax:

CREATE UNIQUE INDEX indexname

ON tablename (columnname1, columnname2, …);

Let’s take an example of a Customer Table where we will insert the ‘City” column on “Country: in the SQL statement.

CREATE:

CREATE TABLE Customer(
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    LastName VARCHAR(50),
    Country VARCHAR(50),
    Age int(2),
  Phone int(10)
);
-- Insert some sample data into the Customers table
INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES (1, 'Shubham', 'Thakur', 'India','23','xxxxxxxxxx'),
       (2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),
       (3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),
       (4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),
       (5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');

Output:

Query:

CREATE INDEX City
ON Customer(Country);

This code does not produce any output directly visible to the user. When an index is created in a database, the database management system updates its internal data structures to include the new index, but this process does not produce any output that is visible to the user.

See the description in the SQL Table.

Query:

SELECT * FROM sqlite_master WHERE 
type = 'index' AND tbl_name = 'Customer';

Output:

To Create an Index on Multiple Columns 

CREATE INDEX idx_Customer
_Country_Age ON Customer (Country, Age);

DROP Index

The DROP INDEX statement could be used to remove an index from any table. 

Syntax:

DROP INDEX tablename.indexname;

Query:

DROP INDEX City;

Output: