我需要删除并重新创建一个表,该表用于“缓存”昂贵的视图.视图可能会更改,我希望尽可能简化维护,因此我希望新表能够反映最新版本的视图.
我还希望能够防止读取错误,如果一个过程在被删除和重新创建的过程中尝试访问该表.我正在使用一个事务,但是我不确定它是否会在一个’drop’表上工作,因为它不存在.
我在运行drop / recreate视图时,在循环中从视图中完成了一个基本测试,30 x SELECT.到目前为止没有错误.
我已经考虑过使用insert进行截断/删除,但是将来视图中可能更改的列要求我保持尽可能灵活,并且固定列不会对此有所帮助.
任何人都可以告诉我,如果事务在被删除时保护表不受读取访问,这是安全的,还是有更好的方法？
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
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,1)
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN
END
END CATCH
IF @@TRANCOUNT > 0
BEGIN
COMMIT TRAN
END
END
解决方法
我使用了我来配音丢弃触发器的过程.我在创建表需要一些时间时非常成功地使用了这个过程,并且我不希望在重建过程中所有需要该表的应用程序/用户都被占用.
>使用一些前/后修复创建表的新版本(例如< TableName> _New)
>删除现有项目(即DROP TABLE< TableName>)
>将新表重命名为原始表的名称(EXEC sql_rename …)[REF]
我通常用这个逻辑创建一个存储过程并在一个作业中安排它.
*注意：要充分利用此过程,您需要在步骤1和步骤2之间在新表上创建所需的任何索引.这意味着您还必须预先修复它们并将其重命名与表一起使用以避免在脚本再次运行时遇到问题.
为了增加安全性,您可以围绕步骤2和3创建事务.将隔离级别设置为Serialized将使其最安全,但我没有经验,如果它实际上将防止错误.没有使用交易,我从未遇到过任何问题.
