Indexes are special lookup tables that need to be used by the database search engine to speed up data retrieval. An index is simply a reference to data in a table. A database index is similar to the index in the back of a journal. It cannot be viewed by the users and just used to speed up the database access.
For example, in order to reference all pages in a book that address a particular subject, you go to the index first, which lists all the topics alphabetically, and then you go to one or more specific page numbers.
Indexes prevent duplicate entries in the column or combination of columns on which it is created. Since SQL indexes are primarily a performance tool, they are most useful when a database grows in size. The clustered index is one of the most popular types of indexes supported by SQL server. This type of index is automatically created with a primary key.
Two primary methods to use the index in SQL are:
- CREATE INDEX Command
- DROP INDEX Command
The CREATE INDEX Command
This command is used for generating the table index in SQL using the build index expression. At the time of creating the table, it will allow you to accept the duplicated data on to the table. Indexing makes columns faster to query by creating pointers to where data is stored within a database.
Assume you need to locate a specific piece of data in a huge database. To retrieve this data from the database, the machine will search every row until it finds it. This query will take a long time to run if the data you're searching for is towards the end.
CREATE INDEX name_of_Index ON name_Of_Table(Attribute1, Attribute2,...);
create INDEX ind_1 on studentdet(regno,dept);
We can generate sorted lists using indexes in SQL instead of having to create new sorted tables, which would take up a lot of storage space.
INDEX - is the keyword to specify the index creation.
Ind_1 is the index name.
Studentdet is the table name.
In the above example, ind_1 has been created with the fields of regno and dept from the table of studentdet.
Just one table column is used to construct a one-way-column index. To create the index for a one-way column from the table, use the below syntax.
CREATE INDEX name_of_Index ON name_Of_Table(Attribute1);
create INDEX ind_reg on studentdet(regno);
Here ind_reg is the index created with the attribute regno.
According to Index in SQL, unique indexes are used for data integrity as well as accuracy. A unique index prevents duplicate values from being entered into the table.
CREATE UNIQUE INDEX name_index on tables_na (Attribute_name);
CREATE UNIQUE INDEX sidindex on Student_DB (L_Name);
The table name is Student_DB with the field L_Name. The sidindex is the unique index that has been created for the table Student_DB.
According to Index in SQL, an index on two or more columns of a table is known as a composite index. It may create the same index with a different number of columns.
CREATE INDEX Name_index ON db_table(column1,Column2…….);
CREATE INDEX stu_index ON Student_DB(S_ID,L_Name);
The sorted lists can be generated using indexes rather than creating new sorted tables, which lot of storage space. When a column is identified as primary key or special, the implicit indexes are generated automatically. On such columns, we don't need to build an index.
CREATE INDEX IN_Name ON TN_Name(Attribute1..);
CREATE INDEX ind_stu_det ON Student_DB(Regno);
The table student_db has attributes like regno, name, dept, and so on. The column or attribute may identify the primary key constraints or other unique constraints that indexes are generated automatically without any interference.
The DROP INDEX Command
The SQL DROP command can be used to remove an index. When lowering an index, be cautious because performance may either slow down or improve. This command is generally used to delete the index on the specified data table.
DROP INDEX name_of_index on Table_name;
DROP INDEX index_name;
When Should Indexes Be Avoided
While indexes are designed to improve the efficiency of a database, there are occasions where they should be avoided, for instance:
- On small tables
- Tables that receive a lot of big batch updates or inserts
- Columns that have large numbers of null values
- Columns that are frequently manipulated
- Where columns are manipulated regularly.
- When the attribute or field is updated frequently
- The attributes aren't often used as a query state
Advantages of Index in SQL
- Speed up select query
- Helps to make a row special or without duplicates (primary, unique)
- We can check against broad string values if the index is set to full-text index and find a word from a sentence.
Disadvantages of Index in SQL
SQL Server is mainly used to store records in a database schema in the form of pages. Mostly, the page size of entries is approximately 8KB. Every database has at least two files: one for the data, which has the default file type of .mdf, and one for the log, which has the default file type of .ldf. There are one or more pages for each table in the database. SQL Server uses a special collection of pages called IAM (Index Allocation Map) to keep track of those pages.
In this article, you learned about indexes with syntax and examples. To gain expertise in SQL programming language, you can join our Simplilearn's Business Analytics for Strategic Decision Making with IIT Roorkee course. Start learning for free today's most in-demand skills. Courses emphasized the development of good core skills for future career advancement.
Have any questions for us? Leave them in the comments section of this article and our experts will get back to you on them, as soon as possible!