Search code examples
sql-serverazuredatabase-partitioning

Azure SQL Database Partitioning

I currently have an Azure SQL Database (Standard 100 DTUs S3) and I'm wanting to create partitions on a large table splitting a datetime2 value into YYYYMM. Each table has at least the following columns:

  • Guid (uniqueidentifier type)
  • MsgTimestamp (datetime2 type) << partition using this.

I've been looking on Azure documentation and SO but can't find anything that clearly says how to create a partition on a 'datetime2' in the desired format or even if it's supported on the SQL database type.

Another example if trying the link below, but I do not find the option to create a partition within SQL Studio to create a partition on the Storage menu.

https://www.sqlshack.com/database-table-partitioning-sql-server/

In addition, would these tables have to be created daily as the clock goes past 12am or is this done automatically?

UPDATE

I suspect I may have to manually create the partitions using the first link below and then at the beginning of each month, use the second link to create the next months partition table in advance.

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transact-sql?view=sql-server-ver15

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-partition-function-transact-sql?view=sql-server-ver15

Context

I currently connect into a real-time feed that feeds upto 600 rows a minute and have a backlog of around 370 million for 3 years worth of data.

Solution

  • Correct.

    You can create partitions based upon datetime2 columns. Generally, you'd just do that on the start of month date, and you'd use a RANGE RIGHT (so that the start of the month is included in the partition).

    And yes, at the end of every month, the normal action is to:

    • Split the partition function to add a new partition option.
    • Switch out the oldest monthly partition into a separate table for archiving purposes (presuming you want to have a rolling period of months)

    And another yes, we all wish the product had options to do this for you automatically.

    I was one of the tech reviewers on the following whitepaper by Ron Talmage, back in 2008 and 99% of the advice in it is still current: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd578580(v=sql.100)