November 15, 2008 at 10:55 pm
I have created partition on an existing table, Steps I followed are as follows:
Created partition function
Created partition scheme
I tried to drop index , but could not because of composite primary key,so I have dropped primary key and recreated clustered Index on partition scheme assuming that primary key will be added automatically after creation of index, but it did not create primary key.
So I am trying to figure it out how to add composite primary key to table. I cannot leave table with out primary key.
So far I see that data in the table is partitioned as I required, but only composite primary key is missing on the table.
Can any one please advise how can I resolve this problem. Thanks!
November 16, 2008 at 10:16 am
Did you create unique clustered index on the columns which were mentioned in the composite primary key? If you did then I guess You have created unique constraint and I guess that solves your problem of uniqueness.
November 17, 2008 at 7:25 am
The partitioning key must be included in the primary key on a partitioned table. Try using something like this:
Alter Table dbo.yourTableName
Add Constraint PK_yourTableName
Primary Key (column1, column2) -- ensure one of these is the partitioning key
With (MaxDop = 1); -- optional, limits CPU used to complete task
Regards,
Michelle Ufford
SQLFool.com - Adventures in SQL Tuning
November 17, 2008 at 10:12 am
Thanks Michelle F. Ufford.
I implemented Partition table but wasn't able to get primary key on a column but with the help of your script now I have primary key on my table.
November 17, 2008 at 10:25 am
One of the flaws in SQL2005 partitioning, is that you need to add the partitioned key to any unique or primary key, if you want that index applying the partitioning function.
This is a "minor" flaw compared the strength you get with partitioning.
Two reasons for this:
- By adding the partitioned key to your former primary key, you end up with a new primary key (which needs to be forwarded to dependent tables, ...)
- however, you can have a less optimal workaround, by creating the pk and having it hosted in another non-partitioned filegroup. Keep in mind, when doing this, you can no longer perform all partial stuff,...
So you have to choose .....
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 28, 2010 at 4:38 am
I have 250 million row table that I wish to partition 10-15 ways in SQL2K8, single filegroup if possible.
Problem am facing is that I have a unique composite clustered index across 3 columns that I wish to retain, can I choose this as the partioning key and if not is the solution to add an additional bigint column and then put back my composite clustered index across 4 columns?
January 28, 2010 at 6:54 am
As always it depends ...
Partitioning only uses a single column to work on ...
If you aim to partition on one of the columns that currently composes your primary key ... that shouldn't be a problem...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 2, 2010 at 4:07 am
Thanks very much, its actually working out easier this way since the composite clustered index was designed for the selectivity of the queries against it in the first place
so makes complete sense to do it by date.
Finally, I haven't yet got the test environments sorted for this so haven't been able to test - when i partition the table by creating the functions / schemes etc does the data automatically move into the partitions or do I have to move it by inserting etc?
March 2, 2010 at 4:36 am
If you can, start with an empty table, because it will handle the partition shift at the apply partitioning time.
At insert time, it will put the data in the correct partition.
If the partitioning key is updated, the row will be moved to the partition that meets the new value.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply