How many temporal tables can be created in single database in Azure SQL Managed Instance

Praveenraj R K 61 Reputation points Microsoft Employee
2022-06-14T03:33:48.817+00:00

Hi All,

How many temporal tables can be created in one single database in Azure SQL Managed Instance ?
What will be the maximum size of one single temporal table ?
What will be the maximum size allowed for all temporal tables in one single database ?

Azure SQL Database
0 comments
Accepted answer
  1. Ronen Ariely 15,096 Reputation points
    2022-06-14T04:23:54.893+00:00

    Hi,

    Your title (and content of the message) speak about "Azure SQL Managed Instance" but your tags are sql-server-general and azure-sql-database which speak about different service. This make the question confusing!

    I will assume that you speak about 'Azure SQL Managed Instance'.

    Please confirm this, so we will fix the tags! Thanks.

    ----------

    (1) Please check the this document to get the Overview of Azure SQL Managed Instance resource limits and (2) the Maximum capacity specifications for SQL Server and (3) finally, make sure you checked the Temporal Table Considerations and Limitations

    As much as I understand, there is no specific limitation for Temporal tables in Azure SQL Managed Instance other than the general resource limitations mentioned in the link above. The Limitations for Temporal Tables (in addition to the resource limitations) are the same as on-premises.

    How many temporal tables can be created in one single database in Azure SQL Managed Instance ?

    Behind the scenes the temporal table includes the active table and the history table. As much as I know there is no strict limitation specific for number of temporal tables. the number of tables is limited by total number of objects in a database which is 2,147,483,647

    What will be the maximum size of one single temporal table ?

    Limited only by available resources.

    What will be the maximum size allowed for all temporal tables in one single database ?

    Again, Limited only by available resources.

    Note that if you use blob data types, such as (n)varchar(max), varbinary(max), (n)text, and image, then this can incur significant storage costs and low performance.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Praveenraj R K 61 Reputation points Microsoft Employee
    2022-06-14T05:08:23.473+00:00

    Thank you for the help !!!

    We have auto scaling options in SQL MI which automatically scale up whether database is in need of any resource ?
    If so how much downtime is required when auto scaling happens ?
    Also what happens to the concurrent connections to the databases and same transactions can be kept alive once auto scaling is done ?