/****************************************************
AboutSQLServer.com blog
Written by Dmitri Korotkevitch

Purging Data
2011-01-09
*****************************************************/
set nocount on
go

create table dbo.Data
(
	Id int not null identity(1,1),
	DateCreated datetime not null,
	Placeholder char(100) not null
		constraint Def_Data_Placeholder
		default 'a',
			
	constraint PK_Data
	primary key clustered(ID)
)
go

declare
	@I int
	,@MinDate datetime
	
select @I = 0, @MinDate = '2010-01-01'

begin tran
	while @I < 1000000
	begin
		insert into dbo.Data(DateCreated)
		values(DATEADD(second,30 * @I,@MinDate))
		select @I += 1
	end
commit
go

create index IDX_Data_DateCreated
on dbo.Data(DateCreated)
go

begin tran
	delete from dbo.Data
	where DateCreated < '2010-05-01'	
rollback
go

drop index IDX_Data_DateCreated on dbo.Data
go

declare
	@MaxRecId int
	,@WorkId int
	,@MinRecId int

select @MaxRecId = MIN(Id) 
from dbo.Data with (nolock)
where DateCreated >= '2010-05-01'

select @MinRecId = MIN(ID) 
from dbo.Data

-- outer begin tran just for test purposes only
begin tran
	while @MinRecId < @MaxRecId
	begin
		select @WorkId = @MinRecId + 50000
		if @WorkId > @MaxRecId
		select @WorkId = @MaxRecId
			
		begin tran
			delete from dbo.Data
			where ID between @MinRecId and @WorkId
		commit
		select @MinRecId = @WorkId
	end
rollback
go


drop index IDX_Data_DateCreated on dbo.Data
go

declare
	@MaxRecId int
	,@WorkId int
	,@MinRecId int
	
select @MaxRecId = MIN(Id) 
from dbo.Data with (nolock)
where DateCreated >= '2010-05-01'

select @MinRecId = MIN(ID) 
from dbo.Data


-- outer begin tran just for test purposes only
begin tran
	while @MinRecId < @MaxRecId
	begin
		select @WorkId = @MinRecId + 50000
		if @WorkId > @MaxRecId
		select @WorkId = @MaxRecId
			
		begin tran
			delete from dbo.Data
			where ID between @MinRecId and @WorkId
		commit
		select @MinRecId = @WorkId
	end
rollback
go
