Returning identity of last inserted row (uniqueide

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Returning identity of last inserted row (uniqueide

Author

Topic

tiwas
Starting Member

37 Posts

Posted - 2010-07-25 : 14:51:47

Hi,

Is there any way to do this? I tried using a 'select scope_identity()', but that returns null, apparently because my identity column is not a numeric of any sort.

An answer to this would help me a lot! I would love to not have to use an integer or similar...

Cheers!

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-07-25 : 14:56:04

An IDENTITY is by default an int or bigint. It sounds like you want an identity value that is not being set/managed by the IDENTITY property. If so, I think your only option is to either query the table using another unique key or using OUTPUT on the INSERT statement.

tiwas
Starting Member

37 Posts

Posted - 2010-07-25 : 16:22:31

Ok, that's what I was afraid of...changing it to bigint, then :)

parody
Posting Yak Master

111 Posts

Posted - 2010-07-26 : 04:14:20

you could add a true identity to the table, then query your idenity by select yourcolumn from yourtable where trueid = scope_identity()

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-26 : 05:26:35

Use the OUTPUT clause in your INSERT statement to get the GUID value.

N 56°04'39.26"
E 12°55'05.63"

Sachin.Nand

2937 Posts

Posted - 2010-07-26 : 06:16:54

Here is a small example of what Peso is trying to say.


create table #tbl (id int)
insert #tbl output NEWID() values(10)
select * from #tbl
drop table #tbl

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-26 : 06:21:38

No, this is what I am talking about

create table #tbl (uuid uniqueidentifier default newid(), m int)
insert #tbl (m) output inserted.uuid values(10)
select * from #tbl
drop table #tbl

N 56°04'39.26"
E 12°55'05.63"

Sachin.Nand

2937 Posts

Posted - 2010-07-26 : 06:33:40

quote:
Originally posted by Peso

No, this is what I am talking about
create table #tbl (uuid uniqueidentifier default newid(), m int)
insert #tbl (m) output inserted.uuid values(10)
select * from #tbl
drop table #tbl

N 56°04'39.26"
E 12°55'05.63"

But your example forces the OP has to make schema changes to his table which he is already reluctant to do.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

parody
Posting Yak Master

111 Posts

Posted - 2010-07-26 : 08:41:20

Using an independant select with no reference to the insert via either the output clause or scope_idenity could be very dangerous - you could easily return an ID from another processes insert even if it is sequential. You would need to ensure you have an exclusive lock on the table until you have retrieved the identity, and take the hit on concurrency.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-26 : 08:53:22

No. Fail.

SCOPE_IDENTITY() is "locked" to your process. The only time it can fail is if you are using partitions, but it still returns value triggered in your own process. However, the value can derive from a trigger inserting into an auxiliary table.

If you still think otherwise, please post link for verification.

N 56°04'39.26"
E 12°55'05.63"

parody
Posting Yak Master

111 Posts

Posted - 2010-07-26 : 09:56:06

No. Fail on attention to detail!! :-)

please re-read my post... specifically the first sentance.

My point was that Idera's original suggestion could cause this without ensuring an exclusive lock, i.e selecting after the insert. Only using scope or output will ensure reference to the select that session performed.

Sachin.Nand

2937 Posts

Posted - 2010-07-26 : 12:10:28

quote:
Originally posted by parody

No. Fail on attention to detail!! :-)

please re-read my post... specifically the first sentance.

My point was that Idera's original suggestion could cause this without ensuring an exclusive lock, i.e selecting after the insert. Only using scope or output will ensure reference to the select that session performed.

There is absolutely no need to use the select statement.It is just by chance that I used it.The insert statement itself with the output clause will give the Newid() value.
Just try this in the Query Analyser


create table #tbl (id int)
insert #tbl output NEWID() values(10)
drop table #tbl

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

tiwas
Starting Member

37 Posts

Posted - 2010-07-27 : 03:49:56

Thanks for the in-depth answers, and I will try to do this next time around. As for my current table, I changed it to use a bigint, which should be sufficient (although I like uniqueidentifiers better).

Cheers!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources