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
