Whenever you read a book, you must have noticed the index in it! Indexes in SQL are the same. Indexes are simply special lookup tables. The database search engine uses these tables to speed up the data retrieval process. There are a lot of provisions under SQL while working with indexes. You can select queries, update, create indexes using specific commands.
The agenda for this topic is as follows:
As I mentioned initially, indexes are special lookup tables. SQL indexes are used in relational databases to retrieve data. An index acts as a pointer to data in a specific table. It works in the same way as the indexes you see in any book you read. You can perform a lot of functions by using them. Learn more about SQL Server and its framework from the SQL Training.
How to create an index?
In order to create an index, follow the given syntax.
CREATE INDEX index_name ON table_name;
Now there are several indexes that can be created. Have a look.
| Index | Definition | Syntax |
| Single-column index | It is created on only one table column. | CREATE INDEX index_name ON table_name (column_name); |
| Composite indexes | These indexes are used on two or more columns of a table. | CREATE INDEX index_name; on table_name (column_name); |
| Unique indexes | These are used for Data integrity. No duplicate values are allowed to be inserted into the table. | CREATE UNIQUE INDEX index_name On table_name (column_name); |
The next segment is on how to drop an index in SQL!
How to Drop an index?
SQL DROP command is used in order to drop and index. The syntax is as follows:
DROP INDEX index_name;
Now let us see how to alter an index using SQL command!
How to alter an index?
Use the following syntax to alter an index.
ALTER INDEX index_name on object_name;
There are three terms that can be used while altering and index.
- Rebuild: The computer index will be recreated using the rebuild option.
- Recognize: The leaf nodes of the b-tree will be re organized using the recognize option.
- Disable: The disable option will disable the index.
There are different types of indexes in SQL. Let us study them!
Types of index in SQL
There are two types of indexes in SQL.
Clustered index
Non-clustered index
Clustered index
Clustered index helps in arranging the rows physically in the memory.
The search for the range of values is fast.
This is maintained by using a b tree Data structure leaf node, the nodes of the indexes point to the table directly.
Non clustered index
Non clustered index will not arrange rows physical in the memory in sorted order.
The maximum number of indexes that can be created is 999.
This index is also maintained by a b-tree data structure but the leaf nodes of the index do not point to the table data directly.
Moving ahead with SQL indexes, let’s see when to avoid them.
When should you avoid indexes?
Indexes should not be used on small tables.
You should avoid the columns that have a high number of NULL values.
Indexes should be avoided in the tables that possess a large number of update or insert operations.
This is all about indexes in SQL. I hope the content explained added value to your knowledge. Keep reading, keep exploring!
If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.
Got a question for us? Please mention it in the comments section of this “Index in SQL” article and we will get back to you.