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
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.
<TableName>_New
)DROP TABLE <TableName>
)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.
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:
CREATE VIEW MyTable as SELECT * from MyTable0
)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".)
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:
and a bunch of other things you can read at the above links.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With