April 27, 2011 at 10:32 pm
Hi
I have a table 70gb. 4 billion records.
There is a primary key with clustered index on a ID column
and there is another column called date(this has duplicate values).
it has data from 2007 to present
Now i want to create partitions quarterly based.
i created enough file groups and .NDF files.
i used create partition wizard for partitioning the table.
Right click on table > storage> create partition
in this way i followed all the steps and i provided every thing.
after finishing every thing the existing data didn't move to the new .NDF files
can any one help me on this?
April 28, 2011 at 6:19 am
Did you set each partition to the correct filegroup?
The easiest way to do what you want (if you have the room) is to create a second table partitioned the way you want and transfer the data.
April 9, 2012 at 2:00 am
It is incorrect! there is no need to create new file to store temporary data for moving.
April 9, 2012 at 10:55 pm
It says "Easiest way", not that it's required...
(1 year old topic)
April 20, 2012 at 8:03 am
I have a similar question.
I tried creating a partition on a table in sql server 2008 R2.
But i do not see the storage option when i right click the table.
Then i read somewhere that i have to install SP1 to get storage>>partition option in SSMS.
Did that but still no luck..
Any help is appreciated.
April 20, 2012 at 8:08 am
tauseef.jan (4/20/2012)
I have a similar question.I tried creating a partition on a table in sql server 2008 R2.
But i do not see the storage option when i right click the table.
Then i read somewhere that i have to install SP1 to get storage>>partition option in SSMS.
Did that but still no luck.
Do not use SSMS, script it 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 20, 2012 at 8:10 am
Partitioning is only available to Enterprise edition of SQL Server, you are most likely using a Standard edition.
April 23, 2012 at 3:48 am
No doubt there is facility to use SSMS but I suggest to use script what I do!
April 23, 2012 at 3:50 am
You will have to move data by scripting or in another way provided by SQL Server. It is an Obligatory action!
April 24, 2012 at 8:40 am
<RANT ON>
To anyone interested in using SQL Server partitioning, LISTEN UP:
SQL Server partitioning is a COMPLEX subsystem with MANY gotchas, requirements, provisos, etc!!! You CANNOT POSSIBLY HOPE TO BE SUCCESSFUL implementing them by reading a few blob posts, forum threads, BOL and then slapping them onto your system. You MAY get lucky, but I have lost track of the number of clients and forum posters that have been no better off, or worse come to grief, pursuing partitioning - often when it was completely unnecessary - without expert advice/assistance. Also note that partitioning was NOT CREATED FOR PERFORMANCE REASONS! It was created to facilitate VLDB management and especially data loading. You MAY get increased performance on SOME data access, but often do not.
</RANT OFF>
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply