| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-08-04 : 18:04:16
|
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. |
|
|
mron0210
Starting Member
2 Posts |
Posted - 2002-08-08 : 11:39:26
|
| 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 |
 |
|
|
sampson
Starting Member
2 Posts |
Posted - 2004-08-23 : 14:57:41
|
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.Davidquote:
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>.
|
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-23 : 15:07:00
|
How about:DECLARE @MyGUID uniqueidentifierSELECT @MyGUID = NewID()INSERT INTO MyTable(Col1, Col2, ColGUID, Col3) VALUES('aaa', 'bbb', @MyGUID, 'zzz')SELECT @MyGUID AS MyGUIDKristen |
|
|
|
dogutumerdem
Starting Member
2 Posts |
Posted - 2005-10-13 : 05:22:28
|
| 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 triggerSELECT * FROM INSERTEDUNIONSELECT * FROM DELETEDINSERTED and DELETED tables are only available in triggers.unfotunately I cannot find an @@IDENTITY varible for uniqueindentifiers even sql server 2005 CTP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-13 : 09:30:33
|
| "SELECT * FROM INSERTEDUNIONSELECT * 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 |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
dogutumerdem
Starting Member
2 Posts |
Posted - 2005-10-17 : 02:53:32
|
| thanks, article is clear but,when we write that much scripts, no problem... :-(I think a variable like @@IDENTITY is a must... |
|
|
|
thirumaran
Starting Member
1 Post |
Posted - 2006-04-04 : 04:54:31
|
| Hi Sqlteam, I read your article "Uniqueidentifier vs. IDENTITY" ref: http://www.sqlteam.com/item.asp?ItemID=283here 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 usefullthanksthirumaran |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-04-04 : 11:58:25
|
| "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. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-04 : 12:18:29
|
| "truly globally unique identifier = Almost never duplicated anyplace"ROTFL! |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-04-04 : 13:39:09
|
| 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. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-05 : 00:43:04
|
| If a duplicate GUID is possible then you have to program for that possibility ... would be nice not to have to IMHO.Kristen |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-04-05 : 09:31:40
|
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. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-05 : 11:16:53
|
| 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 |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-04-05 : 11:47:36
|
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: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-04-05 : 12:51:00
|
| My point exactly. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-04-05 : 13:03:25
|
| 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 |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-04-05 : 13:09:22
|
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: [URL="http://weblogs.sqlteam.com/mladenp"] |
|
|
|
jeffreymfischer
Starting Member
10 Posts |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-09-15 : 12:27:07
|
| Looks thorough, but use of NEWSEQUENTIALID() is rather old news, and fairly well documented already.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
Next Page
|
Article: Uniqueidentifier vs. IDENTITY