Recycle Identity field value when it reaches its max

How about you cycle it back to the negative limit?? I never understand why
humans feel the need to start with 1 for identities - it loses them 50% of
the possible values. Guess it is just one of those things.

Set the identity value to -2,147,483,648, which will give you a long time
before you have any possiblity of hitting values you have already got in the
table.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Ben" wrote in message

> Is it possible to recycle the identity field value back to 1 when it
> reaches
> its maximum value.
>
 >> Stay informed about: Recycle Identity field value when it reaches its max 

>>  I never understand why humans feel the need to start with 1 for identities - it loses them 50% of the possible values.  Guess it is just one of those things. <<

The mindset is simple to underestand. When you mimic a magnetic tape,
PHYSICAL records are nuimbered from 1 to n; there are no PHYSICAL
records in negative PHYSICAL positions. Once you learn RDBMS, you
stop using IDENTITY and start using keys instead.
 >> Stay informed about: Recycle Identity field value when it reaches its max 

> The mindset is simple to underestand. When you mimic a magnetic tape,
> PHYSICAL records are nuimbered from 1 to n; there are no PHYSICAL
> records in negative PHYSICAL positions. Once you learn RDBMS, you
> stop using IDENTITY and start using keys instead.

The majority use them as surrogate keys in MS SQL Server as well you know
it.

Your belief that people use them to mimic magnetic tape just highlights the
degree to which you are out of touch with the industry.

Its one thing to "learn RDBMS" but its an entirely different thing to "apply
RDBMS" the latter of which you consistently fail to do.

--ROGERSON--

"--CELKO--" wrote in message

>> I never understand why humans feel the need to start with 1 for
>> identities - it loses them 50% of the possible values. Guess it is just
>> one of those things. <<
 >> Stay informed about: Recycle Identity field value when it reaches its max 

The choice of magnetic tape as an example might be a poor one - but the
concept is the same misconception that many have; and the reason why most
people set the identity seed to one



The concept of unordered, or arbitrarily ordered, sets is elusive and they
seek to provide order to chaos



There are, of course, other uses for identity; but that it is overused, and
misused, is sure.


"Tony Rogerson" wrote in message

>> The mindset is simple to underestand. When you mimic a magnetic tape,
>> PHYSICAL records are nuimbered from 1 to n; there are no PHYSICAL
>> records in negative PHYSICAL positions. Once you learn RDBMS, you
>> stop using IDENTITY and start using keys instead.
>
> The majority use them as surrogate keys in MS SQL Server as well you know
> it.
>
> Your belief that people use them to mimic magnetic tape just highlights
> the degree to which you are out of touch with the industry.
>
> Its one thing to "learn RDBMS" but its an entirely different thing to
> "apply RDBMS" the latter of which you consistently fail to do.
>
> --ROGERSON--
>
> "--CELKO--" wrote in message
>
>>> I never understand why humans feel the need to start with 1 for
>>> identities - it loses them 50% of the possible values. Guess it is just
>>> one of those things. <<
>
>
 >> Stay informed about: Recycle Identity field value when it reaches its max 

>> The concept of unordered, or arbitrarily ordered, sets is elusive and they seek to provide order to chaos <<

But they want a *familiar* order to the "chaos" so you see posters
here talking about"first", "last" and "next" records in a table.
That is why I say mag tape files; their ghosts lived on early disk
systems and programming languages long after the punch cards and tape
drives were scraped.

Jerry Weinberg's classic Psychology of Programming has stayed in print
for these decades because he realized that your first language gives
you a mindset and that it is hard to break. Just the formatting of
code and the length of variable names says worlds about the
programmer.

NEWID() and their ilk would avoid hot spots in generated phsycial
locators and look more like pointers. This is still not RDBMS, but it
is a more sophistificated design flaw. It says that the guy worked
with some kind of random access file system.

Since I teach SQL, I need to know the various mindsets so I can "un-
learn" my students. Maybe I shold expand that topi8c in the3 next
edition of THINKING IN SETS ..
 >> Stay informed about: Recycle Identity field value when it reaches its max 

On Dec 26, 10:45 am, "m" wrote:
> The choice of magnetic tape as an example might be a poor one - but the
> concept is the same misconception that many have; and the reason why most
> people set the identity seed to one
>
> The concept of unordered, or arbitrarily ordered, sets is elusive and they
> seek to provide order to chaos

I would say that for many (maybe most) practical purposes the concept
of unordered, or arbitrarily ordered, sets is an unnecessary
complication. This is why most bright people unfamiliar with RDBMS
intuitively do not want it - they apply Occum's razor to RDBMS theory.
This includes bright young people who have never seen a computer
magnetic tape. They don't mimic magnetic tapes - they just use their
brains and common sense and reject previous generations' prejudices.
This is progress for you...
 >> Stay informed about: Recycle Identity field value when it reaches its max 

On Dec 26, 3:30 pm, Hugo Kornelis
wrote:
> On Fri, 26 Dec 2008 12:12:38 -0800 (PST), --CELKO-- wrote:
> >Since I teach SQL, I need to know the various mindsets so I can "un-
> >learn" my students.
>
> If you teach SQL, then you should concentrate on LEARNING your students.
> The human brain is perfectly capable of retaining lots of information,
> there is no need to remove stuff first before you can put in something
> new.

I concur. The idea that students are so rigid that they have to
unlearn one approach before they can learn another one is just
ridiculous. You did not have to unlearn Dutch so that you can speak
English, right?
 >> Stay informed about: Recycle Identity field value when it reaches its max 

