Indexes in Microsoft SQL Server
share
Indexes are the best apposite choice for quickly retrieving the records. This is nothing but cutting down the number of Disk IO. Instead of scanning the complete table for the results, we can decrease the number of IO's or page fetches using index structures such as B-Trees or Hash Indexes to retrieve the data faster. The most convenient way to consider an index is to think like a dictionary. It has words and its corresponding definitions against those words. The dictionary will have an index on "word" because when we open a dictionary and we want to fetch its corresponding word quickly, then find its definition. The dictionary generally contains just a single index - an index ordered by word. When we modify any record and change the corresponding value of an indexed column in a clustered index, the database might require moving the entire row into a separately new position to maintain the rows in the sorted order. This action is essentially turned into an update query into a DELETE followed by an INSERT, and it decreases the performance of the query. The clustered index in the table can often be available on the primary key or a foreign key column because key values usually do not modify once a record is injected into the database.
READ FULL TEXT