ALTER TABLE ... ALTER COLUMN .... SYNC IDENTITY not working anymore ?

[MDV]

Hello,

I recently noticed that the ALTER TABLE ALTER COLUMN SYNC IDENTITY command is no longer functioning as expected.

I have an IDENTITY column on my table:

D_Category_SID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1)

Previously, to reset the IDENTITY counter, I used the following steps:

TRUNCATE TABLE dw_cbi_dev.dbo.D_Category

INSERT INTO dw_cbi_dev.dbo.D_Category (D_Category_SID) SELECT 1 AS D_Category_SID

ALTER TABLE dw_cbi_dev.dbo.D_Category ALTER COLUMN D_Category_SID SYNC IDENTITY

TRUNCATE TABLE dw_cbi_dev.dbo.D_Category

This process used to reset the counter, so the next value started from 2. However, now the identity column continues from the last used value instead of resetting.

Can you clarify what has changed and suggest a solution? I’d prefer not to recreate the table, as that would result in losing existing permissions.

[Alberto_Umana · Accepted solution]

Hello @MDV,

Thanks for your question.

According to the recent updates, the SYNC IDENTITY command is now more restrictive and follows stronger invariants. Specifically, it no longer allows the high watermark to be reduced to ensure that there is no risk of assigning duplicate identity values. Here are the key points:

1. High Watermark Validation: The SYNC IDENTITY command now checks that the high watermark does not violate the start setting of the identity column and it will refuse to set the high watermark if it could lead to assigning duplicate values.
2. Rounding and Validations: The new enforcement ensures that the high watermark respects the identity column's start and step values, avoiding any erroneous assignments that could come from user-inserted data.
3. Behavior on Existing High Watermark: The system does not allow synchronization that would result in lowering the high watermark, thus maintaining stronger invariant properties for the identity columns.

Suggested Solution

1. Configuration Override: While not recommended for regular use, you can temporarily allow the lowering of the high watermark by setting identityColumn.allowSyncIdentityToLowerHighWaterMark.enabled = true. This should be done with caution due to the risk of assigning duplicate identity values.

Here is how you can set it:

SET identityColumn.allowSyncIdentityToLowerHighWaterMark.enabled = true;

ALTER TABLE dw_cbi_dev.dbo.D_Category ALTER COLUMN D_Category_SID SYNC IDENTITY;

After executing the necessary commands, make sure to reset the configuration:

SET identityColumn.allowSyncIdentityToLowerHighWaterMark.enabled = false;