Our production database table already have a 18 partitions based on date range for a single state wise, records of around 200 million rows. Now we are planning to include multiple states with state ID and the record count will hit around 3 billions. Is it possible to implement List partition based on state id and create a sub partition based on date range in SQL server similar to Oracle. I am aware that SQL server will support only Range partition, is there any work around to implement like that? Do not want to create any tables based on state wise. ours is a single staging table and multiple objects depends on the table so any additional objects will be strictly avoided because it requires lot of dependency objects code change. Kindly help me to create composite partition similar to Oracle in SQL server 2012 enterprise edition.

  • 1
    Would the addition of a computed-persisted column be an option? - sqlpathy.com/2012/08/09/list-partitioning-in-sql-server Jun 29, 2017 at 12:43
  • I tried with range partition.
    – Kannan.C
    Jun 29, 2017 at 12:46
  • My requirement is first do the list based on state then do the range based on year. so If i am having 25 states with 4 year data then it will create 25 partitions and 4 sub partitions within the partition i.e 25 * 4 =100
    – Kannan.C
    Jun 29, 2017 at 12:54

1 Answer 1


My requirement is first do the list based on state then do the range based on year

That's not a requirement: that's a design. SQL Server doesn't do that.

Instead, you need to look to the data structure to provide the efficient access pattern within each partition.

If you use a clustered index, then within each partition SQL can perform efficient range scanning by the leading index columns.

And if you use a clustered Columnstore index SQL can use Rowgroup Elimination

  • Clustered columnstore isn't yet available on SQL Server 2012, but otherwise could be a good option. A limitation is that Microsoft hasn't yet provided sufficient tools for creating a columnstore index in an order that will maximize rowgroup elimination. See connect.microsoft.com/SQLServer/feedback/details/912452 for an active enhancement request. Jun 29, 2017 at 14:45
  • Yes. But time data tends to get good rowgroup elimination naturally, and you can always switch out a CCI partition, build a clustered index, build a CCI and switch back in. Jun 29, 2017 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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