• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 986
  • Last Modified:

'scope_identity()' for uniqueidentifer

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
dave_gr
Asked:
dave_gr
  • 3
1 Solution
 
NightmanCTOCommented:
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
 
NightmanCTOCommented:
Obviously instead of using NewID() in those examples you would substitute it with newsequentialid()
0
 
NightmanCTOCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree with Nightman, you usually use a stored procedure with output parameter to generate and return the newid() function explicitely
0
 
dave_grAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now