SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Uniqueidentifier vs. IDENTITY
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/04/2000 :  18:04:16  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Kim writes: "with an identity column you can SELECT @@identity after inserting a row. How do you get the value of a uniqueidentifier column after an insert? thanks!"

Uniqueidentifiers are new to SQL 7.0, and behave a little differently than our friend the IDENTITY column. Read on for the answer to Kim's question along with the SQLGuru's opinions on which is better as a key value.

Article Link.

Anonymous
Starting Member

0 Posts

Posted - 09/20/2000 :  08:15:31  Show Profile  Reply with Quote
How do you get the value of a uniqueidentifier column after an insert?

One way to get the value is to create it at the client (don't let SQL Server create it). This saves you from having to make a call to the server to get the GUID before the insert or resyncing your recordset after the insert.

I have found several VB code examples to create a GUID:
http://www.freevbcode.com/ShowCode.Asp?ID=21

http://www.buygold.net/v01n01/v01n01.html

Initially I had written a stored procedure to return a uniqueidentifier but found the above method to be preferrable. It works very well for situations where you want to enter a parent record and related child records at the client while not having an active connection to the database (mobile computing, MSMQ, etc.).

Hope this helps,
darryl.caillouet@alltel.net

Go to Top of Page

mron0210
Starting Member

2 Posts

Posted - 08/08/2002 :  11:39:26  Show Profile  Reply with Quote
Hi,

I am trying to generate a Globally Unique Identifier, some thing similar to the SEQUENCE command in Oracle. I believe GUID in MS SQL Server works or I can use the IDENTITY key. How do I retrieve them without having to place them in the database table (i.e without creating a record in a particular table with IDENTITY or GUID key)? Can I retrieve multiple ID's with one statement or a batch ?

Thanks


Go to Top of Page

sampson
Starting Member

2 Posts

Posted - 08/23/2004 :  14:57:41  Show Profile  Reply with Quote
to get the GUID of the last INSERT (or UPDATE for that matter) could you not include a trigger that assigns it to a global variable or some other table... then retrieve it from that?

This should work since the trigger would have access to the value just prior to the table being updated.

David

quote:
Originally posted by AskSQLTeam

Kim writes: "with an identity column you can SELECT @@identity after inserting a row. How do you get the value of a uniqueidentifier column after an insert? thanks!"<BR><BR>Uniqueidentifiers are new to SQL 7.0, and behave a little differently than our friend the IDENTITY column. Read on for the answer to Kim's question along with the SQLGuru's opinions on which is better as a key value.<P>Article <a href="/item.asp?ItemID=283">Link</a>.

Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/23/2004 :  15:07:00  Show Profile  Reply with Quote
How about:

DECLARE @MyGUID uniqueidentifier
SELECT @MyGUID = NewID()
INSERT INTO MyTable(Col1, Col2, ColGUID, Col3)
   VALUES('aaa', 'bbb', @MyGUID, 'zzz')
SELECT @MyGUID AS MyGUID

Kristen
Go to Top of Page

dogutumerdem
Starting Member

2 Posts

Posted - 10/13/2005 :  05:22:28  Show Profile  Reply with Quote
you can use NEWID() function in default value of the related field, so, in insert statement you don't need to refer that column again.

to get back your inserted rows or id column you can use such a trigger

SELECT * FROM INSERTED
UNION
SELECT * FROM DELETED

INSERTED and DELETED tables are only available in triggers.

unfotunately I cannot find an @@IDENTITY varible for uniqueindentifiers even sql server 2005 CTP
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/13/2005 :  09:30:33  Show Profile  Reply with Quote
"SELECT * FROM INSERTED
UNION
SELECT * FROM DELETED
"

I can't think of any good reason to put that in a trigger - its going to generate masses of output to whatever process attempts to insert into the table.

If you want to know the GUID of a newly inserted record either use a Stored Procedure that allocates it, uses it in the insert and then returns it to you, or allocate it in the client application and pass it to SQL Server along with the other data for the row.

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 10/13/2005 :  09:39:44  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>unfotunately I cannot find an @@IDENTITY varible for uniqueindentifiers even sql server 2005 CTP

Refer this
http://vyaskn.tripod.com/retrieve_guid_value_like_identity.htm

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

dogutumerdem
Starting Member

2 Posts

Posted - 10/17/2005 :  02:53:32  Show Profile  Reply with Quote
thanks, article is clear but,

when we write that much scripts, no problem... :-(

I think a variable like @@IDENTITY is a must...
Go to Top of Page

thirumaran
Starting Member

1 Posts

Posted - 04/04/2006 :  04:54:31  Show Profile  Reply with Quote
Hi Sqlteam,

I read your article "Uniqueidentifier vs. IDENTITY"
ref: http://www.sqlteam.com/item.asp?ItemID=283

here you have mentioned in the last line
"Unless you need a truly globally unique identifier, you're probably better off sticking with an IDENTITY"

what does truly globally unique identifier mean ? example will be very usefull

thanks
thirumaran
Go to Top of Page

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 04/04/2006 :  11:58:25  Show Profile  Visit MichaelP's Homepage  Reply with Quote
"truly globally unique identifier" = Almost never duplicated anyplace

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 04/04/2006 :  12:18:29  Show Profile  Reply with Quote
"truly globally unique identifier = Almost never duplicated anyplace"

ROTFL!
Go to Top of Page

MichaelP
Jedi Yak

USA
2489 Posts

Posted - 04/04/2006 :  13:39:09  Show Profile  Visit MichaelP's Homepage  Reply with Quote
Well, it is possible to create duplicate GUID's. It's HIGHLY unlikly that you'll ever generate the same GUID on the same machine though.

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 04/05/2006 :  00:43:04  Show Profile  Reply with Quote
If a duplicate GUID is possible then you have to program for that possibility ... would be nice not to have to IMHO.

Kristen
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/05/2006 :  09:31:40  Show Profile  Reply with Quote
quote:
Originally posted by Kristen

If a duplicate GUID is possible then you have to program for that possibility
It's possible that the Sun will explode tomorrow as well, though we don't feel the need to include this scenario in our disaster recovery plan.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 04/05/2006 :  11:16:53  Show Profile  Reply with Quote
Yeah, but whats going to happen to the application's user when they get a duplicate GUID that isn't trapped by the application?

Normal scenario is that a Pissed-off User tells 10x mates how crap the site is ....

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 04/05/2006 :  11:47:36  Show Profile  Visit spirit1's Homepage  Reply with Quote
so what... the certanity of getting 2 identical GUID is probably in
the region of sun exploding in the next million years...

Go with the flow & have fun! Else fight the flow
Blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/05/2006 :  12:51:00  Show Profile  Reply with Quote
My point exactly.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 04/05/2006 :  13:03:25  Show Profile  Reply with Quote
Time was when the GUID was based on the NIC address. AFAIK that gave a Unique GUID. Then some do-good'ers said that that was an invasion of privacy, so the NIC stuff was removed, resulting in the evolution of a significantly less-unique algorithm.

As I understand it the odds are still good (but not AS good as before) of getting unique GUIDs from a given machine. However, GUIDs generated on multiple machines are much less likely to be unique than b.d.g. ("Before do-good'ers") - e.g. generate a large batch of GUIDs and then reboot the machine ....

But I'd be very pleased to learn that I have got that wrong!

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 04/05/2006 :  13:09:22  Show Profile  Visit spirit1's Homepage  Reply with Quote
well you can always stay true to your Handle and test it....
1. insert 10 million guids into table1
2. restart the server
3. insert 10 million guids into table2
4. union them


Go with the flow & have fun! Else fight the flow
Blog thingie: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

jeffreymfischer
Starting Member

USA
10 Posts

Posted - 09/13/2009 :  00:42:35  Show Profile  Reply with Quote
I've written a detailed article on an enterprise-ready unique identifier solution.

http://blog.scoftware.com/post/2009/08/29/SQL-Server-UniqueIdentifier-Globally-Unique-Globally-Sequential-SOLUTION.aspx

Read it and provide feedback.

Jeff

Scoftware Achitect/Developer
http://blog.scoftware.com
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000