Sql-server – Is it possible to have partition in SQL Server 2016 with both clustered and non-clustered indexes on different partitions

clustered-indexnonclustered-indexpartitioningsql serversql-server-2016

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?

Best Answer

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));
Go

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

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

Related Question