>> I would say that for many (maybe most) practical purposes the concept of unordered, or arbitrarily ordered, sets is an unnecessary complication. This is why most bright people unfamiliar with RDBMS intuitively do not want it - they apply Occum's razor to RDBMS theory. <<

But this is not a complication; it is a simplifcation -- a constraint
is removed. Ordering can be very elaborate -- get a librarian's guide
book for sorting book titles. This is the problem of the Familar, not
complications. Simplifcation also destroys the Familiar.

In Western culture, thanks to the alphabet, we expect an ordering.
Chinese do not expect this. An old friend of mine taught in Red China
many years ago. She got her class roster in Latin letters, but
unsorted; you simply learn the names of 150 students from a list.

>> This includes bright young people who have never seen a computer magnetic tape. They don't mimic magnetic tapes - they just use their brains and common sense and reject previous generations' prejudices. This is progress for you... <<

They are using magnetic tapes as their mindset, but they do not know
it. Magnetic tapes exist because of punch cards; punch cards are
sorted because of alphabetic order (and place-valued numbering that
allows alphabetic sorting).

They are using the previous generations' prejudices because they live
in an alphabetic cultuire. An example : nested sets versus adjacency
list model for trees. Older programmers like adjacency list models
because they look like pointer chains. Younger programmers like
nested sets because they look like HTML tags.
 >> Stay informed about: Recycle Identity field value when it reaches its max 

On Dec 26, 3:47 pm, --CELKO-- wrote:
> >> I would say that for many (maybe most) practical purposes the concept of unordered, or arbitrarily ordered, sets is an unnecessary complication. This is why most bright people unfamiliar with RDBMS  intuitively do not want it - they apply Occum's razor to RDBMS theory. <<
>
> But this is not a complication; it is a simplifcation -- a constraint
> is removed.  Ordering can be very elaborate -- get a librarian's guide
> book for sorting book titles. This is the problem of the Familar, not
> complications.  Simplifcation also destroys the Familiar.
>
> In Western culture, thanks to the alphabet, we expect an ordering.
> Chinese do not expect this.  An old friend of mine taught in Red China
> many years ago.  She got her class roster in Latin letters, but
> unsorted; you simply learn the names of 150 students from a list.
>

Whatever you call it - for us Westerners it requires an effort to keep
in mind that sets are not sorted. For most practical problems this
effort yields us absolutely no practical gains.

> >> This includes bright young people who have never seen a computer  magnetic tape. They don't mimic magnetic tapes - they just use their  brains and common sense and reject previous generations' prejudices.  This is progress for you... <<
>
> They are using magnetic tapes as their mindset, but they do not know
> it.  Magnetic tapes exist because of punch cards; punch cards are
> sorted because of alphabetic order (and place-valued numbering that
> allows alphabetic sorting).
>
> They are using the previous generations' prejudices because they live
> in an alphabetic cultuire.  An example : nested sets versus adjacency
> list model for trees. Older programmers like adjacency list models
> because they look like pointer chains.  Younger programmers like
> nested sets because they look like HTML tags.

I think you are contradicting yourself. Don't you see yourself: it is
alphabetic order not magnetic tape we are intuitively expecting. Don't
get me wrong - I think we need to change mindsets whenever it is
necessary. But the mindset change must be justified by some practical
gain, and in most cases I just don't see any need, most database
problems can be solved just fine without the concept of unordered
sets.
 >> Stay informed about: Recycle Identity field value when it reaches its max 

--CELKO-- (jcelko212@earthlink.net) writes:
> In Western culture, thanks to the alphabet, we expect an ordering.
> Chinese do not expect this. An old friend of mine taught in Red China
> many years ago. She got her class roster in Latin letters, but
> unsorted; you simply learn the names of 150 students from a list.

Maybe because the Chinese didn't think Latin letters could be sorted? After
all, they have no strokes, so how could they be sorted? As far as I have
understood it, Chinese letters are sorted by strokes.


--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Recycle Identity field value when it reaches its max 

>> Sure if you have heavy inserts then you would have less contention.  But at the cost of a much higher percentage of page splits (of the "bad" kind, where half the page needs to be moved to make room for the new row). <<

Then play with the percentage of free space per data page, like we did
in the old days. . This is one reason I like perfect hashing more
and more as I get older. The CPU is fast and when the math is done, I
get to data in one probe. SQLO and perhaps IT in general gets bzack
to "it all depends..." so fast
 >> Stay informed about: Recycle Identity field value when it reaches its max 

> NEWID() and their ilk would avoid hot spots

Sure if you have heavy inserts then you would have less contention. But at
the cost of a much higher percentage of page splits (of the "bad" kind,
where half the page needs to be moved to make room for the new row).
 >> Stay informed about: Recycle Identity field value when it reaches its max 

>> Maybe because the Chinese didn't think Latin letters could be sorted? <<

No. They translared names directly with software in the order
enterd. They did not think about sorting at all! Hong Kong phone
operators rember 10K or more phone numbers from names They have a
contest each year ..

>> As far as I have understood it, Chinese letters are sorted by strokes <<.

There are 4 or 5 systems. It is NOT strictly strokes; it is a !@#%
mess!! In most systems, you take the base radical such as MA (horse)
then show charaters build from it (horse-fly or fly, Mother, etc. ).
I play Chinese chess and MA or "Knight" is one of the pieces.

When I was a typesetter in the 1970's , I learned that Chinese
newspapers in San Francisco classified metal type by categories -- Man
Eartth, Heaven, Animal, etc. (do you play Pai Goiw?) They set type on
roller skates -- literally
 >> Stay informed about: Recycle Identity field value when it reaches its max