?
Solved

'scope_identity()' for uniqueidentifer

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database 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.
Suggested Courses

578 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