T-SQL Indexes
Indices are the unique tables that the database search engine uses to speed up the data retrieval. The index is a type of indicator for the data in the table. The index in a database is the same like an index at the end of the book.
For example, if we want to reference all pages in the book which discuss some topic, we first refer to the index, that lists all the issues according to the alphabets and then applied to one or many page numbers.
Index selection helps us to speed-up the queries that when the where clauses occur, but it slows down the data input, and contains the updates and statements. No effect is made or dropped on index data.
Creating an index includes an index create statement, which gives us the name of the index, and points to the index in ascending or descending order, to specify tables and columns in the index. Whether it is or not.
The index is unique, and similar to the UNIQUE constraint, used in the index to prevent duplicate entries or combinations of both columns where the index belongs to.
CREATE INDEX Command
Syntax:
Single-Column Indexes:
The single-index is created in the single-column of the database.
Syntax:
Example:
Unique Indexes:
The indexes are used into the data integrity but will not be used in the display form. A unique index does not allow the duplicate values to insert the tables.
Syntax:
Example:
Composite Indexes:
It is used on one or more columns of the database.
Syntax:
Example:
Then, generates a composite-column index or single-column index. We use the filter conditions of the WHERE clause.
If one column is used; there will be a choice of a single-column index. There are two or more columns, when we use WHERE clauses like the filters, the composite index will the best option.
Implicit Indexes
When the object is created, it is created by the database server. The indexes are created for primary key constraints and unique constraints into the Implicit Indexes.
DROP INDEX Command
An index is ignored by the MS SQL SERVER DROP command. It will be exercised when the index drops because the performance is improved.
Syntax:
The basic syntax is.
How to avoid Indexes?
The Indexes are generated to increase the database performance, but there are times when we need to avoid them. The use of the index will be reconsidered when the below guidelines indicate to avoid the indexes-
- Indexes cannot be used on small tables.
- Tables which contain repeated, extensive batch updates, or insert operations will not be indexed.
- Indexes has not been used on the columns which has a greater number of NULL values.
- Columns are often manipulated and cannot be indexed.