Partitioning with null column

Question

Partitioning with null column

RUNNERSQL

SSC-Addicted

Points: 457
More actions
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

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

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

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)

RUNNERSQL SSC-Addicted Points: 457 More actions · Answer 2

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 ?

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

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)

er.mayankshukla SSCarpal Tunnel Points: 4190 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

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)