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.
Sql-server – How to implement composite partition (Range list / Hash range) partition in SQL server
partitioningsql server
Related Question
- Sql-server – SQL Server Partitioning – what to use for partition key
- Sql-server – Partitioning in SQL Server 2008
- Sql-server – SQL Server Database Table Partitioning Consideration
- Sql-server – Problem with a partitioning switch script
- Postgresql – Postgres Partitioning: Edge case issue on date column partitioning
- Sql-server – Unable to create / Move data to New Partitions from the existing partition SQL Server 2014
- Sql-server – How to reduce table partition timing for existing table in SQL Server
Best Answer
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