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]

Re: Guid vs Identity

Ilija_G (access@on.net. mk) writes:[color=blue]
> Is there any replace for "Select @@identity" that could return "just
> inserted" GUID as a primary key?[/color]

The best would be to say:

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

[color=blue]
> Has anyone tested what's faster, working with Guid or Autonumber ?[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[--CELKO--]

Re: Guid vs Identity

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

Re: Guid vs Identity

--CELKO-- (jcelko212@eart hlink.net) writes:[color=blue][color=green][color=darkred]
>>> Guid or Autonumber .. as a primary key? <<[/color][/color]
>
> 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.[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Daniel Morgan]

Re: Guid vs Identity

Erland Sommarskog wrote:
[color=blue]
> --CELKO-- (jcelko212@eart hlink.net) writes:
>[color=green][color=darkred]
>>>>Guid or Autonumber .. as a primary key? <<[/color]
>>
>>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.[/color]
>
>
> 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.[/color]

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/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Mischa Sandberg]

Re: Guid vs Identity

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" <damorgan@x.was hington.edu> wrote in message
news:1088292247 .488234@yasure. ..[color=blue]
> Erland Sommarskog wrote:
>[color=green]
> > --CELKO-- (jcelko212@eart hlink.net) writes:
> >[color=darkred]
> >>>>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.[/color]
> >
> >
> > 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.[/color]
>
> 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
> damorgan@x.wash ington.edu
> (replace 'x' with a 'u' to reply)
>[/color]

[Joe Celko]

Re: Guid vs Identity

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]

Re: Guid vs Identity


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]

Re: Guid vs Identity



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]

Re: Guid vs Identity

>> 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/showArticle.jhtml;jsessionid=BNNNJL

5FBL5F4QSNDBCCK HQ?articleID=21 401090

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

Re: Guid vs Identity

Ilija_G (makice@hotmail .com) writes:[color=blue]
> 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
> ?[/color]

There is no corresponding thing to @@identity for GUIDs, so you have to do
it the way I demonstrated.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: Guid vs Identity

Daniel Morgan (damorgan@x.was hington.edu) writes:[color=blue]
> 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.[/color]

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:[color=blue]
> Have you ever seen any of my bad days?[/color]

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Daniel Morgan]

Re: Guid vs Identity

Erland Sommarskog wrote:[color=blue]
> Daniel Morgan (damorgan@x.was hington.edu) writes:
>[color=green]
>>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.[/color]
>
>
> 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.[/color]

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
[color=blue]
> Neither you, nor I nor Celko knows what Ilija's business requirements are.[/color]

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.
[color=blue]
> 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.[/color]

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[color=blue]
> 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.[/color]

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.
[color=blue]
> Joe Celko writes:
>[color=green]
>>Have you ever seen any of my bad days?[/color]
>
> 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.[/color]

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/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Dan Guzman]

Re: Guid vs Identity

> Is there any replace for "Select @@identity" that could return "just[color=blue]
> inserted" GUID as a primary key?[/color]

As Erland stated, you can't retrieve the uniqueidentifie r 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.
[color=blue]
> Has anyone tested what's faster, working with Guid or Autonumber ?[/color]

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 uniqueidentifie r values,
insert performance degrades when you have a large table with an indexed
uniqueidentifie r 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 uniqueidentifie rs 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 uniqueidentifie r 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 uniqueidentifie r column
in the last position.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ilija_G" <access@on.net. mk> wrote in message
news:40dd617d@n ews.mt.net.mk.. .[color=blue]
> 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 ?
>[/color]

[Erland Sommarskog]

Re: Guid vs Identity

Daniel Morgan (damorgan@x.was hington.edu) writes:[color=blue]
> 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[/color]

But that does not mean that anyone who is asking about autonumber values
is one of those people.
[color=blue][color=green]
>> Neither you, nor I nor Celko knows what Ilija's business requirements
>> are.[/color]
>
> What makes you think Ilija does either? ;-)[/color]

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

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, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp