5

I have a handful or so of permanent tables that need to be re-built on a nightly basis.

In order to keep these tables "live" for as long as possible, and also to offer the possibility of having a backup of just the previous day's data, another developer vaguely suggested taking a route similar to this when the nightly build happens:

  1. create a permanent table (a build version; e.g., tbl_build_Client)

  2. re-name the live table (tbl_Client gets re-named to tbl_Client_old)

  3. rename the build version to become the live version (tbl_build_Client gets re-named to tbl_Client)

To rename the tables, sp_rename would be in use.
http://msdn.microsoft.com/en-us/library/ms188351.aspx

Do you see any more efficient ways to go about this, or any serious pitfalls in the approach? Thanks in advance.

Update

Trying to flush out gbn's answer and recommendation to use synonyms, would this be a rational approach, or am I getting some part horribly wrong?

Three real tables for "Client":
1. dbo.build_Client
2. dbo.hold_Client
3. dbo.prev_Client

Because "Client" is how other procs reference the "Client" data, the default synonym is 

CREATE SYNONYM         Client  
FOR           dbo.hold_Client

Then take these steps to refresh data yet keep un-interrupted access.
(1.a.) TRUNCATE dbo.prev_Client (it had yesterday's data)
(1.b.) INSERT INTO dbo.prev_Client the records from dbo.build_Client, as dbo.build_Client still had yesterday's data

(2.a.) TRUNCATE dbo.build_Client
(2.b.) INSERT INTO dbo.build_Client the new data build from the new data build process
(2.c.) change the synonym

DROP SYNONYM           Client
CREATE SYNONYM         Client  
FOR          dbo.build_Client

(3.a.) TRUNCATE dbo.hold_Client
(3.b.) INSERT INTO dbo.hold_Client the records from dbo.build_Client
(3.c.) change the synonym

DROP SYNONYM          Client
CREATE SYNONYM        Client  
FOR          dbo.hold_Client
mg1075
  • 17,985
  • 8
  • 59
  • 100

4 Answers4

5

Use indirection to avoid manuipulating tables directly:

  • Have 3 tables: Client1, Client2, Client3 with all indexes, constraints and triggers etc
  • Use synonyms to hide the real table eg Client, ClientOld, ClientToLoad
  • To generate the new table, you truncate/write to "ClientToLoad"
  • Then you DROP and CREATE the synonyms in a transaction so that
    • Client -> what was ClientToLoad
    • ClientOld -> what was Client
    • ClientToLoad -> what was ClientOld

You can use SELECT base_object_name FROM sys.synonyms WHERE name = 'Client' to work out what the current indirection is

This works on all editions of SQL Server: the other way is "partition switching" which requires enterprise edition

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Thanks, I'd never heard of synonyms before. This article was helpful. http://www.developer.com/db/article.php/3613301/Using-Synonyms-in-SQL-Server-2005.htm Is this still a good idea, though, if one of these tables could have millions of records? In that case, maybe it's not worth it to try to retain a backup of yesterday's data? – mg1075 Jul 28 '11 at 16:47
  • @mg1075: It's only millions and won't be used: it'll just sit on disk. – gbn Jul 28 '11 at 17:00
  • I think I'd like to try this route, but I'm getting tripped up on the meaning of what you intended to do at the point of "DROP and CREATE the synonyms in a transaction...". So for the Client synonym, DROP SYNONYM Client and then CREATE SYNONYM Client FOR MyDatabase.dbo.Client2 ? – mg1075 Jul 28 '11 at 17:09
  • 1
    @mg1075: yes. Also see http://stackoverflow.com/questions/6268341/how-do-i-periodically-rebuild-a-reporting-table-that-is-very-frequently-accessed/6268753#6268753 and http://stackoverflow.com/questions/4166989/alternate-synonym-in-sql-server-in-one-transaction/4167746#4167746 – gbn Jul 28 '11 at 17:17
  • If the tables have primary keys, would it be best to drop the primary keys after TRUNCATE/before INSERT, and then add the PK's back after data insertion has completed? http://www.sqlservercentral.com/Forums/Topic968793-338-1.aspx – mg1075 Jul 29 '11 at 01:15
  • @mg1075: I tend not to to keep it simple. It may only save a few seconds – gbn Jul 29 '11 at 09:05
  • 1
    I'm going to try to go with the synonym approach. If you see something awful in my updated question, please inform; thanks. – mg1075 Jul 29 '11 at 13:42
1

Except of missing step 0. Drop tbl_Client_old if exists solutions seems fine especially if you run it in explicit transaction. There is no backup of any previous data however.

The other solution, without renames and drops, and which I personally would prefer is to:

  1. Copy all rows from tbl_Client to tbl_Client_old;
  2. Truncate tbl_Client.
  3. (Optional) Remove obsolete records from tbl_Client_old.

It's better in a way that you can control how much of the old data you can store in tbl_Client_old. Which solution will be faster depends on how much data is stored in tables and what indices in tables are.

Petr Abdulin
  • 33,883
  • 9
  • 62
  • 96
1

Some things to keep in mind:

  1. Replication - if you use replication, I don't believe you'll be able to easily implement this strategy
  2. Indexes - make sure that any indexes you have on the tables are carried over to your new/old tables as needed
  3. Logging - i don't remember whether or not sp_rename is fully logged, so you may want to test that in case you need to be able to rollback, etc.

Those are the possible drawbacks I can think of off the top of my head. It otherwise seems to be an effective way to handle the situation.

Derek
  • 21,828
  • 7
  • 53
  • 61
  • FYI: In FULL recovery, or in an explicit transaction, sp_rename is fully logged. Since SQL Server 2000, "minimally logged" when in FULL mode *does not exist": *everything* is logged and can be rolled back – gbn Jul 28 '11 at 07:18
0

if you use SQL Server 2008, why can't you try to use horisontal partitioning? All data contains in one table, but new and old data contains in separate partitions.

Alex_L
  • 2,658
  • 1
  • 15
  • 13