ALTER TABLE dbo.TableA ADD CONSTRAINT [PK_composite] PRIMARY KEY CLUSTERED ( col1 ASC, col2 ASC, col3 ASC, col4 ASC, )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO
Sample table data
col1 col2 col3 col4 col5 col6 col7 1 1 2 2 3 4 5 5 5
TableA contains over 1.2 billion rows . we have to delete all values older than say col2 <7700 ( which is more than half of the rows in the table) . On average to delete 5 million rows it takes one hour. is there a way this can be made run faster . The only index is on the table is clustered index and the table is in a database an AG .
showing here part of the code where col2=5 matches 5 million rows and the delete takes 1 hour and 4 minutes
DELETE TOP (@BATCHSIZE) FROM dbo.TableA WHERE col2=5 --example