Does creating an index and then deleting it return the database to the pre-index creation state?

Question

I'm testing some indexes and want to do a bit of A-B testing on which indexes perform better (primarily the order of the fields in the index and additional fields specified).

I have a backup of the database I'm testing on, but that backup takes a long time to restore. I'd prefer if I could just create index foo, gather performance metrics, then delete foo to return the DB to the pre-index state and create index bar. Is this how creation of indexes work, or does the creation of the index alter the table in someway that deleting the index can't undo?

Answer 1

Longer

Index changes (creates, rebuilds, drops, etc) on a table do invalidate any relevant plans in the plan cache and cause a new plan to be recompiled the next time the related query is executed.

It is possible for a different plan to be generated than what was originally there, on a database whose data has changed between when the original plan was cached and the new one was recompiled.

Practically speaking, since you're just doing A/B testing on a copy of the database that I'm assuming isn't actively changing otherwise, then yes, Erik is succinctly spot on. It's highly likely that dropping the index will result in the same original execution plan to be cached for your queries on their subsequent runs - meaning your database will effectively be returned to its previous state.

Answer 2

Alternative

For this sort of testing, I like to use Database Snapshots.

In a testing environment, it can be useful when repeatedly running a test protocol for the database to contain identical data at the start of each round of testing. Before running the first round, an application developer or tester can create a database snapshot on the test database. After each test run, the database can be quickly returned to its prior state by reverting the database snapshot.

See the linked documentation for exact syntax and things to be aware of.

Prior to SQL Server 2016 Service Pack 1, this feature required Enterprise Edition. Since then, it has been available in every edition.

Answer 3

short

Dropping the index is sufficient for resetting the table to its prior state. If you disable the index, statistics created for it may still be used for cardinality estimation.

Answer 4

There are some small pitfalls that you will have to pay attention to, because it depends on the indexes you are creating and/or dropping.

Along Come Clustered Indexes

There are tips out there that suggest to create clustered indexes on heaps (unsorted data) to allow them to be sorted (due to the creation of the clustered index) and to then remove the clustered index to then have a heap again. Rebuilding the table after dropping the index will free up some unused space due to records being deleted in pages.

• Heaps (Tables without Clustered Indexes) (Microsoft | Learn).

  To rebuild a heap to reclaim wasted space:

  Create a clustered index on the heap, and then drop that clustered index. Use the ALTER TABLE ... REBUILD command to rebuild the heap.

  There is a small warning in the above article stating that creating and dropping a clustered index on a heap where other indexes are present will require a total rebuild of all non-clusterd indexes. CAUTION!

Creating or dropping clustered indexes requires rewriting the entire table. If the table has nonclustered indexes, all the nonclustered indexes must all be recreated whenever the clustered index is changed. Therefore, changing from a heap to a clustered index structure or back can take a lot of time and require disk space for reordering data in tempdb.

You could have a major performance impact if you created a clustered-index on a heap containing non-clustered indexes and then removing the clustered index again.

• SQL Server Clustered Tables vs Heap Tables (MSSQLTips)

  A clustered table provides a few benefits over a heap such as controlling how the data is sorted and stored, the ability to use the index to find rows quickly and the ability to reorganize the data by rebuilding the clustered index. Depending on INSERT, UPDATE and DELETE activity against your tables, your physical data can become very fragmented. This fragmentation can lead to wasted space in your database, because of partly full pages as well as the need to read more pages in order to satisfy the query. So what can be done? The primary issue that we want to address is the fragmentation that occurs with normal database activity. Depending on whether your table has a clustered index or not will determine if you can easily address the fragmentation problem down to the physical data level. Because a heap or a clustered index determines the physical storage of your table data, there can only be one of these per table. So a table can either have one heap or one clustered index.

• Removing a Clustered Index from a Heap (Table) will not return the now sorted data (due to the creation of the clustered index) into an unsorted state.

Answering Your Question

Does creating an index and then deleting it return the database to the pre-index creation state?

Not in all cases, as stated above. Adding and removing a clustered index to a heap may have a major impact on performance and may make a big difference on how your database reacts (execution plan-wise) to these changes.

Example Using db<>fiddle

Here is a short example that shows that creating a clustered-index on a heap and then dropping the clustered-index, will not return the data into its previous state. Even after a ALTER TABLE ... REBUILD...

db<>fiddle example

create table [heap] 
  (
  id int,
  first_name nvarchar(10),
  family_name nvarchar(20)
  )
;

insert into [heap] (id, first_name, family_name) 
values
(4,'John', 'Nautilus'),
(2,'Erik', 'Dreadnaught'),
(1, 'J.D.', 'Edwards')
;

3 rows affected

select * from [heap];

id	first_name	family_name
4	John	Nautilus
2	Erik	Dreadnaught
1	J.D.	Edwards

create clustered index [cix_heap_family_name_PERF_20230321] on [heap] (family_name);

select * from [heap]; -- no longer a heap actually, but a clustered table

id	first_name	family_name
2	Erik	Dreadnaught
1	J.D.	Edwards
4	John	Nautilus

drop index [cix_heap_family_name_PERF_20230321] on [heap];

select * from heap; -- now a sorted heap

id	first_name	family_name
2	Erik	Dreadnaught
1	J.D.	Edwards
4	John	Nautilus

alter table [heap] rebuild;

select * from [heap];

id	first_name	family_name
2	Erik	Dreadnaught
1	J.D.	Edwards
4	John	Nautilus

fiddle

The data has been altered!

Possible Solutions

1. Backups -> Restores to development environment
2. Database Snapshots in development environment

...and please don't create indexes in the production environment for quick testing.