SQL Index – Example | Create, Unique, Composite Index
Stay updated with the latest technology trends while you're on the move - Join DataFlair's Telegram Channel
In this SQL tutorial, we are going to learn about SQL Index. Moreover, we will see types of SQL Index – Create Index SQL Server, SQL Unique Index, SQL Composite Index, and drop SQL Index. Moreover, we will discuss SQL Index Example.
So, let us start the SQL Index.
2. What is the SQL Index?
This statement in SQL uses to create Indexes in tables and also to retrieve the database very quickly.
Do you know about SQL Create and Drop Database
The end users cannot see the databases they can only see the results.
3. Types of Index in SQL
An Index in SQL slows down data input, with the UPDATE and the INSERT statements. Indexes may be created or dropped with no effect on the data.
When an Index is created it involves the statement create Index, this statement further permits you to call the Index and also specify the table and the columns to Index. This statement is also used to indicate or specify whether the Index in ascending or descending order.
a. SQL Create Index
The basic syntax of a create SQL Index is as follows.
CREATE INDEX index_name ON table_name;
A single-column Index is formed based on just one table column.
- The basic syntax is as follows.
CREATE INDEX index_name ON table_name (column_name);
b. SQL Unique Index
Unique Indexes are used not only for performance, however also for data integrity. It helps in the process of no duplication in tables. The basic syntax is as follows.
CREATE unique INDEX index_name on table_name (column_name);
c. Composite SQL Server Index
A composite Index is an Index on two or more columns of a table.
Have a look at SQL Expressions
- Its basic syntax is as follows.
CREATE INDEX index_name on table_name (column1, column2);
Whether to create a single-column Index or a composite index, take into consideration the column(s) that you just could use very oftentimes in a very query’s wherever clause as filter conditions.
Should there be just one column used, a single-column Index ought to be the choice. Should there be 2 or more columns that area unit oftentimes used in the wherever clause as filters, the composite index would be the best selection.
d. DROP SQL Index
An Index may be born using SQL DROP command. Care ought to be taken once dropping an Index as a result of the performance could either slow down or improve.
The syntax of Drop SQL Index
DROP INDEX index_name;
i. When should we avoid Indexes?
There are times when they need to avoid, they are –
- Indexes should not use on small tables.
- The columns which have a high number of NULL values, it should avoid there.
- Columns that often manipulate should not be indexed.
Let’s take a tour to Distinct Keyword in SQL
So, this was all in the SQL Index. Hope you like our explanation.
Hence, in this SQL Index tutorial, we studied the Indexes in SQL. Moreover, we will see the Index in SQL example. Still, if any doubt, ask in the comment tab.
See also –
Null Values in SQL