SQL Server index is used mainly to retrieve data from the database tables quickly, by seeking the requested records in the table index directly, rather than scanning the overall table records. The database transactional tables are changing very frequently, and these changes will be reflected in the related table indexes. Due to these changes, the index becomes fragmented over time, with its pages stored in an unordered manner.

SQL Server Index is the key solution for most of the performance issues that the T-SQL queries suffer from. It can be used to speed up the data retrieval process from your database tables, by seeking for the requested data in the index itself, rather than scanning the overall table rows.

In this article, we will discuss the difference between rebuilding the index using Online and Offline modes.

SQL Server index can be considered as a double-edged sword. When the index is designed and maintained well, it can be used to speed up the data retrieval operations from the database tables, by providing swift access to the table rows, with no need to perform a full scan on the table’s data. On the other hand, the index may cause performance degradation on your system and slow down the data insertion and modification operations, when this index is badly designed.

The purpose behind creating SQL indexes on database tables is to enhance the performance of the queries that read data from the base table by speeding up the data retrieval processes. But these transactional tables are not static and changing very frequently over time. These changes that are performed on the database table include adding new records, modifying or deleting the existing records. As these changes should be replicated to the related table indexes, the table index will become fragmented over time.