Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query on converting the non Partitiontable to Partion table Expand / Collapse
Author
Message
Posted Monday, February 25, 2008 6:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2008 1:05 AM
Points: 49, 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
Post #459638
Posted Monday, February 25, 2008 6:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2008 1:05 AM
Points: 49, 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.

Post #459643
Posted Monday, February 25, 2008 7:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 4:42 AM
Points: 19, 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!
Post #459662
Posted Monday, February 25, 2008 7:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2008 1:05 AM
Points: 49, Visits: 493
any suggestion on that ?!!!:)
Post #459663
Posted Monday, February 25, 2008 7:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 4:42 AM
Points: 19, 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!
Post #459666
Posted Monday, February 25, 2008 7:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2008 1:05 AM
Points: 49, 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
Post #459670
Posted Monday, February 25, 2008 7:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 4:42 AM
Points: 19, 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!
Post #459673
Posted Monday, February 25, 2008 8:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2008 1:05 AM
Points: 49, 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


Post #459695
Posted Monday, February 25, 2008 8:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 4:42 AM
Points: 19, 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!
Post #459717
Posted Wednesday, February 27, 2008 1:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 11, 2008 1:05 AM
Points: 49, 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
Post #460710
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse