Partitioning - logicall transparent?

In Oracle we have implemented database table partitioning for a table
exceeding 130 million rows, to alleviate maintenance issues (update stats,
back up etc). The partitions are by date such that after a month a new table
partition is created and inserts commence on the new partition. In this way
we can update stats on the last partition as required, the older partitions
being effectively static do not need further maintenance. All of this is
transparent to the application the database is supporting, we have also seen
good improvements in query performance.
Is there a way to do this for SQL 2000? Would it be transparent to the
application the database is supporting?
I would expect this table in a particular implementation to exceed 400
million rows per year once in production!Hi Andy
SQL 2000 cannot partition a table directly - it uses an alternative approach
based on views. It is generally transparent to applications except where the
table being partitioned was using an identity (sequence).
You can read more on partitioned views here:
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_06_17zr.asp
SQL Server 2005 includes direct table partitioning, although it's only in
Beta at this stage. It's partitioning support includes range which should
support your date ranges, but not hashlist or composite partitioning.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!|||Right now SQL Server 2000 has "distributed partitioned views" to partition
data. While it can have some advantages in the areas you are addressing it
is not as convenient as you would hope in regards to maintenance and such.
SQL 2005 will totally address this issue with some really great partitioning
functionality but it is currently only in Beta. Check out BOL under
"distributed partitioned views" for more information on the current
capabilities and have a look at http://www.microsoft.com/sql/2005/ for 2005
features.
--
Andrew J. Kelly SQL MVP
"Andy Black" <Andy Black@.discussions.microsoft.com> wrote in message
news:D34620B3-5E0D-4AFB-B810-5AA2F70A0A47@.microsoft.com...
> In Oracle we have implemented database table partitioning for a table
> exceeding 130 million rows, to alleviate maintenance issues (update stats,
> back up etc). The partitions are by date such that after a month a new
table
> partition is created and inserts commence on the new partition. In this
way
> we can update stats on the last partition as required, the older
partitions
> being effectively static do not need further maintenance. All of this is
> transparent to the application the database is supporting, we have also
seen
> good improvements in query performance.
> Is there a way to do this for SQL 2000? Would it be transparent to the
> application the database is supporting?
> I would expect this table in a particular implementation to exceed 400
> million rows per year once in production!