SQL Server - Rename a column in a ledger enabled table.

Question

Hello Everyone,

I would like to know whether renaming a column is supported in table which has ledger capability.
If yes then please let me know how to achieve it?
otherwise Is there a plan to support this in future?

ref: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-ledger-column-history-transact-sql?view=azuresqldb-current
The above doc gives a hint that renaming might be supported for the ledger tables.

Thank you in advance

Answer 1

This is really and Azure SQL question. I suggest you change your tag to "azure-sql-database" for a proper answer.

Answer 2

Yes, you can do that. Demo below.

CREATE TABLE KeyCardEvents
(
    EmployeeID INT NOT NULL,
    AccessOperationDescription NVARCHAR (MAX) NOT NULL,
    [Timestamp] Datetime2 NOT NULL,
    StartTransactionId BIGINT GENERATED ALWAYS AS TRANSACTION_ID START HIDDEN NOT NULL,
    StartSequenceNumber BIGINT GENERATED ALWAYS AS SEQUENCE_NUMBER START HIDDEN NOT NULL
)
WITH (
    LEDGER = ON (
        LEDGER_VIEW = dbo.[KeyCardEventsLedger] (
            TRANSACTION_ID_COLUMN_NAME = TransactionId,
            SEQUENCE_NUMBER_COLUMN_NAME = SequenceNumber,
            OPERATION_TYPE_COLUMN_NAME = OperationId, 
            OPERATION_TYPE_DESC_COLUMN_NAME = OperationTypeDescription
        ),
        APPEND_ONLY = ON
    )
);
go
EXEC sp_rename 'KeyCardEvents.Timestamp', 'WhenItHappened', 'COLUMN'
go
DROP TABLE KeyCardEvents