What is an index in SQL?

Question

What is an index in SQL? Can you explain or reference to understand clearly?

Where should I use an index?

Answer 1

Indexes are used to retrieve the data from the database in a shorter span of time. Users can only speed up searches or queries, they can’t see the indexes.

SQL CREATE INDEX Statement: This CREATE INDEX statement is used to create indexes in tables.

• Syntax of CREATE INDEX: In this duplicate value is allowed, this creates an index on the table. 

CREATE INDEX index_name

ON Table_Name (columnA, columnB, ...);

• Syntax of CREATE UNIQUE INDEX: This creates a unique index on the table. Duplicate values are not allowed in this:

CREATE UNIQUE INDEX index_name

ON Table_Name (columnA, columnB, ...);

Note: In different databases, the syntax varies for creating indexes.

Example of CREATE INDEX

The SQL statement given below creates an index named "idx_lastname" on the "Last_Name" column in the "Students" table:

CREATE INDEX idx_lastname

ON Students (Last_Name);

You can list the column names within the parentheses, separated by commas, if you want to create an index on a combination of columns.

CREATE INDEX idx_sname

ON Students (LastName, FirstName);