question

SrinivasK-6333 avatar image
1 Vote"
SrinivasK-6333 asked Cathyji-msft commented

SQL Server Table Partition help

Hi All,

My name is Srinivas. I want some solution for my different scenario.

I want to make one of existing table to be partition. but this table have no index or primary or unique column. But it has DATE column. i want to create partitions based on month wise by using this date column. Is it require primary or unique column to create partition ?

if we want create partition is it change table structure ?

Do we have any links for crating table partition with out primary key or cluster index for existing SQL Server table.

could you please share your ideas or links or suggestions will be gr8 help to me.

sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I would tend to agree with Tom. But maybe you could tell us why you think you should implement partitioning?

0 Votes 0 ·

Hi @SrinivasK-6333,

We have not received a response from you. Did the reply(s) could help you? If the response helped, do "Accept Answer". If it is not, please let us know. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

0 Votes 0 ·
DavidBrowne-msft avatar image
0 Votes"
DavidBrowne-msft answered

Primary keys are not required for partitioning. But you might want to consider making the table a Clustered Columnstore instead of an un-indexed heap.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered TomPhillips-1744 edited

I highly recommend against table partitioning. Table partitioning is only helpful if you are using "swap in" or "swap out" of partitions. It is not for query performance tuning.

You are generally better off creating a file group with multiple files and using that instead.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @SrinivasK-6333,

I want to make one of existing table to be partition.

Whether it is a good choice for you to use table partitioning in your environment. Suggest you read the blog How To Decide if You Should Use Table Partitioning, then make a decision.

Is it require primary or unique column to create partition ?

The table itself can be partitioned on the datetime column. But the Partition Column has to be in the Primary key if you want to use the SWITCH abilities of the Partition feature. If it is not in the primary key you will get this error: Partition columns for a unique index must be a subset of the index key.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.