Historical tables, partitioning or what?

I have about 45000 records in a CSV file, which I am using as HTTP request parameters to query a website and store some results in a database. This is the kind of application which runs 24/7, so database grows really quickly. Every insert fires up a trigger, which has to look for some old records based on some criteria and modify the last inserted record. My client is crazy about performance on this one and suggested to move the old records into another table, which has exactly the same structure, but would serve as a historical table only (used to generate reports, statistics, etc.), whilst the original table would store only the latest rows (so no more than 45k at a given time, whereas the historical table may grow to millions of records). Is this a good idea? Having the performance in mind and the fact that there's that trigger - it has to run as quickly as possible - I might second that idea. Is it good or bad? What do you think?

I read a similar post here, which mentioned SQL Server 2005 partitioning, I might as well try this, although I never used it before.

I think you should use the archive table and partition it with 2005s new capabilities. This will allow you to easily administer the table(s). Heres a good link on 2005 partitioning. http://msdn2.microsoft.com/en-us/library/ms345146.aspx If you use a seperate archive table, you can create good indexes that will really speed queries. The only bad thing is that if you insert new records into it often, it could be slow. So, you might want to only insert new archive records during off-peak time if possible.
Tim|||I would caution to have the absolute minimal indexes on the archive table. In some situations, it may be efficient to create an index just for a report, and then remove the index as soon as the report is complete.|||Thanks for your comments. I read about SQL Server 2005's partitioning feature and find it very interesting (and useful, I think), but the application in question runs on the Express Edition currently with possible switch to a higher version, but doubtfuly the Enterprise one, so partitioning is out of the question, unfortunately. I will hopefuly use it when I will work on some enterprise level application in the future.

Historical Stats

Ok folks,
Need your help once again.

I have 4 Tables that need to get accurate stats from...

2 are historical, 2 are current

the 2 historical are identical to the 2 present ones, just the historical is everything up to a cutoff date, the the 2 present is everything from that cutoff date on...

They contain login information, access times, install counts etc.

eg. OldTable1 = serialNo, ID, Install Date, NoOfInstalls, etc..
OldTable2 = SerailNo, Accessdate, Installid, UserID etc.
NewTable1 = serialNo, ID, Install Date, NoOfInstalls, etc..
NewTable2 = SerailNo, Accessdate, Installid, UserID etc.
I need to get total installs (gathered from both tables), total accesses after given dates etc...

the records are created at each login, so records may be in both sets of tables, (ie, if accessed after cutoff, a new record is created in the new tables...)

Any ideas?

Thanks in advanceLook up the use of the UNION operator in Books Online. It can be used to effectively combine two tables for processing, or to combine the results of two select statements.

blindman|||And if you post some DDL, DML and sample data, I bet we can help alot more..

