SQL Essentials Training & Certification
- 5k Enrolled Learners
- Self Paced
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.
In order to create an index, follow the given syntax.
CREATE INDEX index_name ON table_name;
|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!
DROP INDEX index_name;
Now let us see how to alter an index using SQL command!
Use the following syntax to alter an index.
ALTER INDEX index_name on object_name;
There are different types of indexes in SQL. Let us study them!
There are two types of indexes in SQL.
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 will not arrange rows physical in the memory in sorted order.
The maximum number of indexes that can be created is 999.
Moving ahead with SQL indexes, let’s see when to avoid them.
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.