Query on converting the non Partitiontable to Partion table

  • 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

  • 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.

  • 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!

  • any suggestion on that ?!!!:)

  • 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

    Cheers,
    Prithiviraj Kulasingham

    Plan to Test your Plan!

  • 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

  • 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!

  • 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

  • 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!

  • 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

  • the partition table cost is higher than the nonpartition table for the particular column ? is any reason behind on these ?

    i created these column as non Clustered index

    Use ArchiveSyDB

    Select * from tablea where field1 = 'S07288_0006_010' - Partition table Query cost - 76

    use ArchiveManagementSystemDB

    Select * from table a where field1 = 'S07288_0006_010' - Non partition table Query Cost = 24

    Apart from this field1 column all others are lesser query cost comparing to non partition table.

    thanks & regards

    Saravanakumar.R

  • Check if thie link below helps you.

    http://sql-articles.com/index.php?page=articles/dpart.htm&PHPSESSID=4cd6af148294965cf66fe16d4428d396

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • Dear Sagee,

    In the production area we can't copy the entire rows from actual table to new temptable and re-upload the same from new temptable to actual table.

    Any other suggestion you have?

    thanks & regards

    Saravanakumar.R

  • i have a basic query ! why don't we are not applied the Partitioned table instead of non partitioning table in Production Area ? is it cause any problem ?

    if problem, kindly explain ?

    thanks & regards

    Saravanakumar.R

  • any update on this ?

    regards

    Saravanakumar.R

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply