I have to delete data from 7 large tables. Adding non-clustered indexes speed up the process but I was told that setting up indexes occupy a lot of space. So, do you think if its a best practice to create the non-clustered index and drop them after the delete operation is complete? My set up is SQL Development Server 2008 R2.
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.