0

I am planning to keep a table online when data refreshes in background. (I am not planning on partitions at this time). I thought of two approaches.

   1. Schema swapping
   2. Alter table switch

Could someone suggest me which one is better or are both approaches are same?

I have a table called dbo.T1, needs to be online when data gets updated in dbo.T1

  1. Schema Swapping Implementation:

    create schema shadow; create schema swap; create table shadow.T1;

I will refresh data in shadow.T1

ALTER SCHEMA swap TRANSFER dbo.T1;
ALTER SCHEMA dbo TRANSFER shadow.T1;
ALTER SCHEMA shadow TRANSFER swap.T1;

In next load, shadow.T1 table data will be used in refresh as I am using incremental load approach.

  1. Alter table switch Implementation:

I use the same procedure as above, but I need to make sure target table must be empty.

In the above two approaches, SQL Server is just changing the meta data (I read in some Microsoft docs).

Is there any difference between these two approaches to solve my above problem perspective?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Narendra
  • 345
  • 4
  • 17
  • You can't alter table when it's locked by any background job. Also, what about FK's? I suggest to use a View and change the From statement everytime you want to change the source table. – XAMT Jan 28 '20 at 18:44
  • My mistake, I wasn't clear earlier, Tableau is not always connecting to the table, so I can alter the table. I can disable FKs. I think I can't use views and change "from" clause always, it becomes difficult to publish those changes to SSMS from visual studio@XAMT – Narendra Jan 28 '20 at 18:54
  • Could you tell me, is there any difference in above 2 methods, I mentioned, or both same ??@XAMT – Narendra Jan 28 '20 at 18:57

0 Answers0