Solved

'scope_identity()' for uniqueidentifer

Posted on 2006-11-19
5
944 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
Comment Utility
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
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now