SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query on converting the non Partitiontable to Partion table


Query on converting the non Partitiontable to Partion table

Author
Message
Saravanakumar.Rathinam
Saravanakumar.Rathinam
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 493
Hi Team,

While I attempting to try to convert the nonPartitioning table to partition table, i faced the below issues.

Steps I made.

Assume i have two column in my table one as ID,Date I want to be split the table by Date wise using the column "ID". The ID is the primary key in the table and dozens of child table referencing to this ID,

1. In this case. I Dropped the clustered index and make the new partition key for column "ID" and partitioned the table successfully. I have used the below queries for partitioning.

a. Create Clustered Index PK_ID on TABLEA(ID) on SCHEME_YR(dt)
b. Drop index PK_ID on TABLEA with (online = OFF, Move To SCHEME_YR (dt))

The table got partitioned successfully but, i have the issue while recreating the primary key clustered index for the column "ID"
I got the error message while i create the index
command :

ALTER TABLE mxDocument WITH NOCHECK
ADD CONSTRAINT PK_mxDocument1 PRIMARY KEY CLUSTERED ([lSysKey]) on SArch_Yr (dtCompletion)

Error message:
Msg 1902, Level 16, State 3, Line 1
Cannot create more than one clustered index on table 'mxDocument'. Drop the existing clustered index 'PK_mxDocument' before creating another.

I shouldn't include/add the other column to make a primary key. because the Column ID id the referencing column for other child tables.

Kindly help me to resolve this issue..

thanks & regards
Saravanakumar.R
Saravanakumar.Rathinam
Saravanakumar.Rathinam
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 493
Sorry i copied wrong error message.

error message is

Msg 1908, Level 16, State 1, Line 1 olumn 'PartCol' is partitioning
column of the index 'cl_ix'. Partition columns for a unique index must
be a subset of the index key.
Preethiviraj Kulasingham
Preethiviraj Kulasingham
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 86
Hi Sara,
A Couple of points:
1. As the actual data pages should be based on clustered index, you can't have the clustered index on one column and partition on another column.
2. Primary key is usually clustered, but it is not a must.
3. You can have a clustered index on one column and primary key on another column.
I suggest that you should explore one of the two solutions (but not both):
1. Create the primary Key as NON CLUSTERED
2. Think about partitioning the table based on Primary key

Let us know if you still have issues.

Cheers,
Prithiviraj Kulasingham

Plan to Test your Plan!
Saravanakumar.Rathinam
Saravanakumar.Rathinam
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 493
any suggestion on that ?!!!Smile
Preethiviraj Kulasingham
Preethiviraj Kulasingham
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 86
[quote]Preethiviraj Kulasingham (2/25/2008)

I suggest that you should explore one of the two solutions (but not both):
1. Create the primary Key as NON CLUSTERED
2. Think about partitioning the table based on Primary key

[quote]

Cheers,
Prithiviraj Kulasingham

Plan to Test your Plan!
Saravanakumar.Rathinam
Saravanakumar.Rathinam
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 493
is it necessary the primary key should be unique or not reqd ?

because i don't have any other unique column apart from "ID"

thanks & regards
Saravanakumar.R
Preethiviraj Kulasingham
Preethiviraj Kulasingham
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 86
Primary key should be Unique, but need not to be Clustered.
Clustered index need not to be Unique

Cheers,
Prithiviraj Kulasingham

Plan to Test your Plan!
Saravanakumar.Rathinam
Saravanakumar.Rathinam
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 493
Except "ID" column, I don;t have any primary key column in TableA and all other columns having duplicate values. Moreover the "ID" is the only column which is references key column of other child tables. So I couldn't able to altering primary key.
In these scenario what about table partition ?
Please correct me if i'm wrong. So, we are not able to convert the non partition table to partition table ?

if we can, Kindly let me know, if you have an example on these ?

Thanks for your reply in time and request your help.

thanks & regards
Saravanakumar.R
Preethiviraj Kulasingham
Preethiviraj Kulasingham
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 86
I recently gave a presentation on table aprtitioning at Sri Lankan user group meeting. Please check the site:
http://sqlserveruniverse.com/content/MeetingList.aspx
Let me clarify one point:

You can have the primary key as the clustered index too. If you do, you need to aprtition the table on the same key (I.e. ID and not on date) But you can have a clustered index on Date column while havign primary key on ID.
The date column need not to be unique.

Hope this clarifies.

Cheers,
Prithiviraj Kulasingham

Plan to Test your Plan!
Saravanakumar.Rathinam
Saravanakumar.Rathinam
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 493
hi,

i have changed the clustered index from (id) column to (date) column, Now i am able and create the clustered index after i made the partitioning. but the query performance is relatively very slow comparing the new partition table vs non partition table. Even I shrink the database the query performance is same. The slowness due to the clustered index column is not a primary key i.e date column in my table.

please let me know., if you have any other suggestion.

thanks for your timely help.

regards
Saravanakumar.R
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search