Guid vs Identity

[Ilija_G]

Hi,

Is there any replace for "Select @@identity" that could return "just
inserted" GUID as a primary key?

Has anyone tested what's faster, working with Guid or Autonumber ?

[Erland Sommarskog]

Ilija_G (ac****@on.net.mk) writes:

Is there any replace for "Select @@identity" that could return "just
inserted" GUID as a primary key?
The best would be to say:

DECLARE @guid uniqueidentifier
SELECT @guid = newid()
INSERT tbl (guidcol, ...)
VALUES (@guid, ...)

Has anyone tested what's faster, working with Guid or Autonumber ?

It is one of these "It depends". I most situations using a integer IDENTITY
column is better, simply because it is smaller. This does not least pay
back when you retrieve data. Since IDENTITY values are consecutive, this
means that all insertions happens in place in the index which has the
key column, whereas with guids they are scattered all over the place.
The latter gives more fragmentation, but fewer hot spots. In any case,
indexes on both IDENTITY columns and guids should normally be non-clustered.

I think the mean reason for using guids is that you circumstances are
such that identity values simply cannot be used, for instance merge
replication.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

>> Guid or Autonumber .. as a primary key? <<

Did you ever consider not using either and find a REAL key? All you
are doing is using PHYSICAL locators, as you would in a file system,
and destroying your data integrity.

I know it is so nice to have "magic, universal, one-size-fits-all"
answer to every problem, but it is always wrong. Designing a databse
is work and it requires research and planning.

[Erland Sommarskog]

--CELKO-- (jc*******@earthlink.net) writes:

Guid or Autonumber .. as a primary key? <<

Did you ever consider not using either and find a REAL key? All you
are doing is using PHYSICAL locators, as you would in a file system,
and destroying your data integrity.

Cut the crap, Celko. You have no idea of what business problem Ilija
is trying to solve. If you can't help people, just keep your big mouth
shut.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Daniel Morgan]

Erland Sommarskog wrote:

--CELKO-- (jc*******@earthlink.net) writes:

Guid or Autonumber .. as a primary key? <<

Did you ever consider not using either and find a REAL key? All you
are doing is using PHYSICAL locators, as you would in a file system,
and destroying your data integrity.

Cut the crap, Celko. You have no idea of what business problem Ilija
is trying to solve. If you can't help people, just keep your big mouth
shut.

Celko's advice was spot on. The fact that you can't handle a perfectly
valid response without resorting to four letter words demonstrates
many things. Not one of them being expertise in relational databases.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Mischa Sandberg]

Speaking as someone who's had to untangle a database where ALL primary keys
were identities, I'd like to suggest that such schemes, easy as they look up
front, bring real headaches later on:

- if there is some other column (set of columns) that ought to be unique,
you need two unique indexes. Or you can really set yourself up for a mess by
assuming that the business logic will make sure the values stay unique. Ha!

- watch out: primary keys are clustered by default, and except for
write-only tables, clustering by ID is seldom what you want

- if you have to extract/transfer consistent chunks of a set of related
tables from one database to another (well, I did) the mapping of old
identity values to new ones can get complicated. It helps if you have the
foresight to not use a step value of (1).
"Daniel Morgan" <da******@x.washington.edu> wrote in message
news:1088292247.488234@yasure...

Erland Sommarskog wrote:

--CELKO-- (jc*******@earthlink.net) writes:

>Guid or Autonumber .. as a primary key? <<

Did you ever consider not using either and find a REAL key? All you
are doing is using PHYSICAL locators, as you would in a file system,
and destroying your data integrity.

Cut the crap, Celko. You have no idea of what business problem Ilija
is trying to solve. If you can't help people, just keep your big mouth
shut.

Celko's advice was spot on. The fact that you can't handle a perfectly
valid response without resorting to four letter words demonstrates
many things. Not one of them being expertise in relational databases.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Joe Celko]

Dan, don't get mad at Sommarskog; he usually posts good stuff and and a
nice colloection of FAQs. He is one of the "good guys" but seems to be
having a bad day. Have you ever seen any of my bad days?

We all know that neither GUIDs or other proprietary Autonumbers can be
keys. The only valid UNIVERSAL MAGIC KEY to use in a data model is the
17 letter Hebrew word that God puts on the bottom of everything in
creation. It is non-proprietary, but you have to use a Cabalist
procedure that you can pick up from any ultra-orthodox rabbi :)

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ilija_G]

Erland, Thank you for the answare, but maybe I didn't explain quite
well. I need "Select @@identity" after I execute "Insert" command, and
SQL Server execute newid() itself.
Is any simple way to do this, or should I use the the way you suggested
?

As for the GUID's , yes I am using GUID to ensure that my data is ready
for replication.

Thanks for trying to help me,
Ilija

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ilija_G]

CELKO, I can understand your "anger" when talking about
"magic key" , but If you designed databases than you must know that not
allways the best solution is the one that fits, sometimes you must think
and use "the magic key" , when the time is crucial for solving the
problem.

Regards,
Ilija
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Joe Celko]

>> but If you designed databases than you must know that not always the
best solution is the one that fits, sometimes you must think and use
"the magic key", when the time is crucial for solving the problem. <<

I used to design databases when I was younger. Now I repair them and I
charge a high consulting rate to do that work. Mischa Sandberg seems to
be doing the same kind of work, too! Perhaps he and I ought to get a
bottle of virtual scotch and share war stories.

Please believe me, it is not that hard to get the job right at the start
of the project, but orders of magnitude harder to correct it later. If
you do not believe, then look at the SEI, TRW and DoD research.

1) Start by looking for an industry standard for the keys. VIN for
vehicles, etc.

2) Look for business rules that make subsets of columns unique --
candidate keys.

3) Look for DRI rules and enforce them, avoiding triggers wherever
possible.

4) If you have to design an identifier yourself, then actually
**design** it. Check digits, syntax that can be validated by SIMILAR TO
or even LIKE predicates, structure, human readability, etc.

You might want to read my current column, especially the last paragraph.

http://www.intelligententerprise.com...ssionid=BNNNJL
5FBL5F4QSNDBCCKHQ?articleID=21401090

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Ilija_G (ma****@hotmail.com) writes:

Erland, Thank you for the answare, but maybe I didn't explain quite
well. I need "Select @@identity" after I execute "Insert" command, and
SQL Server execute newid() itself.
Is any simple way to do this, or should I use the the way you suggested
?

There is no corresponding thing to @@identity for GUIDs, so you have to do
it the way I demonstrated.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Daniel Morgan (da******@x.washington.edu) writes:

Celko's advice was spot on. The fact that you can't handle a perfectly
valid response without resorting to four letter words demonstrates
many things. Not one of them being expertise in relational databases.
Cut the crap, Morgan! Ilija asked two questions, and Celko's reply
answered none of the questions. If you think that it is a valid response,
then you and I have very different opinions what these newsgroups are
good for. My opinion what I am doing here is clear: to help people and
share my expertise, so that those I can help can improve their skills
and knowledge. You don't do that by flaming them and telling them that
they are idiots, so that they never dare to ask a question again.

Neither you, nor I nor Celko knows what Ilija's business requirements are.
Maybe he should use something else, maybe he have very good reasons for
it. Experience have told me that you need to humble, and realize each
situation has its solution. "Natural keys" may sound oh so fine, but in
many situations of real life, the keys of the real world do not live up
to the requirements of a primary key in a relational database.

Joe Celko writes: Have you ever seen any of my bad days?

We have just see too many of them in these newsgroups. I wonder just how
many poor SQL beginners you have managed to deter from ever asking a
question again with you canned nonsense answers.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Daniel Morgan]

Erland Sommarskog wrote:

Daniel Morgan (da******@x.washington.edu) writes:

Celko's advice was spot on. The fact that you can't handle a perfectly
valid response without resorting to four letter words demonstrates
many things. Not one of them being expertise in relational databases.

Cut the crap, Morgan! Ilija asked two questions, and Celko's reply
answered none of the questions. If you think that it is a valid response,
then you and I have very different opinions what these newsgroups are
good for. My opinion what I am doing here is clear: to help people and
share my expertise, so that those I can help can improve their skills
and knowledge. You don't do that by flaming them and telling them that
they are idiots, so that they never dare to ask a question again.

There is a difference between what is beign said and how it is being
said. My comment was on the quality of the advice ... not the method.

Far too many people seem to be of the opinion that

1. I have a problem
2. Surrogate keys are a solution
3. Therefore I need surrogate keys
Neither you, nor I nor Celko knows what Ilija's business requirements are.
What makes you think Ilija does either? ;-)

The point being that we give or withhold advice based on what we think
appropriate given what was posted.
Maybe he should use something else, maybe he have very good reasons for
it. Experience have told me that you need to humble, and realize each
situation has its solution.
I'm not knocking humility but you are confusing quality of advice with
how it was given. The usenet is no place for those that lead with their
feelings and emotions.

"Natural keys" may sound oh so fine, but in many situations of real life, the keys of the real world do not live up
to the requirements of a primary key in a relational database.
5% of the time that is True. 95% it is not. Surrogate keys are a 100%
guarantee of data corruption unless you also have unique constraints
that could were, in fact, the natural key.
Joe Celko writes:

Have you ever seen any of my bad days?

We have just see too many of them in these newsgroups. I wonder just how
many poor SQL beginners you have managed to deter from ever asking a
question again with you canned nonsense answers.

I wonder how many of those of you banging out SQL have

1. Never taken a university level course on the subject
2. Have no idea who Chris Date is
3. Wouldn't survive 5 days in a non-windows shop

You'd be wise to listen to Mr. Celko. There are few that knows as
much about the subject as he does. Buy him a scotch some time and
you might get past your view of his attitude and discover his aptitude.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Dan Guzman]

> Is there any replace for "Select @@identity" that could return "just

inserted" GUID as a primary key?
As Erland stated, you can't retrieve the uniqueidentifier value when it is
generated via a default constraint or trigger. You can generate the value
yourself in Transact-SQL or generate it in your application program so that
you can easily determine the value.
Has anyone tested what's faster, working with Guid or Autonumber ?
An IDENTITY column is always faster because it is smaller and, when the
column is indexed, results in greater buffer efficiency for inserts due to
the hot spot at the tail of the index. This is especially true when the
index is clustered. Due to the random nature of uniqueidentifier values,
insert performance degrades when you have a large table with an indexed
uniqueidentifier column. A non-clustered index mitigates the performance
hit but is still much slower with large tables compared with IDENTITY.

We have an ETL application where we use uniqueidentifiers to identify
records (sic), assign the values in application code and insert using a bulk
insert technique. Because the resultant tables can have hundreds of
millions of rows, having an indexed uniqueidentifier column during the
inserts was too much of a performance hit. We either build the index after
the data load or create a composite index with the uniqueidentifier column
in the last position.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ilija_G" <ac****@on.net.mk> wrote in message
news:40******@news.mt.net.mk... Hi,

Is there any replace for "Select @@identity" that could return "just
inserted" GUID as a primary key?

Has anyone tested what's faster, working with Guid or Autonumber ?

[Erland Sommarskog]

Daniel Morgan (da******@x.washington.edu) writes:

Far too many people seem to be of the opinion that

1. I have a problem
2. Surrogate keys are a solution
3. Therefore I need surrogate keys
But that does not mean that anyone who is asking about autonumber values
is one of those people.

Neither you, nor I nor Celko knows what Ilija's business requirements
are.

What makes you think Ilija does either? ;-)

You can bet your ass that he knows more about it than we do. We don't
even know his business domain.
I'm not knocking humility but you are confusing quality of advice with
how it was given.

How it was given, indeed has a lot do it. Had Celko posted something
like "In general autonumber or similar are not good solutions for
database implementation", and continued to explain in friendly voice,
and in away so that even a person with a low experience of database
implemetnation could get an understanding of what he was talking about,
I would not bother. If you want an example who is very good to express
himself in such away, I encourse you to search Google groups for posts
by BP Margolin who unfortunately does not post here any more.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Daniel Morgan]

Erland Sommarskog wrote:

Daniel Morgan (da******@x.washington.edu) writes:

Far too many people seem to be of the opinion that

1. I have a problem
2. Surrogate keys are a solution
3. Therefore I need surrogate keys

But that does not mean that anyone who is asking about autonumber values
is one of those people.

No it does not guarantee it. But if you've been following this usenet
group for more than a few days you know this is a band that has a lot
of percussion and very little melody.

Neither you, nor I nor Celko knows what Ilija's business requirements
are.

What makes you think Ilija does either? ;-)

You can bet your ass that he knows more about it than we do. We don't
even know his business domain.

I not only woudn't bet my ass I wouldn't bet yours. To assume that
someone in our business knows what they are doing flies in the face
of a lot of evidence.

I'm not knocking humility but you are confusing quality of advice with
how it was given.

How it was given, indeed has a lot do it. Had Celko posted something
like "In general autonumber or similar are not good solutions for
database implementation", and continued to explain in friendly voice,
and in away so that even a person with a low experience of database
implemetnation could get an understanding of what he was talking about,
I would not bother. If you want an example who is very good to express
himself in such away, I encourse you to search Google groups for posts
by BP Margolin who unfortunately does not post here any more.

This world is populated by adults. Get used to it. Either way ... I may
not have chosen to communicate Celko's thoughts with Celko's words ...
but his advice was more likely to be accurate than any other that could
have been given.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[Erland Sommarskog]

Daniel Morgan (da******@x.washington.edu) writes:

Erland Sommarskog wrote:

But that does not mean that anyone who is asking about autonumber values
is one of those people.
No it does not guarantee it. But if you've been following this usenet
group for more than a few days you know this is a band that has a lot
of percussion and very little melody.

Not only have I followed this newsgroup for quite some time, and I
have also worked with an application for quite some time, and I have
come to the realisation that natural keys out of the real world are
rare creature. Have you ever tried to model financial instruments?

(Hint: the situation when a natural key breaks down is when your users
insist on adding something to the system which is not in the domain of
that natural key, but yet is in the domain of the users' business.)
I not only woudn't bet my ass I wouldn't bet yours. To assume that
someone in our business knows what they are doing flies in the face
of a lot of evidence.
Ilija may or may not be using is uniqueidentifier for a good reason.
But he knows what system he is working with, and you and I and Celko
don't. If you still think that you know better than him, that gives
more ideas about your ego than your professional knowledge.
This world is populated by adults. Get used to it.

If Celko was only pulling the legs of me and other SQL Server MVPs
I could live with it. Now the victims for his meaningless tirades
are innocent people who deserve a better start in the SQL Server
world.

And if you think this is "adult" behaviour, then I am not coming over
to your playground.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Daniel Morgan]

Erland Sommarskog wrote:

Daniel Morgan (da******@x.washington.edu) writes:

Erland Sommarskog wrote:

But that does not mean that anyone who is asking about autonumber values
is one of those people.
No it does not guarantee it. But if you've been following this usenet
group for more than a few days you know this is a band that has a lot
of percussion and very little melody.

Not only have I followed this newsgroup for quite some time, and I
have also worked with an application for quite some time, and I have
come to the realisation that natural keys out of the real world are
rare creature. Have you ever tried to model financial instruments?

