Azure SQL – Hyperscale service tier – are table partitions supported

All we need is an easy explanation of the problem, so here it is.

In the Azure SQL DB, running in the Hyperscale service tier, are table level partitions supported? It does not seem to be called out in the known limitations page.

While trying to create a filegroup in an Azure SQL Hyperscale Database via SSMS, which is the initial step towards creating a partition scheme, it threw an error:

Keyword or statement option ‘filegroup’ is not supported in this version of SQL Server.

So, either it is not feasible to do partitions or there must be some other way to create them.

  • Can someone clarify on whether partitions are supported?
  • If so, how to create them for tables?
  • How are the partition switch / new partition creation handled?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Partitioning is totally supported in Azure SQL Database, including the Hyperscale service tier.

However, adding additional filegroups is not supported in Azure SQL Database.

If you look at the docs for Files & Filegroups, the "Applies to" section says "SQL Server (all supported versions)" which actually refers to ONLY the traditional self-installed "running on a Server" flavor of SQL Server, and not Azure SQL Database. Adding additional filegroups to Azure SQL Database doesn’t work.

There are a number of features/syntaxes that don’t work in Azure SQL Database, which all throw Error 40517:

Keyword or statement option '%.*ls' is not supported in this version of SQL Server.

In the case of adding a filegroup, %.*ls gets replaced with filegroup when the error is thrown:

Msg 40517, Level 16, State 1, Line 4
Keyword or statement option 'filegroup' is not supported in this version of SQL Server.

For various data management reasons (spreading out IO, slower storage or archive data, piecemeal restores, etc), you may want to have separate filegroups for separate partitions. However, this isn’t a requirement to use partitioning, and you can just as easily set up partitioning with everything in the PRIMARY filegroup–this ends up being your only option in Azure SQL Database.

You can just skip the step of creating new filegroups and proceed with configuring partitioning by creating partition schemes & functions, then applying those to a table.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply