[posted and mailed, please reply in news]
elpico (ke**************@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,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
