topic Re: How to find the identity column seed value? in Data Engineering

How to find the identity column seed value?

lizou — Sun, 17 Apr 2022 05:27:26 GMT

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');

Re: How to find the identity column seed value?

Hubert-Dudek — Sun, 17 Apr 2022 18:19:05 GMT

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

Re: How to find the identity column seed value?

lizou — Sun, 17 Apr 2022 21:51:10 GMT

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}