SQL Server - deleting and re-creating a table - blocking access at run time

Question

I need to drop and recreate the table that exists to "cache" the expensive view. The view may change, and I want to make the maintenance as simple 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 if the procedure tries to access the table while it is in the middle of a fall and recreate. I am using a transaction, but I'm not sure if this will work in the table "reset" for this split second, that it does not exist.

I performed a basic test, 30 x SELECT from a view in a loop, when you run the drop / rereate view. There are no errors yet.

I looked at Truncate / Delete with an insert, but potentially changing columns in the view in the future require me to keep this as flexible as possible, and fixed columns will not help with this.

Can someone tell me if the transaction will protect the table from read access when it is deleted, is it safe, or if there is a better way?

Code Drop / Restore:

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 request after Brads response:

 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

I am using the process I came with to overwrite drop-flip-flop . I used this process with great success when creating a table takes some time, and I do not want all applications / users who need a table to be delayed during this rebuild process.

• Create a new version of the table with some preliminary fix (e.g. <TableName>_New )
• Drop the existing item (i.e. DROP TABLE <TableName> )
• 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 the task.

---

* NOTE: . To take full advantage of this process, you will need to create any indexes that you need in the new table between steps 1 and 2. This means that you will have to pre / post -fix them as well and rename them along with the table to avoid problems restarting the script.

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 really prevents errors. I have never encountered any problems without using a transaction.

Answer 2

This will work, but as @Brad notes, you may have a long lock period when the table is rebuilt. Creating it new / drop old / rename will work just fine.

The other trick I used is based on views:

• You have two persistent tables: MyTable0 and MyTable1
• You save 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 is updated, you upload your data to an offline table,
• run ALTER VIEW to refer to the current current table (as a single statement, it is enclosed in an implicit transaction, so you may not need to fuss with the declared transactions),
• and update the indicator accordingly ( Indicator = (Indicator + 1) % 2 )

(I once had a four-table system based on this idea, for past data, current data, future data available to premium clients, and “waiting for the next download”.)

Answer 3

I will reveal my comment as the complete answer: before implementing drop-flip-flop, I first check to see if it is possible to simply index the view:

Indexed Views in SQL 2000
Indexed Views in SQL 2005
Indexed Views in SQL 2008

Basically, if a view satisfies a certain set of conditions, you can simply add a clustered index to the view and actually save a physical copy of the results of the view, which are updated every time the base tables are changed.

In other words, SQL does all the work for you that you do now manually.

Unfortunately, not all views are materializable. An indexed view cannot contain:

• Distinct
• View tables or subqueries
• Exists / does not exist
• UNION
• TOP
• OUTER union
• TO ORDER

and many other things that you can read on the links above.

Answer 4

Set the transaction isolation level to serialized. This means that when you start a transaction, locked lines are executed so that others cannot read them until tranny finishes