GeneratePaddedSequentialNumbers.sql

/*
Purpose:
- Preview or assign deterministic, zero-padded sequence values to existing rows.

Safety:
- Changes data only when @ApplyChanges = 1. The default execution rolls back.

Requirements:
- SQL Server 2012 or later.

Customization:
- Replace dbo.SampleEntity, entity_id, and display_sequence.
- Choose a stable ORDER BY column; never use ORDER BY (SELECT NULL) for persisted numbering.
*/

SET XACT_ABORT ON;

DECLARE @ApplyChanges BIT = 0;

BEGIN TRANSACTION;

;WITH NumberedRows AS
(
    SELECT
        entity_id,
        RIGHT(REPLICATE('0', 6) + CONVERT(VARCHAR(20), ROW_NUMBER() OVER (ORDER BY entity_id)), 6) AS padded_number
    FROM dbo.SampleEntity
)
UPDATE target
SET display_sequence = numbered.padded_number
OUTPUT
    inserted.entity_id,
    deleted.display_sequence AS previous_value,
    inserted.display_sequence AS proposed_value
FROM dbo.SampleEntity AS target
JOIN NumberedRows AS numbered
    ON numbered.entity_id = target.entity_id;

IF @ApplyChanges = 1
    COMMIT TRANSACTION;
ELSE
    ROLLBACK TRANSACTION;