SQL-Server: Neue Spalte an bestimmter Position in Tabelle einfügen

(SQL-Server: Insert column at a special position in a table)

In seltenen Fällen kann es doch mal notwendig sein, dass man eine neue Spalte an einer bestimmten Position in einer Tabelle einfügen und diese nicht einfach am Ende anfügen will. Leider gibt es es hierfür keine “einfache” Funktion. Über das SQL Server Management Studio (SSMS) gibt es zwar diese Möglichkeit, jedoch geht es nur, wenn die betroffene Tabelle noch nicht gefüllt ist oder gelöscht werden darf.

Wie geht das Management Studio aber (grob gesagt) intern vor?
1. Die betroffene Tabelle wird mit neuen Spalten als temporäre Tabelle erstellt.
2. Die Inhalte werden aus der originalen Tabelle in die temporäre Tabelle kopiert.
3. Die originale Tabelle wird gelöscht.
4. Die temporäre Tabelle wird umbenannt und als permanente Tabelle bereitgestellt.

Genau nach dieser Vorgehensweise habe ich nun ein SQL-Skript geschrieben, welches eine neue Spalte an einer beliebigen Position einer Tabelle einfügt und dabei keine Daten verliert.

USE [Spielwiese]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

BEGIN TRY

    BEGIN TRAN TransactionInsertColumn;

    -- Create a new table with the additional column (DescriptionShort)
    CREATE TABLE [dbo].[Product_NEW] (
        [ID]                       INT           IDENTITY (1, 1) NOT NULL,
        [Name]                     VARCHAR (50)  NOT NULL,
        [DescriptionShort]        VARCHAR (50)  NULL,
        [Description]             VARCHAR (255) NULL
    );

    SET IDENTITY_INSERT [Product_NEW] ON;
    
    -- Copy all data from the original table to the new (extended) one
    INSERT INTO [Product_NEW]
               ([ID]
               ,[Name]
               ,[DescriptionShort] -- This is the new inserted column
               ,[Description]
               )
         SELECT [ID]
                ,[Name]
                ,NULL -- Content of the new column is NULL (or can set here)
                ,[Description]                
      FROM [Product]

    SET IDENTITY_INSERT [Product_NEW] OFF;

    -- Rename the original table to _OLD
    EXEC sp_rename 'Product', 'Product_OLD';

    -- Rename the new table to the original name
    EXEC sp_rename 'Product_NEW', 'Product';

    -- Drop the old table
    DROP TABLE Product_OLD;

    -- Optional: Create index here
    CREATE NONCLUSTERED INDEX [ProductIndex1]
        ON [Product]([Id] ASC);    
    
    -- Optional: Create primary key here
    ALTER TABLE [Product]
        ADD CONSTRAINT [Product_PK] PRIMARY KEY CLUSTERED ([ID] ASC);

    -- Optional: Create foreign keys here
    --ALTER TABLE [Product]
    --    ADD CONSTRAINT [FKProductHeaderID] FOREIGN KEY ([FKProductHeader]) REFERENCES [ProductHeader] ([ProductID]);
    
    -- NOTE:
    -- All rights (grant/deny) are lost and have to set new
    --GRANT SELECT ON [Product] TO "RoleReadOnly";
    --GRANT SELECT ON [Product] TO "RoleReadWrite";
    
    COMMIT TRAN TransactionInsertColumn;
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE(), ERROR_NUMBER() -- Show the last error message if the transaction was aborted
    ROLLBACK TRAN TransactionInsertColumn
END CATCH