MS SQL Server Concepts and Programming Question:

Download Job Interview Questions and Answers PDF

How To Defragment Table Indexes?

MS SQL Server Interview Question
MS SQL Server Interview Question

Answer:

When a table index is fragmented to a certain percentage, you need to defragment the index to maintain its performance level. There are 3 ways to defragment:

1. "ALTER INDEX index_name ON table_name REORGANIZE" - Defragmenting the specified index performed in online mode. No locks applied on affected table. Used for indexes with a small fragmentation percentage.

2. "ALTER INDEX index_name ON table_name REBUILD" - Defragmenting the specified index performed in offline mode by default. It can be performed in online mode. Used for indexes with a large fragmentation percentage.

3. "CREATE INDEX ... WITH (DROP_EXISTING = ON)" - Re-creating the specified index. The definition of the index can be changed.

Download MS SQL Server Interview Questions And Answers PDF

Previous QuestionNext Question
What Causes Index Fragmentation?How To Defragment Indexes with ALTER INDEX ... REORGANIZE?