473,561 Members | 3,772 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 10857
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...
0
1399
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
11524
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
10210
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...
5
6654
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
3028
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...
1
20981
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...
8
8722
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...
16
2373
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
7637
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7558
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...
0
7851
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. ...
0
8072
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...
1
7605
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...
0
6195
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3609
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2055
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1171
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.