Wqnr8sKT

1GO
2PRINT N'Starting rebuilding table [dbo].[MyTable]...';
3
4
5GO
6BEGIN TRANSACTION;
7
8SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
9
10SET XACT_ABORT ON;
11
12CREATE TABLE [dbo].[tmp_ms_xx_MyTable] (
13    [Id] BIGINT IDENTITY (1, 1) NOT NULL,
14    -- etc, other columns
15);
16
17IF EXISTS (SELECT TOP 1 1 
18           FROM   [dbo].[MyTable])
19    BEGIN
20        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_MyTable] ON;
21        INSERT INTO [dbo].[tmp_ms_xx_MyTable] ([Id], ...)
22        SELECT   [Id],
23                 -- etc, other columns
24        FROM     [dbo].[MyTable]
25        ORDER BY [Id] ASC;
26        SET IDENTITY_INSERT [dbo].[tmp_ms_xx_MyTable] OFF;
27    END
28
29DROP TABLE [dbo].[MyTable];
30
31EXECUTE sp_rename N'[dbo].[tmp_ms_xx_MyTable]', N'MyTable';
32
33COMMIT TRANSACTION;
34
35SET TRANSACTION ISOLATION LEVEL READ COMMITTED;