SQL Server Table Partition help

Question

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.

Answer 1

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.

Answer 2

Hi @Srinivas K ,

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

Answer 3

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.