I’m just inserting ‘9925203’ records into backup tables which is having SEQ_NUM
SEQ_NUM decimal(10,0) NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 1
INCREMENT BY 1
Why it is showing maxvalue is greather than actuall row count?
select count(1) from prod_olap_bkp.w_plrevn_op_a--9,925,203
Why it is weird behaving ?
becaus the increment by 1 is handled vproc local.
Identity columns are not guarantee a sequence - they only quarantee unique ids.
Each vproc request a range of numbers he can assign next. The size of the range is controlled via a dbs controll field - and don't even think of setting this to 1 as you would not have a MPP process any longer.
So assumumg the dbs controll field is stateing 1000 and you have 2 vprocs.
so the first vproc will start with 1 and the second with 1001.
If all numbers of the range assigned to the vproc are used he is requesting a new range (next would be 2000) etc.
So if you add only 100 rows per vproc you would see (in perferct world) values between 1 and 100 and 1001 and 1100.
P.S. read the documentation carefully before using identity columns. There are other issues as well which need to be considered...