How to find the identity column seed value?

[lizou]

How to find the identity column seed value? A seed value is required when we need specifically like start generating new values from a number (most likely we need to keep the original key values when data is reloaded from another source, and any new data need starts from the current max value +1)

new from runtime 10.4

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( [ START WITH start ] [ INCREMENT BY step ] ) ]

I have done searching on documents, and there is no result.

in sql server, the equivalent statement is

-- check current identity seed value

DBCC CHECKIDENT ('dbo.table_with_identity_col');

[Hubert-Dudek · Accepted solution]

When you go to source delta files on the dbfs, you can see that in JSON and CRC, there is field delta.identity.highWaterMark, which contains max identity value. I couldn't find any way to get that value in SQL. Of course, you can aggregate MAX. I asked the Delta team if there a way to get highWaterMark

[lizou]

found it, thanks!

of course, it will be nice to have a sql function available to query the value.

example

\"delta.identity.start\":984888,\"delta.identity.highWaterMark\":1004409,\"comment\":\"identity\",\"delta.identity.step\":1}