Missing Identity Numbers

Question

Missing Identity Numbers

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

Perry Whittle (6/3/2015)
it's not a bug it's by design and yes, there is a trace flag to force the engine to behave as sql server 2008 R2. The trace flag is 272, here is the Microsoft Connect item link
https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity
Jeff Moden (6/2/2015)
This feature also affects SEQUENCEs.
Not if sequence caching is turned off

Yep. My point exactly. And, many thanks for the link, Perry. I really appreciate it. You saved me the time of looking for it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125560 More actions · Answer 2

The solution to this problem depends on why (or if) the rowset displayed to users really needs to contain a list of sequential numbers. Attempting to maintain the sequential integrity of the identity column with no gaps is a lost cause; it just doesn't work that way.

Instead of returning the identity column, consider returning a computed column based on the row_number() function, which is guaranteed to the sequential (within that resultset). The numbering can be based on the identity column or on something else like date/time. However, keep in mind that this is just a form of runtime ranking, and the same row isn't guaranteed to have the same row_number() for each query.

For example, let's assume you have an Orders table that contains order_id (identity) and order_date.

select row_number() over (order by order_id asc) order_seq

from Orders

order by order_id asc;

select row_number() over (order by order_date asc) order_seq

from Orders

order by order_date asc;

Or just continue returning the identity column, and tell the users that it's normal for there to be gaps in the row numbering.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

j-1064772 SSCertifiable Points: 5316 More actions · Answer 3

Eric M Russell (6/4/2015)
The solution to this problem depends on why (or if) the rowset displayed to users really needs to contain a list of sequential numbers. Attempting to maintain the sequential integrity of the identity column with no gaps is a lost cause; it just doesn't work that way.
Instead of returning the identity column, consider returning a computed column based on the row_number() function,

[font="Comic Sans MS"]I lost hope of a gap-less identity sequence a long time ago.

Your suggestion of using ROW_NUMBER() comes like a little slap in the back of the head - why didn't I thunk of that ! Very clever. Got to get out of that SSQL 2000 rut and seriously look at the new functions made available in later versions. Wake-up call.[/font]