SQL Server - Drop and Recreate a Table - Blocking Access While Doing So

Question

Hi,

I need to drop and recreate a table, which exists to "cache" an expensive view. The view may change and I want to make maintenance as easy as possible, so I want the new table to reflect the latest version of the view.

I also want to be able to prevent read errors should a procedure try to access the table while it is in the middle of being dropped and recreated. I'm using a transaction, but I'm not sure if that will work on a 'dropped' table for that split second that it doesn't exists.

I've done a basic test, 30 x SELECT's from the view in a loop while running the drop / recreate view. No errors so far.

I have considered a Truncate / Delete with insert, but the potentially changing columns on the view in the future require that I keep this as flexible as possible, and fixed columns wont help with this.

Can anyone tell me if the transaction will protect the table from read access while being dropped and this is safe, or if there is a better way?

Drop / Recreate Code:

BEGIN TRAN

    BEGIN TRY

        DROP TABLE Persisted_View_1

        SELECT * INTO Persisted_View_1

        FROM View_1

    END TRY
    BEGIN CATCH

        RAISERROR('The procedure proc_PersistView1 failed to commit, the transaction was rolled back', 16, 1)

        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRAN
        END

    END CATCH

        IF @@TRANCOUNT > 0
        BEGIN
            COMMIT TRAN
        END

    GO

UPDATE: Revised query following Brads Answer:

ALTER PROCEDURE proc_Drop_Recreate_Persisted_View_MyData

AS
BEGIN

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    BEGIN TRAN

        BEGIN TRY

            -- Re create

            SELECT * INTO Persisted_View_MyData_Temp FROM View_MyData

            -- Create index on product ID

            CREATE CLUSTERED INDEX [IX_ProductID_ProductTypeID] ON [dbo].[Persisted_View_MyData_Temp] 
            (
                [productID] ASC,
                [productTypeID] ASC
            )
            WITH 
            (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

            -- Check and drop table

            IF EXISTS ( SELECT Id FROM sysObjects WHERE Name like  'Persisted_View_MyData' ) 
            BEGIN           
                DROP TABLE Persisted_View_MyData     
            END         

            EXEC sp_rename 'Persisted_View_MyData_Temp', 'Persisted_View_MyData'

        END TRY
        BEGIN CATCH

            RAISERROR('The procedure proc_PersistViewMyData failed to commit, the transaction was rolled back', 16, 1)

            IF @@TRANCOUNT > 0
            BEGIN
                ROLLBACK TRAN
            END

        END CATCH

            IF @@TRANCOUNT > 0
            BEGIN
                COMMIT TRAN
            END


END

Answer 1

Set the transaction isolation level to serialised. This means that when you start a transaction then the rows that are locked are done so in a way that others cannot read them until the tranny finishes

Answer 2

I use the process which I've come to dub the drop-flip-flop. I have used this process with great success when creating the table takes some time and I don't want all the applications/users needing the table to be held up during this rebuild process.

1. Create the new version of the table with some pre/post-fix (e.g. <TableName>_New)
2. Drop the existing item (i.e. DROP TABLE <TableName>)
3. Rename the new table to the name of the original table (EXEC sql_rename...) [REF]

I usually create a stored procedure with this logic and schedule it in a job.

---

*NOTE: To take full advantage of this process, you'll need to create any indexes you need on the new table between step 1 and 2. This means that you'll have to pre/post-fix them also and rename them along with the table to avoid running into problems when the script runs again.

For added security, you can create a transaction around steps 2 and 3. Setting the isolation level to Serialized will make it the safest, but I have no experience if it will actually prevent errors. I have never run into any problems without using a transaction.

Answer 3

This will work, but like @Brad points out, you could have an extended period of blockage while the table is being rebuilt. His create new / drop old / rename would work pretty well.

Another trick I've used is based on views:

• You have two permanent tables, MyTable0 and MyTable1
• You store an indicator somewhere indicating which table is "current", and which is offline
• You have a view defined against the "current" table (as simple as CREATE VIEW MyTable as SELECT * from MyTable0)
• When it's time to update, you load your data into the offline table,
• run an ALTER VIEW to reference the now-current table (as a single statement, it's wrapped in an implicit transaction, so you might not need to fuss with declared transactions),
• and update the indicator accordingly ( Indicator = (Indicator + 1) % 2 )

(I once had a four-way table system based on this idea, for past data, current data, future data available to premium customers, and "waiting for next load".)

Answer 4

I'll expand my comment as a full answer: Before implementing the drop-flip-flop, I would check first to see if you can just index the view:

Indexed views in SQL 2000
Indexed views in SQL 2005
Indexed views in SQL 2008

Basically, if the view meets a certain set of conditions, you can simply add a clustered index to the view, and it actually saves a physical copy of the view results, which are updated every time the underlying tables are modified.

In other words, SQL is doing all the work for you that you are now doing manually.

Unfortunately, not all views are materializable. An indexed view can't contain:

• DISTINCT
• Derived Tables or Subqueries
• Exists/Not Exists
• UNION
• TOP
• OUTER Joins
• ORDER BY

and a bunch of other things you can read at the above links.