Post reply

Partitioning with null column

  • March 6, 2016 at 3:10 am

    #309180

    Hi,

    we have partitioned a table based a column datatype 'null'.

    we have partitions created on all non-clustered indexes.And we didn’t create the partition column in the primary key as it an ‘null’ column. we created the non clustered indexes with this Partition schema, but not the primary key.

    And the table property is not showing as partitioned.But we see that we have data on all the partitions on the non clustered indexes that are being moved to respective partition and corresponding file group.

    we also found out that null records went to primary file group and all others went to respective partitioned file groups.

    is the table partitioned properly ?

    Thanks

  • March 6, 2016 at 4:35 pm

    #1863384

    RUNNERSQL (3/6/2016)

    Hi,

    we have partitioned a table based a column datatype 'null'.

    we have partitions created on all non-clustered indexes.And we didn’t create the partition column in the primary key as it an ‘null’ column. we created the non clustered indexes with this Partition schema, but not the primary key.

    And the table property is not showing as partitioned.But we see that we have data on all the partitions on the non clustered indexes that are being moved to respective partition and corresponding file group.

    we also found out that null records went to primary file group and all others went to respective partitioned file groups.

    is the table partitioned properly ?

    Thanks

    In a word. No. Why would you partition on a nullable column that can change? That would require data movement between partitions.

    And, yes, that was a rhetorical question. There's no good answer to that question.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 6, 2016 at 4:57 pm

    #1863385

    yes, we realized later that we did the partitiotion on nullable column.

    Do we need to make it to notnull and redo the partitiotion ? OR any other options ?

  • March 6, 2016 at 6:15 pm

    #1863386

    RUNNERSQL (3/6/2016)

    yes, we realized later that we did the partitiotion on nullable column.

    Do we need to make it to notnull and redo the partitiotion ? OR any other options ?

    Not currently enough information...

    1. How many rows in the table?

    2. How many columns?

    3. What datatype are the columns?

    4. What is the Primary Key and what column have you clustered on?

    5. What is the datatype of the clustering column and what type of data is in the clustering column?

    6. Is the data in the clustering column "immutable" (written once and never changed)? If not, why not?

    7. Why was the table partitioned to begin with?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 7, 2016 at 11:18 pm

    #1863617

    Do we need to make it to notnull and redo the partitiotion ? OR any other options ?

    Alter the column to non-nullable

    Alter the table to add primary key on the table including the partitioned column.

    Also if you want to take advantage to partition escalation,

    then set the auto_escalation level to AUTO

  • March 8, 2016 at 10:34 am

    #1863765

    er.mayankshukla (3/7/2016)

    Alter the column to non-nullable

    That's the right idea but they also need to ensure that the data in the partitioning column will NOT change... EVER. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 6 posts - 1 through 5 (of 5 total)

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