CREATE TABEL myTable99 (col1 int, ect

INSERT INTO myTable99 (col1, ect
SELECT 1, ect UNION ALL
SELECT 1, ect UNION ALL
SELECT 1, ect UNION ALL
SELECT 1, ect

SELECT * FROM myTable99 WHERE ect -- or whatever you've tried som far...

might be worth your time...|||This is what I am trying, but it has not been working..I get this is not permitted when the subquery follows ... (or when the subquery follows and expression)

I am trying to do this with out a new table, but a view is fine...

I am just trying to count version 4s from old, version 4s from new, then the union that combines (just as a test audit for now)

should be minimal overhead this way...

CREATE VIEW dbo.FV_Serial_Reg
AS

SELECT COUNT(SerialNumber) AS Ver4_Old,
(

SELECT COUNT(SerialNumber)
FROM FRV.dbo.WFSerialNumber
WHERE left(Version,1) = '4'
) AS Ver4_New,
(
SELECT Version4 = COUNT(SerialNumber) FROM FRV.dbo.WFSerialNumber WHERE left(Version,1) = '4'
UNION
SELECT COUNT(SerialNumber) FROM FRV.dbo.WFSerialNumber_PREV WHERE left(Version,1) = '4'
) AS Version4


FROM FRV.dbo.WFSerialNumber_Prev
WHERE left(Version,1) = '4'|||DUDE.....

ya lost me...is there a question here?

why are you doing what you're doing?

Does it work|||I mentioned the error in the previous post...

This is what I am trying, but it has not been working..

I get this is not permitted when the subquery follows ... (or when the subquery follows and expression)

I need these stats in a view so that we access them via a data access page created with MS Access.

historical monthly/weekly/daily totals breakdown

I have a sales table( id, date , techid, amount)
id date techid amount
---
1 01/01/2005 tsmith 99.99
...
1054 08/16/2005 jborrow 29.99
I need to create historical report&graph with monthly totals (amount) for
every agent: Jan,Feb...Aug.
Jan Feb... Aug
tsmith 15898.44 69352.88 586311.55
How can I aggregate daily data in order to get historical
monthly/weekly/daily totals breakdown.
Is there any function or option (in Report designer)?
Thank you!You may want to read up on "grouping" in the RS Books Online and also look
at the "Company Sales" and the "Employee Sales Summary" sample reports of RS
2000.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"agenda9533" <agenda9533@.discussions.microsoft.com> wrote in message
news:4C7B3EAA-7FAC-400F-A80F-9586A4A1EFF1@.microsoft.com...
>I have a sales table( id, date , techid, amount)
> id date techid amount
> ---
> 1 01/01/2005 tsmith 99.99
> ...
> 1054 08/16/2005 jborrow 29.99
> I need to create historical report&graph with monthly totals (amount) for
> every agent: Jan,Feb...Aug.
> Jan Feb... Aug
> tsmith 15898.44 69352.88 586311.55
>
> How can I aggregate daily data in order to get historical
> monthly/weekly/daily totals breakdown.
> Is there any function or option (in Report designer)?
> Thank you!
>sql

historical lookup query

I'm having a dickins of a time with a particular query and am hoping
someone here can help me.
Using the following example;
declare @.SearchDate datetime
set @.SearchDate = '30 Nov 2005'
declare @.t1 table (t1id int, t1desc varchar(10))
insert into @.t1 (t1id, t1desc) values (1, 'Ed')
insert into @.t1 (t1id, t1desc) values (2, 'Bill')
insert into @.t1 (t1id, t1desc) values (3, 'Bob')
insert into @.t1 (t1id, t1desc) values (4, 'Fred')
insert into @.t1 (t1id, t1desc) values (5, 'John')
declare @.t1history table (t1id int, t1desc varchar(10), created
datetime)
insert into @.t1history (t1id, t1desc, created) values (1, 'James', '01
Jan 2005')
insert into @.t1history (t1id, t1desc, created) values (1, 'Frank', '05
Jan 2005')
insert into @.t1history (t1id, t1desc, created) values (1, 'Henry', '10
May 2005')
insert into @.t1history (t1id, t1desc, created) values (1, 'Joe', '28
Nov 2005')
insert into @.t1history (t1id, t1desc, created) values (4, 'Toby', '21
Oct 2005')
insert into @.t1history (t1id, t1desc, created) values (4, 'Brian', '25
Oct 2005')
insert into @.t1history (t1id, t1desc, created) values (4, 'Horace', '28
Nov 2005')
insert into @.t1history (t1id, t1desc, created) values (5, 'Ben', '21
Oct 2005')
declare @.lookup table (val varchar(10))
insert into @.lookup (val) values ('Ben')
insert into @.lookup (val) values ('Frank')
insert into @.lookup (val) values ('Bill')
--example query
select *,
(select top 1 t1desc from @.t1history as Table1History where
Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
created desc) as t1deschistory
from @.t1 as Table1
I want to filter the results returned from @.t1 against those contained
in @.lookup
I also need to be able to filter the results based on what the value
for t1desc could have been in the past using @.t1history and @.SearchDate
For example, with the date of '30 Nov 2005' I would expect the
following;
t1id t1desc t1deschistory
----
2 'Bill' null
5 'John' 'Ben'
Changing the date to '15 Oct 2005' I would expect;
t1id t1desc t1deschistory
----
2 'Bill' null
and changing it again to '15 Jan 2005' I would expect;
t1id t1desc t1deschistory
----
1 'Ed' 'Frank'
2 'Bill' null
What I basically want to do is this;
select *,
(select top 1 t1desc from @.t1history as Table1History where
Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
created desc) as t1deschistory
from @.t1 as Table1
where t1desc in (select val from @.lookup) or t1deschistory in (select
val from @.lookup)
This gives the following error as expected;
Server: Msg 207, Level 16, State 3, Line 28
Invalid column name 't1deschistory'.
Moving the sub-query into a join doesn't work either;
select *
from @.t1 as Table1
left join (select top 1 * from @.t1history as t1history where
t1history.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
created desc) Table1History on Table1.t1id = Table1History.t1Id
where Table1.t1desc in (select val from @.lookup) or
Table1History.t1desc in (select val from @.lookup)
This gives the following error;
Server: Msg 107, Level 16, State 2, Line 28
The column prefix 'Table1' does not match with a table name or alias
name used in the query.
Can anyone help?
Many thanks in advance,
Edone way: make it a derived table before applying the where e.g.
select * from (
select *,
(select top 1 t1desc
from @.t1history as Table1History
where Table1History.t1id = Table1.t1Id
and created <= @.SearchDate +1
order by created desc) as t1deschistory
from @.t1 as Table1
) x
where t1desc in (select val from @.lookup) or t1deschistory in (select
val from @.lookup)
ThievingScouser wrote:
> I'm having a dickins of a time with a particular query and am hoping
> someone here can help me.
> Using the following example;
> declare @.SearchDate datetime
> set @.SearchDate = '30 Nov 2005'
> declare @.t1 table (t1id int, t1desc varchar(10))
> insert into @.t1 (t1id, t1desc) values (1, 'Ed')
> insert into @.t1 (t1id, t1desc) values (2, 'Bill')
> insert into @.t1 (t1id, t1desc) values (3, 'Bob')
> insert into @.t1 (t1id, t1desc) values (4, 'Fred')
> insert into @.t1 (t1id, t1desc) values (5, 'John')
> declare @.t1history table (t1id int, t1desc varchar(10), created
> datetime)
> insert into @.t1history (t1id, t1desc, created) values (1, 'James', '01
> Jan 2005')
> insert into @.t1history (t1id, t1desc, created) values (1, 'Frank', '05
> Jan 2005')
> insert into @.t1history (t1id, t1desc, created) values (1, 'Henry', '10
> May 2005')
> insert into @.t1history (t1id, t1desc, created) values (1, 'Joe', '28
> Nov 2005')
> insert into @.t1history (t1id, t1desc, created) values (4, 'Toby', '21
> Oct 2005')
> insert into @.t1history (t1id, t1desc, created) values (4, 'Brian', '25
> Oct 2005')
> insert into @.t1history (t1id, t1desc, created) values (4, 'Horace', '28
> Nov 2005')
> insert into @.t1history (t1id, t1desc, created) values (5, 'Ben', '21
> Oct 2005')
> declare @.lookup table (val varchar(10))
> insert into @.lookup (val) values ('Ben')
> insert into @.lookup (val) values ('Frank')
> insert into @.lookup (val) values ('Bill')
> --example query
> select *,
> (select top 1 t1desc from @.t1history as Table1History where
> Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
> created desc) as t1deschistory
> from @.t1 as Table1
>
> I want to filter the results returned from @.t1 against those contained
> in @.lookup
> I also need to be able to filter the results based on what the value
> for t1desc could have been in the past using @.t1history and @.SearchDate
> For example, with the date of '30 Nov 2005' I would expect the
> following;
> t1id t1desc t1deschistory
> ----
> 2 'Bill' null
> 5 'John' 'Ben'
> Changing the date to '15 Oct 2005' I would expect;
> t1id t1desc t1deschistory
> ----
> 2 'Bill' null
> and changing it again to '15 Jan 2005' I would expect;
> t1id t1desc t1deschistory
> ----
> 1 'Ed' 'Frank'
> 2 'Bill' null
>
> What I basically want to do is this;
> select *,
> (select top 1 t1desc from @.t1history as Table1History where
> Table1History.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
> created desc) as t1deschistory
> from @.t1 as Table1
> where t1desc in (select val from @.lookup) or t1deschistory in (select
> val from @.lookup)
> This gives the following error as expected;
> Server: Msg 207, Level 16, State 3, Line 28
> Invalid column name 't1deschistory'.
> Moving the sub-query into a join doesn't work either;
> select *
> from @.t1 as Table1
> left join (select top 1 * from @.t1history as t1history where
> t1history.t1id = Table1.t1Id and created <= @.SearchDate +1 order by
> created desc) Table1History on Table1.t1id = Table1History.t1Id
> where Table1.t1desc in (select val from @.lookup) or
> Table1History.t1desc in (select val from @.lookup)
> This gives the following error;
> Server: Msg 107, Level 16, State 2, Line 28
> The column prefix 'Table1' does not match with a table name or alias
> name used in the query.
>
> Can anyone help?
> Many thanks in advance,
> Ed
>

Historical Data Problem

We have a database that adds over 100,000 records per day. This goes back to 2002 and I only need historical data for 6 months. Presently we can only can delete 1000 row at a time. Is there a faster way of deleting. We seem to continuely run out of disk space. Urgent!!!!!!!!!!!If you just need a small set of data from a much larger set, you are better off insert those data you need into a new table, drop the old table, and rename the new table to the old table's name.|||After deciding to pull data out of the large table going back 6 months only. I created a statement that pulls each field out and will insert into a temp table. I get this error message.
'Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'INCOMING_TEMP' when IDENTITY_INSERT is set to OFF.'
I'm not sure what this means. Is it that I cannot insert into a ID field?|||Check 'SET IDENTITY_INSERT' in the BOL.|||? Check the bol???|||Books... On... Line... B.O.L.|||Due to the forum being down. I did some reserch on the error message and found a solution. I went into the management properties of the new temp_table and made it able to except inserts. The update is running as we speak. It's been running about 20 minutes and that's about right. The date range hold about 1,500,000 record per month. And that's why I'm clearing it out and holding only 6 months of backup historical data. Day 2 of new project.|||What your tranny logs and tempdb....

Are you doing this in batches with transactions?

A permanent table I think would have been better

Historical Data Problem

We have a database that adds over 100,000 records per
day. This goes back to 2002 and I only need historical
data for 6 months. Presently we can only can delete 1000
row at a time. Is there a faster way of deleting. We
seem to continuely run out of disk space.
Urgent!!!!!!!!!!!
Can you tell me why you can only delete 1000 rows at a time?
Jeff Duncan
MCDBA, MCSE+I
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!
|||I also don't understand what you mean by "we can only delete 1000 row at a
time"... what happens when you try to delete 1001?
I recommend halting the inserts briefly, copy the last 6 months of data into
a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the old
table, rename the new table.
Then, set up a job that runs daily (or maybe more often) that deletes any
data more than 6 months old (184 days is probably the safest to account for
31-day months). This way your window will always be roughly six months, and
you won't be tripping over yourself trying to trim it down. Automate it as
much as possible, it's less taxing on both the system and your hair.
However, if you are running out of disk space now, what happens when your
volume increases and you are adding 200k, 500k, 1m rows per day?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!
|||Because we at present, time out error occur when
attempting to delete more than 1000.
>--Original Message--
>Can you tell me why you can only delete 1000 rows at a
time?
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"Garry Dawkins" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:798401c4311f$5f08b590$a301280a@.phx.gbl...
1000
>
>.
>
|||I agree with Aaron. Also what criteria is in your delete command. Is the
value your deleting against indexed.
Jeff Duncan
MCDBA, MCSE+I
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%238mJPFSMEHA.628@.TK2MSFTNGP11.phx.gbl...
> I also don't understand what you mean by "we can only delete 1000 row at a
> time"... what happens when you try to delete 1001?
> I recommend halting the inserts briefly, copy the last 6 months of data
into
> a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the
old
> table, rename the new table.
> Then, set up a job that runs daily (or maybe more often) that deletes any
> data more than 6 months old (184 days is probably the safest to account
for
> 31-day months). This way your window will always be roughly six months,
and
> you won't be tripping over yourself trying to trim it down. Automate it
as
> much as possible, it's less taxing on both the system and your hair.
> However, if you are running out of disk space now, what happens when your
> volume increases and you are adding 200k, 500k, 1m rows per day?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
> news:798401c4311f$5f08b590$a301280a@.phx.gbl...
>

Historical Data Problem

We have a database that adds over 100,000 records per
day. This goes back to 2002 and I only need historical
data for 6 months. Presently we can only can delete 1000
row at a time. Is there a faster way of deleting. We
seem to continuely run out of disk space.
Urgent!!!!!!!!!!!Can you tell me why you can only delete 1000 rows at a time?
--
Jeff Duncan
MCDBA, MCSE+I
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!|||I also don't understand what you mean by "we can only delete 1000 row at a
time"... what happens when you try to delete 1001?
I recommend halting the inserts briefly, copy the last 6 months of data into
a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the old
table, rename the new table.
Then, set up a job that runs daily (or maybe more often) that deletes any
data more than 6 months old (184 days is probably the safest to account for
31-day months). This way your window will always be roughly six months, and
you won't be tripping over yourself trying to trim it down. Automate it as
much as possible, it's less taxing on both the system and your hair.
However, if you are running out of disk space now, what happens when your
volume increases and you are adding 200k, 500k, 1m rows per day?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!|||Because we at present, time out error occur when
attempting to delete more than 1000.
>--Original Message--
>Can you tell me why you can only delete 1000 rows at a
time?
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"Garry Dawkins" <anonymous@.discussions.microsoft.com>
wrote in message
>news:798401c4311f$5f08b590$a301280a@.phx.gbl...
>> We have a database that adds over 100,000 records per
>> day. This goes back to 2002 and I only need historical
>> data for 6 months. Presently we can only can delete
1000
>> row at a time. Is there a faster way of deleting. We
>> seem to continuely run out of disk space.
>> Urgent!!!!!!!!!!!
>
>.
>|||I agree with Aaron. Also what criteria is in your delete command. Is the
value your deleting against indexed.
--
Jeff Duncan
MCDBA, MCSE+I
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%238mJPFSMEHA.628@.TK2MSFTNGP11.phx.gbl...
> I also don't understand what you mean by "we can only delete 1000 row at a
> time"... what happens when you try to delete 1001?
> I recommend halting the inserts briefly, copy the last 6 months of data
into
> a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the
old
> table, rename the new table.
> Then, set up a job that runs daily (or maybe more often) that deletes any
> data more than 6 months old (184 days is probably the safest to account
for
> 31-day months). This way your window will always be roughly six months,
and
> you won't be tripping over yourself trying to trim it down. Automate it
as
> much as possible, it's less taxing on both the system and your hair.
> However, if you are running out of disk space now, what happens when your
> volume increases and you are adding 200k, 500k, 1m rows per day?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
> news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> > We have a database that adds over 100,000 records per
> > day. This goes back to 2002 and I only need historical
> > data for 6 months. Presently we can only can delete 1000
> > row at a time. Is there a faster way of deleting. We
> > seem to continuely run out of disk space.
> > Urgent!!!!!!!!!!!
>

Historical Data Problem

We have a database that adds over 100,000 records per
day. This goes back to 2002 and I only need historical
data for 6 months. Presently we can only can delete 1000
row at a time. Is there a faster way of deleting. We
seem to continuely run out of disk space.
Urgent!!!!!!!!!!!Can you tell me why you can only delete 1000 rows at a time?
Jeff Duncan
MCDBA, MCSE+I
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!|||I also don't understand what you mean by "we can only delete 1000 row at a
time"... what happens when you try to delete 1001?
I recommend halting the inserts briefly, copy the last 6 months of data into
a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the old
table, rename the new table.
Then, set up a job that runs daily (or maybe more often) that deletes any
data more than 6 months old (184 days is probably the safest to account for
31-day months). This way your window will always be roughly six months, and
you won't be tripping over yourself trying to trim it down. Automate it as
much as possible, it's less taxing on both the system and your hair.
However, if you are running out of disk space now, what happens when your
volume increases and you are adding 200k, 500k, 1m rows per day?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!|||Because we at present, time out error occur when
attempting to delete more than 1000.
>--Original Message--
>Can you tell me why you can only delete 1000 rows at a
time?
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"Garry Dawkins" <anonymous@.discussions.microsoft.com>
wrote in message
>news:798401c4311f$5f08b590$a301280a@.phx.gbl...
1000[vbcol=seagreen]
>
>.
>|||I agree with Aaron. Also what criteria is in your delete command. Is the
value your deleting against indexed.
Jeff Duncan
MCDBA, MCSE+I
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%238mJPFSMEHA.628@.TK2MSFTNGP11.phx.gbl...
> I also don't understand what you mean by "we can only delete 1000 row at a
> time"... what happens when you try to delete 1001?
> I recommend halting the inserts briefly, copy the last 6 months of data
into
> a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the
old
> table, rename the new table.
> Then, set up a job that runs daily (or maybe more often) that deletes any
> data more than 6 months old (184 days is probably the safest to account
for
> 31-day months). This way your window will always be roughly six months,
and
> you won't be tripping over yourself trying to trim it down. Automate it
as
> much as possible, it's less taxing on both the system and your hair.
> However, if you are running out of disk space now, what happens when your
> volume increases and you are adding 200k, 500k, 1m rows per day?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
> news:798401c4311f$5f08b590$a301280a@.phx.gbl...
>sql

historical data

A general data design question:

We have data which changes every week. We had considered seperating
historical records and current records into two different tables with
the same columns, but thought it might be simpler to have them all
together in one table and just add a WeekID int column to indicate
which week it represents (and perhaps an isCurrent bit column to make
querying easier). We have a number of tables like this, holding weekly
data, and we'll have to query for historical data often, but only back
through the last year -- we have historical data going back to 1998 or
so which we'll rarely if ever look at.

Is the all-in-one-table approach better or the seperation of current
and historical data? Will there be a performance hit to organizing data
this way? I don't think the extra columns will make querying too much
more awkward, but is there anything I'm overlooking in this?

Thanks.rottytooth (rottytooth@.gmail.com) writes:
> A general data design question:
> We have data which changes every week. We had considered seperating
> historical records and current records into two different tables with
> the same columns, but thought it might be simpler to have them all
> together in one table and just add a WeekID int column to indicate
> which week it represents (and perhaps an isCurrent bit column to make
> querying easier). We have a number of tables like this, holding weekly
> data, and we'll have to query for historical data often, but only back
> through the last year -- we have historical data going back to 1998 or
> so which we'll rarely if ever look at.
> Is the all-in-one-table approach better or the seperation of current
> and historical data? Will there be a performance hit to organizing data
> this way? I don't think the extra columns will make querying too much
> more awkward, but is there anything I'm overlooking in this?

One thing you could consider is partitioned views, with one table
per year. (So each year you add a new table and change the view.)

A partioned view is constructed as:

SELECT col1, col2, ...
FROM tbl1
UNION ALL
SELECT col1, col2,
FROM tbl2
UNION ALL
...

In the tables there needs to be a check constraint on a column which is
part of the PK, and the constraint should define the range for that
table. Ranges must not overlap.

The point is that when you query the view, SQL Server will only access
the tables that are covered by the condition on the partioning column.

Please see Books Online for further details.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp