Can anybody help me? I have three tables those are having aprox 50 million data.Indexes are given below- 1)First Table has one clustered index and one non clustered index. 2)Second table has only non clustered index. 3)Third table has no index Please reply for below questions : 1)What happens if I removed clustered index from first table ? 2)What happens if create clustered index on third table ? 3)How can I delete 20 millions data from all tables and what steps I need to follow? ----------------------------------------
1) If you remove the clustered index from the first table, the table has to be rebuilt as a heap. A heap is a table without a clustered index. By and large, unless you've done thorough testing to prove otherwise, most tables should have a clustered index. When you rebuild the table without a clustered index, the non-clustered index will also be rebuilt in order to change the pointers to the data for the nonclustered index from the clustered key to the row identifiers used in the new heap table. Hard to say a lot more without specifics. 2) The table will be rebuilt with different storage, logically ordering the data on the keys of the clustered index you define. Any non-clustered indexes will also be rebuilt so that they point to that same key instead of RID. Clustered, ordered, storage is preferred within SQL Server. 3) To delete this data, you can simply delete it. But, I'd be sure that the WHERE clause of the delete operation is using the clustered index key in order to ensure the fastest possible search operation. One thing you might consider is that the transaction size for deleting 20 million rows might be high. So, you can consider doing the deletes in smaller increments as a series of transactions. You might also consider dropping or disabling the non-clustered indexes during the delete operation so that they don't have to be maintained as rows are removed from the clustered index.