sql - Remove records by clustered or non-clustered index -

i have table (let's errorlog)

create table [dbo].[errorlog] (     [id] [int] identity(1,1) not null,     [created] [datetime] not null,     [message] [varchar](max) not null,      constraint [pk_errorlog]          primary key clustered ([id] asc) ) 

i want remove records older 3 months.

i have non-clustered index on created column (ascending).

i not sure 1 of these better (seem take same time).

query #1:

delete errorlog created <= dateadd(month, - 3, getdate()) 

query #2:

declare @id int  select @id = max(l.id) errorlog l l.created <= dateadd(month, - 3, getdate())  delete errorlog id <= @id 

once know maximum clustered key want delete faster use key. question whether worth selecting key first using date. right decision depends on size of table , portion of data need delete. smaller table , smaller number of records deletion more efficient should first option (query #1). however, if number of records delete large enough, non-clustered index on date column ignored , sql server start scanning base table. in such case second option (query #2) might more optimal. , there other factors consider.

i have solved similar issue (deleting 600 million (2/3) old records 1.5tb table) , have decided second approach in end. there several reasons it, main follows.

the table had available new inserts while old records being deleted. so, not delete records in 1 monstrous delete statement rather had use several smaller batched in order avoid lock escalation table level. smaller batches kept transaction log size in reasonable limits. furthermore, had 1 hour long maintenance window each day , not possible delete required records within 1 day.

with above mentioned in mind fastest solution me select maximum id needed delete according date column , start deleting beginning of clustered index far selected id 1 batch after other (delete top(@batchsize) errorlog with(paglock) id <= @mymaxid). used paglock hint in order increase batch size without escalating lock table level. deleted several batches each day in end.