Suppose I have an equities pricing table with a 1000 million rows. I need to be able to maintain consistency, read and write to it at high speed with a large number of clients.

What I'd like is a partition that splits the table into two. An 'archive partition' that has a clustered index and has the older (by time) 90% of the data. I'd also like a 'recent partition' that contains the most recent 10% with a non-clustered index.

I'm not sure if this is possible.

My question is: Is it possible to have partition in SQL Server 2016 with both clustered and non-clustered indexes on different partitions?


I don't think it's possible. The notion seems to fly in the face of everything that we know about partitioning and clustered indexes, but I might be wrong.

One possibility is a partitioned view over two tables, one with a clustered index, one as a heap. I have included a demo script for this, but it does come with a number of limitations which might be critical:

  • inserts seem to need to explicitly insert into each column
  • the heap table still needs a primary key which includes the partitioning column (even if the primary key is not clustered)
  • moving data from 'recent' to 'archive' involves moving from one table to another

Overall your situation is probably complex enough that it needs a much more considered analysis.

Use tempdb;

Create Table TestOld (
    id int,
    partition_column char(1) Constraint chk_partition_old Check (partition_column = 'O'),
    other_column char(100),
    Constraint pTestOld Primary Key Clustered (partition_column, id));

Create Table TestNew (
    id int,
    partition_column char(1) Constraint chk_partition_new Check (partition_column = 'N'),
    other_column char(100),
    Constraint pTestNew Primary Key Nonclustered (partition_column, id));

Create View Test As
Select id, partition_column, other_column
  From TestOld
Union All
Select id, partition_column, other_column
  From TestNew;  

Insert Into Test (id, partition_column, other_column)
Values  (1, 'O', ''),
        (2, 'N', '');

Select *
  From Test
  Where partition_column = 'N';

A second option which may have more merit in your case is a filtered index which you could add over the old part of your table and include a large number of columns so that it covers the queries that you do on the old part of the table (this would serve to speed up reads to the old part of the table but wouldn't slow writes in the recent part).

  • Thanks - the filtered index is nearly there but can't do rolling date ranges so is a deal breaker.
    – hawkeye
    Nov 27 '16 at 11:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.