Azure sql database (SQL MI) partitioning

sakuraime 2,321 Reputation points
2021-01-22T04:34:07.95+00:00

May I know if Azure sql database (SQL MI) partitioning the table, although it's in the Primary file group , will it seperate the partition data into different files at the backend ?

and it's it support parallel execution ?

Azure SQL Database
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Martin Cairney 2,241 Reputation points
    2021-01-22T07:30:10.737+00:00

    Partitioning never places specific partitions of the data on specific files UNLESS you have multiple filegroups - the files that make up a file group have a proportional fill algorithm on where the data is located.

    For Managed Instance - you can ONLY have the PRIMARY file group - but for performance you can have many files under this.

    With partitioning, the partition elimination will still work with Managed Instance and the optimiser will create a plan that eliminates partitions in the same way SQL Server does. In the same way that SQL Server does, it can also generate a parallel plan that will assign threads to each partition. But this is the SAME as SQL Server - so if you achieved it with your specific tables, data and queries then you should be able to achieve the same with Managed Instance