Get Started: SQL CREATE INDEX Statement

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:

3 thoughts on “Get Started: SQL CREATE INDEX Statement”

Leave a Comment