question

rudrapbiswas avatar image
rudrapbiswas asked

How to rename (logical) table online

Looking for some thoughts on design table renaming. Looking for ways to avoid needed to the ability to rename tables online. Basically, we have a 2 tables. One is being used by the app for querying (active), and one is used by ETL to prepare the next data set (staging). Once the staging table is all loaded, logically, they rename the table from staging to active and active to staging, so the app sees the freshest data (I am glossing over details). How can we avoid that? The use case is we will load data into _temp_ table, rename target table to _bak_ table, rename _temp_ table to target table, and finally we will truncate _bak_ table. It is to minimize data access downtime while we refresh table data.

sql-servertabletable-designeronline
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered

Use a synonym. It's a metadata operation to re-point a synonym at another table, so will be fast.

  • Have the app read from a synonym, which is pointed at Table1.
  • Load new data into Table2.
  • Switch synonym to point to Table2.
  • At next load time, load data in Table1, switch synonym to point to Table1.

This approach does however require you to know which is the live table and which isn't.

6 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rudrapbiswas avatar image rudrapbiswas commented ·

How much downtime, do you expect on this? I mean to shift the synonyms? And btw, to use synonyms, I need to have a separate db, right?

0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·

I would expect this to be measurably instantaneous. And no you don't need a separate DB - why do think this?

0 Likes 0 ·
rudrapbiswas avatar image rudrapbiswas Kev Riley ♦♦ commented ·

in the same db, you want me to create synonyms or view?

0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ rudrapbiswas commented ·

So it looks like you are already using synonyms - you'll need to find out what the base tables are and make the synonym test111 reference that instead

0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.