c# - SQL Server Sequence Gaps -

i have sequence used set transaction folio of table:

create sequence [seq].[folio]   [bigint]  start 114090  increment 1  minvalue -9223372036854775808  maxvalue 9223372036854775807  cache

today curiousity did a:

select folio  transactions order folio desc

and surprise there gaps, there missing folios in table.

example:

that means happening. give more information, insert stored procedure used has following before insert into...

declare @numfolio int  select @numfolio = next value  seq.folio

when saving information application used database transactions, if goes app commit transaction , if not rollback transaction.

i think origin of problem transaction, when there error next value of sequence has been generated , rollback has no effects on that.

any clue how solve in order have perfect sequence without gaps?

so, there few things should understand sequence.

it's not transactional, yes, once transaction retrieves value, rolling not restore it.
the values sequence allocated in batches, have cache size set 10, grab 1 value, restart server, there gap of 10.

as how perfect sequence, well, way getting max value table in serializable transaction. question should ask "do need sequential?".

Parth Code