vendredi 26 décembre 2014

Will a non-clustered index on a column with covering index on all the columns of table affect the Delete statement Records)?


I am trying to delete thousands of records say 35K from a table which consists like 10 Million records.



Delete * from Table where ID=859


I have also tried bulk deletion



DECLARE @RowsDeleted INTEGER
SET @RowsDeleted = 1

WHILE (@RowsDeleted > 0)
BEGIN

DELETE top(1000) FROM tableWHERE ID=@ID
SET @RowsDeleted = @@ROWCOUNT
END


i would like to ask


1) Generally How much time it would take to delete such no of records ? 2) If there is any Index on that table then is it gonna affect the delete statement ? Note: I have a non-clustered index on a column ID. Does this have any affect on performance of if i delete records based on that ID 3) are there any common bottle-necks that may cause delete to take long time than it should take ?





Aucun commentaire:

Enregistrer un commentaire