Recycle Identity field value when it reaches its max

> Then play with the percentage of free space per data page, like we did
> in the old days.

That'd be great, if more than 1% of the audience had any concept of fill
factor.
 >> Stay informed about: Recycle Identity field value when it reaches its max 

On Dec 28, 11:15 am, "m" wrote:
> This is not an issue exclusive to western culture; nor is it limited by
> alphabetic congruity.  This is, as Celko says, an issue of familiarity.
> Relational database are _hard_ to understand because they are, in many ways,
> counterintuitive.  This is not a case of a new generation rejecting
> something contrived in the dotage of their predecessors, but of a specific
> and exotic species of knowledge providing a tangible benefit to the elegance
> and performance of data storage and retrieval systems.
>
> I will not go so far as to arrogate myself in the claim that I can relate
> the sum of another's experience from their choice of variable names or the
> paradigm of a singularity of their construction
>
> Remembering that data storage is arbitrarily ordered is a VITAL part of
> working on ANY database.  

No. In 21st century there is growing discontent with this approach.
Vertica broke away from the unsorted data paradigm, and it (Vertica)
is lightning fast for some problems.
 >> Stay informed about: Recycle Identity field value when it reaches its max 

This is not an issue exclusive to western culture; nor is it limited by
alphabetic congruity. This is, as Celko says, an issue of familiarity.
Relational database are _hard_ to understand because they are, in many ways,
counterintuitive. This is not a case of a new generation rejecting
something contrived in the dotage of their predecessors, but of a specific
and exotic species of knowledge providing a tangible benefit to the elegance
and performance of data storage and retrieval systems.



I will not go so far as to arrogate myself in the claim that I can relate
the sum of another's experience from their choice of variable names or the
paradigm of a singularity of their construction



Remembering that data storage is arbitrarily ordered is a VITAL part of
working on ANY database. The solution may be as simple as to always include
an order by clause in queries - and there is nothing wrong with that
approach! A better one is to make the behaviour of the consuming
application invariant with the order of the result set(s); and many times
this just means sorting for display on the desktop.




"Alex Kuznetsov" wrote in message

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 

>> No. In 21st century there is growing discontent with this approach. Vertica broke away from the unsorted data paradigm, and it (Vertica) is lightning fast for some problems. <<

Before Vertica, we had SAND, Sybase's IQ, Model 204 and a few other
columnar DBs. What columnar DBs broke away from was contiguous
physical records. These are usually great for Data Warehouse apps
where you need aggregate data but not so good for OLTP.
 >> Stay informed about: Recycle Identity field value when it reaches its max 

On Dec 28, 5:50 pm, --CELKO-- wrote:
> >>  No. In 21st century there is growing discontent with this approach. Vertica broke away from the unsorted data paradigm, and it (Vertica) is lightning fast for some problems. <<
>
> Before Vertica,  we had SAND, Sybase's IQ, Model 204  and a few other
> columnar DBs.  What columnar DBs broke away from was contiguous
> physical records.  These are usually great for Data Warehouse apps
> where you need aggregate data but not so good for OLTP.

Yep, if you assume that data is unsorted, you can build RDBMS based on
that assumption, and it will be great for some purposes. Similarly, if
you assume that data is _sorted_, you can also build another kind of
DBMS based on that assumption, and it will also be great for some
purposes too...

The rules DBMS are built on are not laws of Nature - they can be
changed and you can still have a useful system.
 >> Stay informed about: Recycle Identity field value when it reaches its max 

On Dec 29, 8:02 pm, "m" wrote:
> This product, Vertica, is purpose built for time-series data and, like most
> problems, the more accurately you can define it, the more efficiently you
> can solve it.

Yes, and there are other cases when RDBMS do not shine, and other
approaches work better...
 >> Stay informed about: Recycle Identity field value when it reaches its max 

This product, Vertica, is purpose built for time-series data and, like most
problems, the more accurately you can define it, the more efficiently you
can solve it.



I also question the benchmarks on their website. I have serious doubts
about the efficiency of the systems they were replacing



This system aside, systems that store data serially are not new and are well
understood to be a subset of DBMSes. If you assume that your app will
ALWAYS be using a serial data store, then you can optimize it in certain
ways. If not, then your app will still work, but it _may_ do some extra
work to make sure that it will always work.





"Alex Kuznetsov" wrote in message

On Dec 28, 11:15 am, "m" wrote:
> This is not an issue exclusive to western culture; nor is it limited by
> alphabetic congruity. This is, as Celko says, an issue of familiarity.
> Relational database are _hard_ to understand because they are, in many
> ways,
> counterintuitive. This is not a case of a new generation rejecting
> something contrived in the dotage of their predecessors, but of a specific
> and exotic species of knowledge providing a tangible benefit to the
> elegance
> and performance of data storage and retrieval systems.
>
> I will not go so far as to arrogate myself in the claim that I can relate
> the sum of another's experience from their choice of variable names or the
> paradigm of a singularity of their construction
>
> Remembering that data storage is arbitrarily ordered is a VITAL part of
> working on ANY database.

