sql-server - SQL Server - 删除并重新创建表 - 执行此操作时阻止访问

Question

我需要删除并重新创建一个表，该表的存在是为了“缓存”昂贵的 View 。 View 可能会发生变化，并且我希望维护尽可能简单，因此我希望新表能够反射(reflect)最新版本的 View 。

我还希望能够防止过程在删除和重新创建表时尝试访问该表时出现读取错误。我正在使用事务，但我不确定这是否适用于不存在的那一瞬间的“删除”表。

我已经完成了基本测试，在运行删除/重新创建 View 时从循环 View 中执行 30 x SELECT。到目前为止没有错误。

我考虑过使用插入截断/删除，但将来 View 上可能发生变化的列要求我保持尽可能的灵 active ，固定列对此无济于事。

谁能告诉我事务是否会保护表在被删除时不被读取访问并且这是安全的，或者是否有更好的方法？

删除/重新创建代码:

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

更新:根据 Brads 答案修改查询:

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

我使用的过程被称为drop-flip-flop。当创建表需要一些时间时，我使用此过程取得了巨大成功，并且我不希望在此重建过程中保留所有需要该表的应用程序/用户。

1. 通过一些前/后修复创建新版本的表格(例如 <TableName>_New )
2. 删除现有项目(即 DROP TABLE <TableName> )
3. 将新表重命名为原始表的名称 ( EXEC sql_rename... ) [REF]

我通常使用此逻辑创建一个存储过程并将其安排在作业中。

<小时/>

*注意:要充分利用此过程，您需要在步骤 1 和 2 之间在新表上创建所需的任何索引。这意味着您必须预先/post-fix 它们并将它们与表一起重命名，以避免在脚本再次运行时遇到问题。

为了提高安全性，您可以围绕步骤 2 和 3 创建事务。将隔离级别设置为 Serialized会让它最安全，但我没有经验是否它真的可以防止错误。在不使用事务的情况下，我从未遇到过任何问题。