RBarryYoung (2/28/2010)
Paul: So on RollBacks, will it leave permanent gaps, try to recycle the gaps, or does it guarantee no gaps, ever? (I am pretty sure that this last choice cannot be done, by anything, without some kind of frequent deadlocking or severe blocking). 😀
Like IDENTITY, it leaves gaps on ROLLBACK - as you say, all non-blocking methods do.
It is, however, possible to avoid gaps if a 'pool' of pre-allocated keys is cached and managed by a separate component.
That is an implementation detail to be handled by the application.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi