SQL | Indexes
Indexes are special data structures associated with tables or views that help speed up the query.
It makes the querying process fast by providing easy access to rows in data tables
An index is a schema object. It is used by the server to speed up the retrieval of rows by using a pointer. It can reduce disk I/O(input/output) by using a rapid path access method to locate data quickly. An index helps to speed up select queries and where clauses, but it slows down data input, with the update and the insert statements.
These indexes need extra space on the disk, but they allow faster search according to different frequently searched values.
CREATE New NON UNIQUE INDEX
Creates an index on a table. Duplicate values are allowed:
CREATE UNIQUE INDEX
Creates a unique index on a table. Duplicate values are not allowed:
DROP INDEX
Rname an index?
Using system stored procedure sp_rename
We can also rename it directly using UI of SQL Server Management Studio
Disable Indexes
We can disable the indexes in SQL Server by using the ALTER INDEX statement
Enable Indexes
If we want to enable the disabled index in the SQL Server, we need to rebuild it because we cannot simply enable it.
Filtering Indexes
The filtered index concept comes into play when our applications just need to query a portion of a table's rows. A filtered index is a non-clustered index with a predicate that lets us select which rows to include in the index.
Benefits of filtered indexes:
Filtered indexes can save spaces, especially when the index key columns are sparse. Columns with a lot of NULL values are considered sparse.
It also reduces maintenance costs because only a part of the data rows will be changed when the data in the related table changes, rather than all of them.
Types of SQL Server Indexes
SQL Server provides two types of indexes: clustered index and non-clustered index.
Clustered indexes
Clustered indexes use key values for sorting and storing data rows in tables or view. They are included in the index definition.
It always stores the index value in a B-tree structure where the actual data is stored in the leaf node.
Since the data rows are stored in one direction, each table can only have a single clustered index.
A table stored the rows in sorted order only when the table has a clustered index. We can refer to a clustered table as one that has a clustered index.
Non-Clustered indexes
The structure of non-clustered indexes is similar to the clustered index except that the actual data is not contained in the leaf nodes.
A non-clustered index has the non-clustered index key values, and each key-value entry contains a reference to the actual data.
All of its data rows are stored in a heap, which is an unordered structure.
Depending on how the table data is stored, it could point to a data value in the clustered index or a heap structure.
If a row locator is a pointer to the row, it is a heap structure. If a row locator is the clustered index key, it is a clustered table.
When should indexes be created?
When a column has a wide range of values
When the column does not have a large number of null values
When single or multiple columns used together in a where or join clause
When should indexes be avoided?
When a table is small
When the columns aren't used as a query condition
When the column is constantly updated
