Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

'scope_identity()' for uniqueidentifer

Posted on 2006-11-19
5
Medium Priority
?
976 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
  • 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 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

972 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