473,836 Members | 2,138 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Guid vs Identity

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 ?

Jul 20 '05 #1
24 10892
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 uniqueidentifie r
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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
>> 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.
Jul 20 '05 #3
--CELKO-- (jc*******@eart hlink.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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
Erland Sommarskog wrote:
--CELKO-- (jc*******@eart hlink.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.wash ington.edu
(replace 'x' with a 'u' to reply)

Jul 20 '05 #5
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.was hington.edu> wrote in message
news:1088292247 .488234@yasure. ..
Erland Sommarskog wrote:
--CELKO-- (jc*******@eart hlink.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.wash ington.edu
(replace 'x' with a 'u' to reply)

Jul 20 '05 #6
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!
Jul 20 '05 #7

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!
Jul 20 '05 #8


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!
Jul 20 '05 #9
>> 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
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!
Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
630
by: Robert Chapman | last post by:
I know that this should be impossible, yet it is happening... I have some c# code that uses a method scoped variable of type Guid. The code assigns Guid.NewGuid() to the variable. This value is then used as a primary key in a database table. I have seen two occasions over the last 6 months where after a certain point in time the same Guid is always generated by the code. I have verified that there is nothing static, the code is...
0
1428
by: Armel Asselin | last post by:
Hello, I try to use an GUID identity column (GUID / Automatic Number). Previously I used a Long / Automatic Number and I could use "SELECT @@identity as ID" to retrieve the ID of each newly created line. What is the request to use when the identity is a GUID column ??? Please help Armel
26
11579
by: John Grandy | last post by:
Is it possible to generate a 20 byte integer from a GUID that is "unique enough" ( just like a GUID is not truly unique , but is "unique enough" ). We identify transactions with GUIDs , but a partner web service has a 20 byte limit on transaction ID passed.
9
10283
by: eagle | last post by:
How can I return the new id that is created when an insert command is used? for example: qry = "insert into tblClients (lname, fname) values ('smith', 'joe')" Dim xyz as string = command.executescalar(strconn, commandtype.text, qry) I tried this and received an error "System.NullReferenceException: Object reference not set to an instance of an object" Although the insert does take place. How would I do this? I can't create a
5
6678
by: Jason L James | last post by:
Hi all, is there a method or property of a class that will return a GUID in VB.NET that I can use as the PK of my DB. I do not want SQL to generate it as I can not then use SELECT @@IDENTITY to retrieve it. I want to try and mimic the functionality of
3
3044
by: MP | last post by:
context: vb6/ ado / .mdb format / jet 4.0 (not using Access - ADO only) - creating tables via ADO (don't have access) - all tables have a primary key (PK) - many of the PK will become FK(Foreign Key) in other table(s) - record entries will be made via ADO I am soliciting opinions on the pros and cons of using AUTOINCREMENT versus code generated GUID or LONG value (as far as my limited understanding goes, if I enter a record, and need...
1
20990
by: Brad Eck | last post by:
In Access, newID returns a unique for the table. In SQL Server, newid() returns a GUID - unique in the world. I do not need or desire that complexity. Is there a way to get a simple unique int on the table in SQL Server? Brad Eck http://www.sitesdynamic.com http://www.basketsetcetera.com *** Sent via Developersdex http://www.developersdex.com ***
8
8750
by: Jack Brown | last post by:
Hi there, I need to create a unique identifier for my own internal needs and am happy to rely on "Guid.NewGuid()" to pull this off. I'd like to know if .NET offers any competing alternative however. Perhaps there's something with a richer set of functions for instance and it might offer potential advantages over a GUID. Am just exploring the possibilities for now. Thanks in advance.
16
2394
by: sloan | last post by:
Current Framework 2.0/3.0. ... In Sql Server, there is a way to generate consecutive guid's. newsequentialid. Is there a way to reproduce this type of consecutive guid's in the framework.
0
9656
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10821
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10527
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10571
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7773
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6973
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5812
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4001
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3102
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.