Quick one today:

Sometimes, in our lab and performance testing environments, we end up in a situation where we need to remove and re-add indexes while running tests on different scenarios. In these situations, rather than DROPping and re-CREATing indexes, there is a simpler way to manage these things. The underlying activities that happen under the hood are the same in either case, but the big difference is in the amount of time a developer/DBA spends doing these actions.

DISABLE & ENABLE (REBUILD) gives a simpler and cleaner way to carry out the same operation. Let’s look at an example:

REBUILD is the keyword that results in EABLEing the index. Under the hood, it just rebuilds the index using the same definition. Since we did not drop the index, the definition is still present in Sql Server. So, it just rebuilds it. Sometimes using the rebuild options available in WITH clause we could configure the rebuild operation to fit our needs. See example below.

Some points to  keep in mind:

• In Sql Server databases Clustered Index is a special index. The index is where the entire table data is stored. So, if we disable clustered index, the entire data becomes inaccessible. Data is not lost, but it becomes inaccessible to users. Just REBUILD it to gain access to data.
• Disabling clustered index on indexed view, deletes the view. So take extra caution with indexed views.

In some cases, when you want to deal with all the indexes on a table in one shot, use the below example.