asked on

'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

Nightman

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: https://www.experts-exchange.com/questions/21202671/Return-Scope-Identity-as-output-param-of-type-uniqueidentifier-wth-default-value-of-NewID.html

Nightman

Obviously instead of using NewID() in those examples you would substitute it with newsequentialid()

ASKER CERTIFIED SOLUTION

Nightman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

I agree with Nightman, you usually use a stored procedure with output parameter to generate and return the newid() function explicitely

dave_gr

ASKER

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