question

Before you start adding nonclustered indexes, you should make sure you have a good clustered index. A unique clustered is not required, but it is definitely preferred. In addition, it is also better if the clustered index key is a column that increases in value because the clustered index dictates the layout of the table data. For that reason, a surrogate key is often a good choice. Having said all of that, deleting data using clustered key criteria is the most efficient method. If that cannot be done in your scenario, you can make a nonclustered index as you mentioned. The storage space required for a nonclustered index is the size of the index key columns plus the size of the clustered key (because every nonclustered index must include the clustered index key as a way to access the rest of the table) plus any extra columns included in the nonclustered index. Try to use an existing clustered or nonclustered index if possible before creating a new one because every insert or delete operation must act on every index as well as any update that affects any columns in the index. I do not recommend creating the indexes just for the delete and then dropping them again unless this is a one-time event. If you need it once, you might need it again, so why make the database engine repeat the work of generating it? I hope that helps. Let us know if you have any additional questions.