Add primary key to a partioned table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Add primary key to a partioned table

Add primary key to a partioned table

Rate Topic

Display Mode

Topic Options

Author

Message

Mh-397891

Posted Saturday, November 15, 2008 10:55 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, February 02, 2012 10:18 AM
Points: 255, Visits: 1,411

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!

Post #603305

sqlizer

Posted Sunday, November 16, 2008 10:16 AM

SSChasing Mays

Group: General Forum Members
Last Login: Monday, March 07, 2011 1:19 PM
Points: 601, Visits: 572

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.

Post #603353

Michelle Ufford-459603

Posted Monday, November 17, 2008 7:25 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, September 16, 2011 3:13 PM
Points: 468, Visits: 280

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

Post #603662

sqlizer

Posted Monday, November 17, 2008 10:12 AM

SSChasing Mays

Group: General Forum Members
Last Login: Monday, March 07, 2011 1:19 PM
Points: 601, Visits: 572

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.

Post #603808

ALZDBA

Posted Monday, November 17, 2008 10:25 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:40 AM
Points: 6,627, Visits: 7,336

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

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?

"press F1 for solution", "press shift+F1 for urgent solution" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #603819

ClusterJunkie

Posted Thursday, January 28, 2010 4:38 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, February 01, 2012 7:00 AM
Points: 21, Visits: 290

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?

Post #855078

ALZDBA

Posted Thursday, January 28, 2010 6:54 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:40 AM
Points: 6,627, Visits: 7,336

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

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #855177

ClusterJunkie

Posted Tuesday, March 02, 2010 4:07 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, February 01, 2012 7:00 AM
Points: 21, Visits: 290

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?

Post #874996

ALZDBA

Posted Tuesday, March 02, 2010 4:36 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:40 AM
Points: 6,627, Visits: 7,336

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

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #875008

« Prev Topic | Next Topic »

Permissions