Post reply

Query on converting the non Partitiontable to Partion table

  • February 25, 2008 at 6:24 am

    #120539

    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

  • February 25, 2008 at 6:34 am

    #781519

    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.

  • February 25, 2008 at 7:15 am

    #781533

    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!

  • February 25, 2008 at 7:15 am

    #781534

    any suggestion on that ?!!!:)

  • February 25, 2008 at 7:18 am

    #781536

    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!

  • February 25, 2008 at 7:23 am

    #781538

    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

  • February 25, 2008 at 7:26 am

    #781541

    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!

  • February 25, 2008 at 8:11 am

    #781561

    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

  • February 25, 2008 at 8:29 am

    #781580

    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!

  • February 27, 2008 at 1:13 am

    #782374

    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

  • February 27, 2008 at 6:59 am

    #782478

    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

  • February 27, 2008 at 10:35 am

    #782718

    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

  • February 28, 2008 at 12:09 am

    #783011

    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

  • February 28, 2008 at 1:45 am

    #783038

    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

  • February 28, 2008 at 9:33 pm

    #783591

    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