Ageing data using Sql Server 2000

Hi there,

I've only recently started a project using sql server 2000 for the
first time. One of the considerations we have to take is that we need
to continuously age/purge data from a couple of large tables (100
million rows+) - to keep the tablesize growth to a minimum. Coming
from an Oracle background, I've used partitions to help manage this
before.

Now with Sql Server 2000, I'm wondering what the best/recommended
approaches are for ageing data - I've been struggling to find out
enough inforamtion on the msdn site, so I'm hoping some gurus out
there can help me here.

Thanks![posted and mailed, please reply in news]

elpico (kevinmartinwalsh@.yahoo.co.uk) writes:
> I've only recently started a project using sql server 2000 for the
> first time. One of the considerations we have to take is that we need
> to continuously age/purge data from a couple of large tables (100
> million rows+) - to keep the tablesize growth to a minimum. Coming
> from an Oracle background, I've used partitions to help manage this
> before.
> Now with Sql Server 2000, I'm wondering what the best/recommended
> approaches are for ageing data - I've been struggling to find out
> enough inforamtion on the msdn site, so I'm hoping some gurus out
> there can help me here.

Partitioned views may be your best bet. You would have one table each
for each chunk you want to prune at a time. (You would have to know the
size of the chunk before you start inserting the data, obviously.) Each
table would have a check constraint on the primary key which constrains
the table to its partition, and then you combine all tables into a view
by means of UNION ALL.

Provided that you follow certain rules you can insert directly through
the view. You can also use a INSTEAD OF triggers that divert the data
into the approriate table.

Each time you need to add a new partition, you would need to alter
the view, and possibly also alter the constraint for the primary key
for the top-most table. But you could create partitions long before you
actually need them, to make this a swift operation.

I've only given you a brief introduction. Use the index in Books Online to
find "partitioned views" to get more information.

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

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