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