Does Partitioned Table require Clustered Index?

Question

Hi, I'm currently creating a partition table by creating new table on partition schema and then copy data from existing table to the new partitioned table, I'm using date column as partition key column.

does partitioned table require clustered index? fyi, the table doesn't have primary key

and if it require the clustered index, should I use the partition key column (date) in that clustered index?

or should I just create nonclustered non-unique index on my partition key column?

Answer 1

Table partitioning does not require a clustered index but I suggest one create a clustered index on SQL Server tables unless there is a specific reason to organize the table as a heap.

The clustered index does not need to be a primary key nor does it need to be unique. SQL Server will implicitly add the partitioning column as a clustered index key for non-unique clustered indexes if it's not already an explicit key column.

A non-unique clustered on your date column will be especially appropriate If data are incrementally inserted into the table by date and queries specify date ranges in WHERE clauses without additional criteria. Additional non-clustered indexes may be needed to optimize queries for other criteria.