In This Article, You Will Know About SQL CREATE INDEX.
Before moving ahead, let’s know a bit about the SQL DEFAULT Constraint.
Table of Contents
CREATE INDEX
The CREATE INDEX statement is used to create indexes in tables.
Indexes are just a way to speed up users’ search or queries in the whole database. SQL indexes help to get data from the database more quickly than other ways.
However, updating indexes in the table takes more time than updating without indexes. Therefore it is recommended that only create indexes on columns that will easily find out queries within the database.
Creates an index on a table. CREATE INDEX allows duplicate values.
Syntax
CREATE INDEX index_name
ON table_name (column1, column2, ...);
CREATE INDEX Example
Example: Use the SQL CREATE INDEX to create an index in the table.
CREATE INDEX my_index
ON Student_info (Subject);
The above SQL command adds an index named “my_index” in the “Subject” column on the table Student_info.
Create an Index on multiple columns.
Example: Use the SQL CREATE INDEX to create an index in the table.
CREATE INDEX my_index
ON Student_info (Roll_No, Subject);
The above SQL command adds an index named “my_index” in the “Roll_No” and “Subject” columns on the table Student_info.
CREATE UNIQUE INDEX
Create a unique index on a table. CREATE INDEX does not allow duplicate values.
Syntax
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);
CREATE UNIQUE INDEX Example
Example: Use the SQL CREATE UNIQUE INDEX to create an index in the table.
CREATE UNIQUE INDEX my_index
ON Student_info (Subject);
The above SQL command adds an index named “my_index” in the “Subject” column on the table Student_info.
Create a unique index on multiple columns.
Example: Use the SQL CREATE UNIQUE INDEX to create an index in the table.
CREATE INDEX my_index
ON Student_info (Roll_No, Subject);
The above SQL command adds an index named “my_index” in the “Roll_No” and “Subject” columns on the table Student_info.
DROP INDEX Statement
The DROP INDEX statement is used to delete an index in the table.
SQL Server
Syntax
DROP INDEX table_name.index_name;
MySQL Server
Syntax
ALTER TABLE table_name
DROP INDEX index_name;
If you find anything incorrect in the above-discussed topic and have further questions, please comment below.
Connect on:
