Index in SQL SERVER Database

Indexes in SQL Server are database objects that help improve the performance of queries by providing quick access to data in a table. We can say that 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.

An index is essentially a data structure that maps the values in one or more columns of a table to their physical locations on disk. When a query is executed that includes the indexed columns in its search criteria, the index can be used to locate the rows that match the criteria much more quickly than if the database had to scan the entire table. Indexes can be created on one or more columns in a table or view, and they can be either clustered on two or more columns or non-clustered. A clustered index determines the physical order of the data in a table, while a non-clustered index is a separate structure that points to the location of the data.

Type of index in sql server

Ø  Clustered Index

Ø  No clustered Index

Ø  Unique Index

Ø  Column Store Index

Ø  Index with Included Columns Index

Ø  Index on Computed Columns Index

Ø  Filtered Index

Ø  Spatial Index

Ø  XML Index

Ø  Full-text Index

Ø  Indexed View

Ø  Partition Indexes

Advantage of index

Ø  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.

Ø  Indexes can optimize the performance of ORDER BY clauses, as it can provide sorted data directly.

Ø  Indexes can speed up queries that use aggregate functions (e.g., SUM, COUNT) by limiting the number of rows scanned.

Disadvantages of Indexes

Ø  Indexes take more disc space.

Ø  INSERT, UPDATE, and DELETE are all slowed by indexes, but UPDATE is speed up if the WHERE condition has an indexed field. Since the indexes must be modified with each process, INSERT, UPDATE, and DELETE become slower.

Ø  Managing indexes requires careful planning and maintenance, such as regularly rebuilding or reorganizing them to prevent fragmentation.

Ø  Having too many indexes can lead to performance degradation, as the database engine has to evaluate multiple indexes to determine the best one to use.

Ø  Index maintenance during updates can lead to increased locking, which may affect concurrency.

We Should Avoided to create index on below cases

Ø  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