Menu
i know this has been discussed already like anything but could not find a reliable answer i can go with.
Suppose i have a table with 10 billion records and need to delete records with identity column in where clause. which option should i go with?
option 1: disable the index which will save overhead to rearrange the index after deletion but will take longer time to search which row needs to deleted.
option 2: will not do anything with index which will locate the row very fast but rearrange the index can take some time.
i am more inclined towards the 2 option but want to see what will experts say? :)
859
Disabling a clustered index on a view or a nonclustered index physically deletes the index data. Disabling a clustered index on a table prevents access to the data; the data still remains in the table, but is unavailable for data manipulation language (DML) operations until the index is dropped or rebuilt.
When a clustered index is disabled, its data rows cannot be accessed. This means that there will be no insertion process possible.
The answer is yes, Indexes do help with the DELETE statement. Let us see a simple example of the SELECT command and its execution plan.
In theory, we would expect the best delete performance for a table without any indexes—as it is for insert . If there is no index, however, the database must read the full table to find the rows to be deleted. That means deleting the row would be fast but finding would be very slow.
Suppose i have a table with 10 billion records and need to delete records with identity column in where clause. which option should i go with?
If you're deleting (or inserting) more than 10% of the table (1 billion records), you should remove all of the non-clustering indexes, delete the records, then rebuild the non-clustering indexes.
If you're deleting less than 10% of the table, leave the indexes in place.
You're free to do performance testing to see if the 10% rule applies to your SQL Server database engine.
51
"Option 1" is not an option anyway.
Disabling the clustered index will make the whole table inaccessible and you would not be able to run a DELETE on the table anyway. It would fail with
The query processor is unable to produce a plan because the index ... is disabled.
Example code generating this error.
CREATE TABLE T(X INT CONSTRAINT PK PRIMARY KEY CLUSTERED, Y INT);
ALTER INDEX PK ON T DISABLE
DELETE FROM T
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