After 35 years in this industry I've done it more than a few times. And,
as I said before, while there are times when surrogate keys are the
correct solution ... that does not mean that every problem is a nail
and requires the use of hammer: Most don't. But thank you for
intentionally selecting an exception.
(Hint: the situation when a natural key breaks down is when your users
insist on adding something to the system which is not in the domain of
that natural key, but yet is in the domain of the users' business.)

Users don't insist on anything that breaks a system down in a
professional run IT shop. If you've faced that problem you might wish
to consider that the problem is in your IT department.

How is it that Date, Codd, Celko, and many others far more highly
qualified to discuss relational databases than either of us have
managed to not run into this issue as a show-stopper? Do you know
wish to claim you know more than the ASCII committee? How about the
ISO?

You can posture all you wish ... but the vast majority of the time
professionals in our industry will find your advice the quick easy
way out that more often than not demonstrates a lack of formal
education and training.

I not only woudn't bet my ass I wouldn't bet yours. To assume that
someone in our business knows what they are doing flies in the face
of a lot of evidence.

Ilija may or may not be using is uniqueidentifier for a good reason.

And he may not be. But of course that doesn't stop you from jumping in
and defending the quick fix where serious thought might be required.

This world is populated by adults. Get used to it.

If Celko was only pulling the legs of me and other SQL Server MVPs
I could live with it. Now the victims for his meaningless tirades
are innocent people who deserve a better start in the SQL Server
world.

Innocent? No one that takes a job and cashes paychecks for doing a
specific job is "innocent". Try treating what we do as a profession
rather than a hobby.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[DCM Fan]

This thread has been an interesting read, to say the least, watching a few
highly-respected Usenet folks "duke it out" on multiple principles. (Notably
Surrogate Keys and Usenet Focus)

Although I'm not in the same league as the aforementioned respectables, I feel
compelled to put in my 2 cents anyway!

I'm familiar with Chris Date and Celko in that I've read their stuff, and I
"seek" their columns with internet searches every now-and-then. I certainly
respect their work. Erland has also helped me in the past.

I frequent Date's and Fabian Pascal's website, dbdebunk.com, admittedly more
for its dry entertainment value, but the site does give ammo to those of us who
try to "do things the right way" in database design for business. (Their
anti-XML stuff is priceless!)

I don't think there's any argument that the "right way" from an ideal design
standpoint is to forget about surrogate keys altogether. (Is there ANY room for
surrogates???)

I gotta tell ya, though, that can be painful. I've got a table with 5 fields in
its primary key, and it has a child (foreign) table with 7 fields in its
primary key (The 5 from the parent, and 2 others.) Yes, I'm actually using a
5-field Foreign Key constraint to hold them together!

I learned databases originally through MS Access, so I would normally have just
said "screw it" and thrown an AutoIncrement in there, and related it using
that, but I have to admit that I was "determined to do this database the right
way" after having been educated by the likes of Celko and Date over the last
few years.

I sometimes regret that decision. It turns out that the "right way" has bogged
down the development time dramatically, because every time I need to reference
those tables, I have to write SQL Statements with ugly joins, long WHERE
clauses, and I have to deal with 5 or 7 fields all the time...passing those
values to-and-from application to server.

If I had used Autoincrement on the parent, related one-many on the child with
that field, development time would have been drastically reduced, and I could
still have used unique indexes judiciously to preserve integrity. I'm really
not sure what I've gained, however, except the right to say "I built it the
right way."

My point is this: Although I strive to do everything "the right way," reality
can interfere with the abstract ideal. I get the impression that the
"Idealists" are so far up the Ivory Tower that they have trouble dealing with
the real world, below the clouds. (Or they are SO Intelligent, Idealistic and
Abstract, they just refuse to--or literally CANNOT--acknowledge reality.)

I'm not being flip. I'm dead serious.

Case in point is this thread. The original post only wanted to know about the
pros and cons of GUID vs Identity. Hell, even the post was TITLED as such!
Erland offered awesome advice toward that post.

But the Idealists--a righteous group at that--couldn't let it go!
(Understandably, too, since it's the nature of righteousness!) It didn't matter
to them that the original post had no questions about when/where/how/why to USE
GUID and/or Identity. It didn't occur to them that the original poster may have
already thought through the ramifications (or inherited the application).

Instead, their retort was quite predictable, questioning mere existence of
GUID/Identity in the schema, while at the same time using subtle "put-downs"
against the implementer.

Personally, I was glad Celko posted what he did. Being righteous about
GUID/Identity is a good thing. I was also glad Erland posted his "cut the crap"
post back at Celko, because he brought to light the 'meaning' of Usenet and the
context of the original question--that is, he brought the post back to the real
world.

Although I consider myself a 'believer' in the relational model, I probably
won't use real keys ever again if they go beyond about 3 fields, maybe 4 at the
end of a one-many-many-many chain! I thank God he gave me the intelligence to
comprehend the Relational Model (well, Set Theory and Logic anyway), but I'm
more grateful that I can use that knowledge in the real world!

[Erland Sommarskog]

Daniel Morgan (da******@x.washington.edu) writes:

After 35 years in this industry I've done it more than a few times. And,
as I said before, while there are times when surrogate keys are the
correct solution ... that does not mean that every problem is a nail
and requires the use of hammer: Most don't. But thank you for
intentionally selecting an exception.
I didn't take financial instruments out of the blue. This is the core
of the business domain I work with.

(Hint: the situation when a natural key breaks down is when your users
insist on adding something to the system which is not in the domain of
that natural key, but yet is in the domain of the users' business.)

Users don't insist on anything that breaks a system down in a
professional run IT shop. If you've faced that problem you might wish
to consider that the problem is in your IT department.

We're an ISV, and there are changes in our customer's business - and
this is an ever-changing world - they can not always wait for our
next release, but have to work around with what they have. Or it
might just be that this particular part of the business is too marginal
to warrant the price tag that we would offer them.
How is it that Date, Codd, Celko, and many others far more highly
qualified to discuss relational databases than either of us have
managed to not run into this issue as a show-stopper?

Maybe because they have not worked much with real-world system and
gotten dirt under their nails? Nice talk about "the problem is in
your IT department" is alwyas easy to say from an armchair.

Ilija may or may not be using is uniqueidentifier for a good reason.

And he may not be. But of course that doesn't stop you from jumping in
and defending the quick fix where serious thought might be required.

No, I am not defending "the quick fix". Hell, none of us know if it is
a quick fix or not. You may have 35 years of experience in industry, but
I find that hard to belive. Had you had that experience, you would have
learnt that sometimes you are right, and sometimes you are wrong. And
you should have learnt to not talk about something you don't know about.

If Celko was only pulling the legs of me and other SQL Server MVPs
I could live with it. Now the victims for his meaningless tirades
are innocent people who deserve a better start in the SQL Server
world.

Innocent? No one that takes a job and cashes paychecks for doing a
specific job is "innocent". Try treating what we do as a profession
rather than a hobby.

He is innocent in the sense that just because he gets a paycheck he has
no reason to accept being slammed by nipwits who don't know anything
about his work.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Daniel Morgan]

Erland Sommarskog wrote:

Daniel Morgan (da******@x.washington.edu) writes:

After 35 years in this industry I've done it more than a few times. And,
as I said before, while there are times when surrogate keys are the
correct solution ... that does not mean that every problem is a nail
and requires the use of hammer: Most don't. But thank you for
intentionally selecting an exception.

I didn't take financial instruments out of the blue. This is the core
of the business domain I work with.

That may be true. But to either assume everyone else is doing the same.
Or that a specific person is doing so is a huge stretch. Rules have
exceptions. But surrogate keys are the result of rejecting the preferred
approach ... not the initial approach except for the lazy.

(Hint: the situation when a natural key breaks down is when your users
insist on adding something to the system which is not in the domain of
that natural key, but yet is in the domain of the users' business.)

Users don't insist on anything that breaks a system down in a
professional run IT shop. If you've faced that problem you might wish
to consider that the problem is in your IT department.

We're an ISV, and there are changes in our customer's business - and
this is an ever-changing world - they can not always wait for our
next release, but have to work around with what they have. Or it
might just be that this particular part of the business is too marginal
to warrant the price tag that we would offer them.

That may well be. And don't get me started on SAP, PeopleSoft or the
worst of the worst ... Siebel.

How is it that Date, Codd, Celko, and many others far more highly
qualified to discuss relational databases than either of us have
managed to not run into this issue as a show-stopper?

Maybe because they have not worked much with real-world system and
gotten dirt under their nails? Nice talk about "the problem is in
your IT department" is alwyas easy to say from an armchair.

Maybe? Perhaps you should learn more about them. Your assumption is not
valid.

Ilija may or may not be using is uniqueidentifier for a good reason.

And he may not be. But of course that doesn't stop you from jumping in
and defending the quick fix where serious thought might be required.

No, I am not defending "the quick fix". Hell, none of us know if it is
a quick fix or not. You may have 35 years of experience in industry, but
I find that hard to belive. Had you had that experience, you would have
learnt that sometimes you are right, and sometimes you are wrong. And
you should have learnt to not talk about something you don't know about.

My first work in this industry was Fortran IV with punchcards working
on an IBM 370-145 in 1969: You do the math.

What I've learned is that the vast majority of the time the best advice
is what Celko gave. And I clearly stated that his advice was helpful.
Not perfect ... helpful. And should not have received the disrespectful
response you gave it.

If Celko was only pulling the legs of me and other SQL Server MVPs
I could live with it. Now the victims for his meaningless tirades
are innocent people who deserve a better start in the SQL Server
world.

Innocent? No one that takes a job and cashes paychecks for doing a
specific job is "innocent". Try treating what we do as a profession
rather than a hobby.

He is innocent in the sense that just because he gets a paycheck he has
no reason to accept being slammed by nipwits who don't know anything
about his work.

Calling people with decades of expertise nitwits because you don't
share their opinion says much about you. My guess, and it is only a
guess, is that you've never taken a university level academic course
in relational theory or practice. What you've learned you've learned
on the job. And that practice is the reason why so many jobs are
being off-shored to people who, yes work for less, but also know what
they are doing.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

[William Cleveland]

--CELKO-- wrote:

Guid or Autonumber .. as a primary key? <<

Did you ever consider not using either and find a REAL key? All you
are doing is using PHYSICAL locators

Except, of course, that the rows may or may not be actually
physically located that way. Given that database tables are
mostly built with B-Trees, they probably aren't located that
way.

Bill

[xAvailx]

>>I encourse you to search Google groups for posts
by BP Margolin who unfortunately does not post here any more.<<

What did happen to him? Does he post anywhere else anymore?

Thx,

BZ

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

Daniel Morgan (da******@x.washington.edu) writes:

Far too many people seem to be of the opinion that

1. I have a problem
2. Surrogate keys are a solution
3. Therefore I need surrogate keys

But that does not mean that anyone who is asking about autonumber values
is one of those people.

Neither you, nor I nor Celko knows what Ilija's business requirements
are.

What makes you think Ilija does either? ;-)

You can bet your ass that he knows more about it than we do. We don't
even know his business domain.

I'm not knocking humility but you are confusing quality of advice with
how it was given.

How it was given, indeed has a lot do it. Had Celko posted something
like "In general autonumber or similar are not good solutions for
database implementation", and continued to explain in friendly voice,
and in away so that even a person with a low experience of database
implemetnation could get an understanding of what he was talking about,
I would not bother. If you want an example who is very good to express
himself in such away, I encourse you to search Google groups for posts
by BP Margolin who unfortunately does not post here any more.

[Erland Sommarskog]

xAvailx (bj******@hotmail.com) writes:

I encourse you to search Google groups for posts

by BP Margolin who unfortunately does not post here any more.<<

What did happen to him? Does he post anywhere else anymore?

I have no idea of his whereabouts. If you see him somewhere, please
say hello from me!

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Daniel Morgan (da******@x.washington.edu) writes:

That may be true. But to either assume everyone else is doing the same.
Or that a specific person is doing so is a huge stretch. Rules have
exceptions. But surrogate keys are the result of rejecting the preferred
approach ... not the initial approach except for the lazy.
It's funny, though, that when I take something from my own business that
you immediately admit that this is an exception. I'll give you another
core item in our application which appears equally difficult to model
with natural keys: customers.
What I've learned is that the vast majority of the time the best advice
is what Celko gave. And I clearly stated that his advice was helpful.
Not perfect ... helpful. And should not have received the disrespectful
response you gave it.
Let's see, Celko gives a very disrespectful response to Ilija, and
when I criticize that, you complain that I am being disrespectful?
Calling people with decades of expertise nitwits because you don't
share their opinion says much about you.
You are nipwits becase you are slamming someone (or defends the
slamming) without nothing zilch about what he is doing. And just because
you can call yourself an expert in relational databases does not
defend that. You are not an expert in Ilija's business, but complete
ignorants.
My first work in this industry was Fortran IV with punchcards working
on an IBM 370-145 in 1969: You do the math.

This is not a discussion about math. This is a discussion about social
skills. Of which one would expect that your 35 years in industry
should have helped you to acquire some.

You see: that is the core of the discussion *How* it is being said.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp