Posted by Mastino on 02/28/07 12:57

First, thank you for the answer, Erland, it's not the first time you help me
!

> Permit me to start with SQL 2005. There you have partitioned tables,
> and in a case like yours you would set up the table with let's say
> four partitions, with the month as the partitioning column. To delete
> old rows, you would simply take that table out of the partition
> table, and then drop table that table. You in the same manner, shift
> in a new table for the next month. Here I said month, but you have one
> partition per day, and have 90 partitions if you like - whether this
> is a good idea I don't know.

Reading and reading over the internet, I found that I can transfer the data
to be dropped with a:

ALTER TABLE SWITCH...

and I can also make partition function dynamic:

http://msdn2.microsoft.com/en-us/library/aa964122.aspx

now I'm studying hard for the solution, the problems to resolve are many.


>
> Note that partitioned tables are only available in the Enterprise
> (and Developer) Edition of SQL 2005.
>
> In SQL 2005, you would use partitioned views (and here 90 paritions
> would definitely go beyond what is manageable). One table per month
> and then they are united in a view with a UNION ALL statement. At
> a new month you would run a job that dropped the table from four
> months back, and create a new table. Notice that you can load directly
> to the new, and data should turn in the right place.
>
> See also Stefan Delmarco's article on partitioned views:
> http://www.fotia.co.uk/fotia/FA.02.Sql2KPartitionedViews.01.aspx

I do not want, and I cannot use partitioned wiews, I have to delete what we
do not need any more.

>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thank you

Massimo / Mastino

[Back to original message]