June 17, 2007 at 9:49 pm
I have 265GB Database & i am curious to know if it is possible to partition existing table in database without affecting production & data. any help is appreciated. thanking in advance.
June 18, 2007 at 12:25 am
If its SQL 2005 database you can go for data partitioning else if its 2000 my suggestion is to archive the table that has history of records keeping only the current records in the table. this way you ensure that your database has optimal records and yuor archive data is also available.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 19, 2007 at 6:23 am
Look for partitioning views in SQL 2000 Books online. You can horizontally split your data into multiple tables and create a view that is the name of the original table. Using check constraints, the query optimizer can determine the correct table to find the data you are looking for.
This works pretty well.
You can also continue to insert and update the view in SQL 2000, so it can be treated as a table. With some work, you can make this transparent to your application. You will need to be running Enterprise edition for the insert / update piece to work.
June 19, 2007 at 6:49 am
Either way you have to move some data. SQL 2005 partitioning features require the table to be empty, has to be created on a partition scheme instead of a filegroup. Whether you union all it back together or not depends on what you want to accomplish.
June 20, 2007 at 7:17 am
Michael - I've have updateable partitioned views working using SQL 2000 Standard Edition.
I couldn't implement in the end, as doing a TOP 5 whatever ORDER BY datetime desc caused a table scan when using a view that unioned the data (PK was on a bigint).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply