I see that you looking for very quick turn around and partitioning might work well as long as you have Enterprise Version of SQL SERVER.
just wanted to show the alternate "customized horizontally partitioned method".
As you said there is not much time to rollout bigger changes to each client; in my view once tested internally this kind of only DB changes works fastest.
assume this the current system. and we are trying to come up with design that allows easy delete of expired data w/o performance impact on existing system.
if object_id('mytesttable','u') is not null
drop table mytesttable
go
create table mytesttable
(
id int identity
,value nvarchar(max)
,lastdt date default getdate()
)
go
if object_id('pr_insert_mytesttable','p') is not null
drop procedure pr_insert_mytesttable
go
create procedure pr_insert_mytesttable
(
@value nvarchar(max)
)
as
begin
begin try
begin tran
insert into mytesttable (value)
select @value
commit tran
end try
begin catch
rollback tran
end catch
end
go
insert mytesttable (value,lastdt)
select '<my data on 10-01-2013>','10-01-2013'
union all select '<my data on 10-02-2013>','10-02-2013'
union all select '<my data on 10-03-2013>','10-03-2013'
union all select '<my data on 10-04-2013>','10-04-2013'
union all select '<my data on 10-05-2013>','10-05-2013'
union all select '<my data on 10-06-2013>','10-06-2013'
union all select '<my data on 10-07-2013>','10-07-2013'
union all select '<my data on 10-08-2013>','10-08-2013'
union all select '<my data on 10-09-2013>','10-09-2013'
union all select '<my data on 10-010-2013>','10-10-2013'
union all select '<my data on 10-011-2013>','10-11-2013'
union all select '<my data on 10-012-2013>','10-12-2013'
union all select '<my data on 10-013-2013>','10-13-2013'
union all select '<my data on 10-014-2013>','10-14-2013'
union all select '<my data on 10-015-2013>','10-15-2013'
this is your current table looks like
As you mentioned lets take the expiration threshold to 3 days. idea is create individual table that has max of 3 days of worth data. and create API layer that will figure out where to put incoming data w/o changing client side code. so end result will be 5 different table storing max of 3 days of data. API will create new tables as needed.
you will need one time existing data migration script; something like below. if you execute below script it will create new table and copy appropriate data from original table as shown in below picture.
Declare @mindt date, @maxdt date, @sql nvarchar(max)='', @tblname nvarchar(200)='', @paradef nvarchar(1000)=''
select @mindt = MIN(lastdt),@maxdt=MAX(lastdt) from mytesttable
while @mindt <= @maxdt
begin
set @mindt = DATEADD(day,3,@mindt)
set @tblname = 'mytesttable'+'_'+Replace(cast(@mindt as nvarchar(10)),'-','')
set @sql = N'
if object_id('''+@tblname+N''',''u'') is not null
drop table '+@tblname+N'
'
execute sp_executesql @sql
set @sql= N'
create table '+@tblname+N'
(
id int identity
,value nvarchar(max)
,lastdt date default getdate()
)
'
execute sp_executesql @sql
set @sql = N'
Delete from '+ @tblname+N'
;set identity_insert '+ @tblname+N' ON
insert into '+@tblname+N' (id,value,lastdt)
select id,value,lastdt
from mytesttable
where lastdt < @mindt and lastdt >= DATEADD(day,-3,@mindt)
;set identity_insert '+ @tblname+N' OFF
--select * from '+ @tblname+N'
'
set @paradef=N'@mindt date'
execute sp_executesql @sql,@paradef,@mindt
end
now what would the insert API will look like? this is a modified insert API that will look if table exists where this data fits or new tables needs to create.
if object_id('pr_insert_mytesttable_modified','p') is not null
drop procedure pr_insert_mytesttable_modified
go
create procedure pr_insert_mytesttable_modified
(
@value nvarchar(max)
)
as
begin
begin try
begin tran
declare @sql nvarchar(MAX)=''
,@chktblname nvarchar(200)=''
,@inserttblname nvarchar(200)=''
,@today date=getdate()
,@paradef nvarchar(1000)=''
set @inserttblname = 'mytesttable'+'_'+Replace(cast(@today as nvarchar(10)),'-','')
set @chktblname = 'mytesttable'+'_'+Replace(cast(DATEADD(day,-3,@today) as nvarchar(10)),'-','')
IF EXISTS (select top 1 1 from sys.sysobjects where name = @chktblname)
BEGIN
SET @inserttblname = @chktblname
END
ELSE
BEGIN
set @sql = N'
if object_id('''+@inserttblname+N''',''u'') is not null
drop table '+@inserttblname+N'
'
execute sp_executesql @sql
set @sql= N'
create table '+@inserttblname+N'
(
id int identity
,value nvarchar(max)
,lastdt date default getdate()
)
'
execute sp_executesql @sql
END
set @sql = N'
insert into '+@inserttblname+N' (value)
select @value
'
set @paradef=N'@value NVARCHAR(MAX)'
execute sp_executesql @sql,@paradef,@value
commit tran
end try
begin catch
rollback tran
end catch
end
go
so why to go over this dynamic code generation?
this does not require Enterprise version.
the code generation is not that complex as it looks. its one time creation only.
A simple DROP TABLE API will do what you need for fastest delete.
At any given time select is not looking across more than 3 days of worth data in you case.
As you drop entire tables no issue to deal with after Delete/Truncate like fragmentation.
