Kenny... The first question is Is it worth optimizing this step? If the
answer is
yes, then you could possibly speed up the processing by returning a
sorted
sequence and writing your own algorithm doing a binary search looking
for
row number > ticket number where row number and ticket number are one
based.
This begs the question if your DB truly is guaranteed to create a
sequence.
Identity does _not_ quarantee a valid sequence. "The value automatically
produced with the Identity .... is not guaranteed to be consecutive. For
efficiency, a value is considered used as soon as it is presented to a
client."
So if a client starts an INSERT and then rolls back on error the number
is
_lost_ and a gap appears. If you want a consecutive sequence consider a
"next_number" table.
As for SQL.
http://www.developersdex.com/gurus/articles/115.asp
http://www.nigelrivett.net/FindGapsInSequence.html
http://www.tek-tips.com/faqs.cfm?fid=840
Finding gaps in an identity column sequence
Suppose a table has an identity column and gaps exist in the identity
sequence. Suppose you want to identify the gaps and fill them in. (I
don't
know why one would want to do this but we are just supposing. ) The
following T-SQL code will find the next gap in the sequence.
Create Procedure usp_FindNextPartID As
Declare @MinID int
Select @MinID = Min(PartID) From PartTable
If @MinID > 1
Select MinID=1
Else
Select MinID=min(a.PartId+1)
From PartTable a Left Join PartTable b
On a.PartId+1=b.PartId
Where b.PartNumber Is Null
Go
Regards,
Jeff
I have a DB which receive tickets in secuence 1,2,3,4,5.... I want to
create an routine that can detect if a ticket is missing so I just need
to
detect if the secuence is broken (1,2,3,5... 4 is missing)<