Sql-server – Partition switching vs schema transfer

azure-sql-databasepartitioningschemasql servert-sql

I'm designing a database whose data will be generated daily by an ETL.
After some research, I found that one can use partition switching or schema transfer to maximize the uptime of the "live" data. Just for completeness of information, this database will be deployed on Azure SQL Database and developed on SQL Server 2019 Developer Edition on Docker. It consists in ~ 20 tables, each one having less than 10M rows (expected).

So now in my design there are two schemas:

  • [App]: containing multiple tables (w/o foreign keys) with the live data used by the application.
  • [AppShadow]: containing the exact same tables as the [App] schema (but w/ foreign keys), usually empty, used as destination for the ETL.

My flow looks like this:

  • Perform the ETL using the tables in the [AppShadow] schema as a destination. The foreign keys ensure that my data is consistent.
  • set transaction isolation level serializable; begin transaction;
  • Disable all the constraints on the tables in the [AppShadow] schema using alter table [AppShadow].[<table_name>] nockeck constraint all;
  • Truncate all the tables in the [App] schema
  • Switch the partition of all the tables from [AppShadow] to [App]
  • Re-enable all the constraints on the tables in the [AppShadow] schema using alter table [AppShadow].[<table_name>] with check check constraint all;
  • commit transaction;

This solutions seems to work fine and the downtime is minimal, but I can't figure out any advantage or disadvantage over transferring the entire tables between schemas using a [Swap] schema and the alter schema <schema_name> transfer <table_name> statement.
SQL Server being SQL Server, I'm pretty sure there are some hidden caveats that I'm unaware of that should let me prefer a solution over the other.

Do you have any advice or suggestion?

Best Answer

Do you have any advice or suggestion?

All the operations you described are metadata-only, so I wouldn't expect any significant difference in performance.

It is strange that you don't have Foreign Keys on your [App] schema. You've already paid the cost of checking the DRI, and if you have validated foreign keys in your database the Query Optimizer can take advantage of them.

Schema transfer would allow you to leave all the foreign keys in-place.