No. In 21st century there is growing discontent with this approach.
Vertica broke away from the unsorted data paradigm, and it (Vertica)
is lightning fast for some problems.
 >> Stay informed about: Recycle Identity field value when it reaches its max 

I think it's important to differentiate between theory and practical
solutions. For instance, how many theoretical and defined forms of
normalization are there and how many practical systems follow every one of
those constraints and rules?

The concept of a logical table is essentially an unordered set of rows and
columns. However, in a usable RDBMS based solution, you are going to rely on
ordered data storage in order to speed up data access. Consider any index
you will build within the major RDBMSes. Ordered data storage.

Whether you use an identity field as a surrogate key or a natural key,
you're going to index it, and whether Joe Celko realizes it or not, if you
index your primary key, then you're physically ordering your data (even if
it's just the keys in the index, since in this case, the keys in the index
would equal your primary key).

I tend to choose to use surrogate keys because I know that this value will
never have to change (plus it tends to result in a smaller key). It's a very
rare occasion when the natural key is a uniquely and non-changing data value.
Unfortunately for theorists like Celko (and make no mistake, I believe Joe
has one of the strongest understandings of RDBMS theory), the world that you
try to model in database systems does NOT bend itself to the rules of RDBMS
design, it defines itself by the actual reality of the thing being modeled.
Because of this, the system being modeled should drive the design of the
database, not the other way around.

Just a quick reality check.

Matt

"Alex Kuznetsov" wrote:

> On Dec 29, 8:02 pm, "m" wrote:
> > This product, Vertica, is purpose built for time-series data and, like most
> > problems, the more accurately you can define it, the more efficiently you
> > can solve it.
>
> Yes, and there are other cases when RDBMS do not shine, and other
> approaches work better...
>
 >> Stay informed about: Recycle Identity field value when it reaches its max 

On Thu, 25 Dec 2008 13:01:31 -0800 (PST), --CELKO--
wrote:

>>>  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.

Still waiting for the patently obvious natural key for a human
being.....

Or a customer, where that customer may or may not be a registered
company....

Or anything else, for that matter.

Iain
 >> Stay informed about: Recycle Identity field value when it reaches its max 

>> Still waiting for the patently obvious natural key for a human being....Or a customer, where that customer may or may not be a registered company.... <<

Have you looked at identity resolution software? It has become a
whole subtopic in the Data Quality world. Here is a start:

http://www.dmreview.com/white_papers/10002001-1.html?ET=dmreview:e757:...7139a:&

Informatica and other companies concerned with loading Data Warehouses
have some smart software to resolve identity problems.
 >> Stay informed about: Recycle Identity field value when it reaches its max 

Why can't you answer the question Joe, instead of pointing to an article
that requires registration? What do you think Amazon uses to identify a
customer? DNA? Highly unlikely. Anything less is unverifiable according
to your strict standards. So, show me the DDL for their Customers table,
please?


>>> Still waiting for the patently obvious natural key for a human being....Or
>>> a customer, where that customer may or may not be a registered company....
>>> <<
>
> Have you looked at identity resolution software? It has become a
> whole subtopic in the Data Quality world. Here is a start:
>
> http://www.dmreview.com/white_papers/10002001-1.html?ET=dmreview:e757:2057139a
> :&st=email
>
> Informatica and other companies concerned with loading Data Warehouses
> have some smart software to resolve identity problems.
 >> Stay informed about: Recycle Identity field value when it reaches its max 

>> Why can't you answer the question Joe, instead of pointing to an article that requires registration? <<

Because you cannot post an entire discipline on a newsgroup. Identity
resolution has become a very broad topic in the Data Quality world. I
just wanted to get him started with information that does not require
paying money. Do you think I should have bought him a member is
DAMA?

>>  What do you think Amazon uses to identify a customer?  DNA?  Highly unlikely.  Anything less is unverifiable according to your strict standards.  So, show me the DDL for their Customers table, please? <<

Amazon uses email addresses (an option that Iain rejected) and credit
card/PayPal accounts. Why don't they use an IDENTITY? Well, they are
not an MS shop running on one server. But internally generated
customer numbers cannot be verified internally. Customers will not
learn them by heart. But an email address is easy to validate (look
up a few regular expressions for them) and you ping it to verify it.
I can also use it to look at other websites if I want to get more
details on the customer (Joe Celko speaks Esperanto and plays Go
according to his browsing habits; better put those topic in his
favorites lists and send him some spam).

I am the guy who talks and writes thousands of words about "degree of
risk" in data. The courts want my DNA when I am on trial; a foreign
government wants to see my Passport; the grocery store wants my
Drivers License when I cash a check; my ATM wants my card and a PIN.
Each has a different degree of risk. Etc. Etc. If you don't have my
books, you should have read my postings.

What I do see is that DNA will become the identifier of choice when
the technology is there in just a few years. Instead of a PIN, I will
put my finger in an ATM (or whatever replaces them). A Japanese
company is marketing a cheap fingerprint machine for police -- Austin
wants to buy ~100 of them and we are a small city. There are also DNA
machines that can go into the field that are currently sold to police
agencies. It is just a matter of time.

I don't want to deal with the privacy issues here ...
 >> Stay informed about: Recycle Identity field value when it reaches its max