Solved

'scope_identity()' for uniqueidentifer

Posted on 2006-11-19
5
956 Views
Last Modified: 2010-08-05
Hi,

Is there a way of obtaining the ID of a newly inserted record where this is defaulted to newsequentialid()  in the table definition.  For int identities you can use '@@identity' or scope_identity() however when I try and use scope_identity() I get the following error message:

Operand type clash: numeric is incompatible with uniqueidentifier

Thanks,

David
0
Comment
Question by:dave_gr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 17975339
You can't use scope_identity, as you have already noticed. However, You can set it internally in a stored procedure and use it as an output parameter. Good example at: http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21202671.html
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17975344
Obviously instead of using NewID() in those examples you would substitute it with newsequentialid()
0
 
LVL 29

Accepted Solution

by:
Nightman earned 500 total points
ID: 17975413
Correction here - newsequentialid() can ONLY be used as a default value - you can't use it to set the value of a local variable (or in fact any other scalar expression), so your options are limited. Is that the only unique value in your table? If so, you may have to consider newID (just DON'T cluster on it) or create a regular autonumber (e.g. int, bigint) for the clustered key and use newID as part of a covering index.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17975436
I agree with Nightman, you usually use a stored procedure with output parameter to generate and return the newid() function explicitely
0
 
LVL 3

Author Comment

by:dave_gr
ID: 17975484
OK - newid() it must be then.

To give you a bit of background I am developing a system that requires multiple copies of a central database to run indepentently and concurrently.

We need it to be entirely stand alone on individual users laptops, then allow them to re-sync their (field) work into the main db - hence int IDs are no use as they won't be unique.  I wanted to keep clustered indexes on all the tables but it looks like this isn't possible now so I'm going to have to sacrifice a little bit of query performance for sensible insert overhead.

Thanks for your help,

David
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question