Re: Query to find first missing integer in a field

On Apr 7, 3:38 pm, DeanB <deanbrow..._at_yahoo.com> wrote:
> On Apr 7, 4:08 pm, ddf <orat..._at_msn.com> wrote:
>
> > On Apr 7, 2:51 pm, dean <deanbrow..._at_yahoo.com> wrote:
>
> > > Does anyone know a good way to find the first free integer number that
> > > does not exist in a table in a specified column? I can think of a
> > > couple of tedious ways of doing this but they are hardly elegant.
> > > Assume there could be any integer present up to 2 billion.
>
> > This has 'DON'T DO THIS' written all over it in large, bold, red
> > letters as it's a disaster in the making.  I suppose you're looking to
> > 'backfill' missing sequence numbers and that is a recipe for failure.
>
> > Seriously rethink this before you're in too deep and end up with an
> > application that won't run because it won't scale.
>
> > David Fitzjarrell
>
> Correct - the problem is that our consultants tend to poke around in
> the database and analyse data, and its far easier to do this if the ID
> numbers are 1234 rather than 158943758.

How so? Are your consultants such poor typists they can't type any number longer than 4 digits?

>
> Also, sequences are not reproducable, so debugging a series of SQL
> statements is sometimes a difficult process.

Sequences or not sometimes debugging a SQL statement is difficult, but hacking away at the sequence values on production data isn't the way to aid in troubleshooting. You're more likely to create more problems than you intended to solve.

David Fitzjarrell Received on Tue Apr 07 2009 - 15:47:02 CDT