What is equivalent of Oracle sub-partitions in Ms sql server?

Question

Dear Team,

We are migrating to Azure SQL server from Oracle on-premise server.

In Oracle, we have table structures which are partitioned on col_1 and then sub-partitioned on col_2.

How can I achieve partitioning and sub-partitioning in MS SQL Server?

Thanks.

Answer 1

SQL Server has partitioning on a single column only. (Well, if you wanted to partition on a two-column combo, you could possibly partition on a computed column that covers both column, but it does not strike to me as a bad idea.)

Also beware that features like this may work considerably different on different products, so what maybe was a good idea on Oracle, may not be a good idea on SQL Server.

Exactly what do you hope to achieve with your partitioning?

Answer 2

Hi @Soniya Sharma

Not an expert of Oracle, but as Erland answered above, SQL Server’s table partitioning only allows for a single column to be used as a partitioning key. However, it is possible to use a computed column for the partitioning key. It could be something similar to a combination of a date and a warehousing code (e.g. “ASDF:20120810”).

Please refer to this doc for more details: Partitioned tables and indexes.

Also, check this SQL Server table partitioning resources page for more information on Table Partitioning.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.