July 21, 2005 at 3:19 am
Hi,
Does anybody know how to generate primary keys automaticaly without using IDENTITY Column property and without affetcing concurrency.
Cheers
Rajesh
July 21, 2005 at 3:28 am
Check out the NEWID() function in BOL and see whether that suits.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 21, 2005 at 4:11 am
Has to be an integer. I don't want to use a 16 bit Unique identifier.
July 21, 2005 at 4:52 am
Could you describe a bit more what you are trying to achieve?
July 21, 2005 at 5:01 am
When you say 'automatic' - I presume you mean exactly that, ie users do not need to do anything special, they just INSERT to the other fields and the key looks after itself?
If the answer is yes, please describe why you don't want to use IDENTITY.
You may be able to implement a trigger that does this for you - but what will it give you that IDENTITY won't?
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 21, 2005 at 5:23 am
Sorry for all the confusion.
I have a program that work exaclty as of an identiy property. But it doesn't support concurrecy as it does like IDENTITY column property.
We are not able use the IDENTITY becasue the application work altogether in a different way.
Cheers
Rajesh
July 21, 2005 at 5:58 am
I'm sorry, but I'm afraid that confusion still reigns in my mind! Why would you want to write a program that works exactly like IDENTITY?
Does your prog need to generate a unique key as part of its code? That is, is this a SQL question or a VB/SQL question - I presume the latter, otherwise IDENTITY would be fine.
More info please.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 21, 2005 at 6:31 am
Well,
To be precise, I would like to know is there any method in SQL Server like
autonomous transaction pragma in Oracle ?
Cheers
July 21, 2005 at 7:30 am
I have never worked with Oracle - could you describe further, please?
July 21, 2005 at 7:42 am
autonomous transaction pragma
I think is equivalent to transaction in TSQL
so you will need
BEGIN TRANSACTION
your code
IF @@ error=0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTIOn
Vasc
July 21, 2005 at 9:17 am
The solution is the Key-Table method
You can create a table with the following structure:
Create Table Allkeys (
TableName varchar(128) PRIMARY KEY,
Key int default(0)
)
and you will have a procedure like
create proc LastKey (@Tname varchar(128), @Amount int)
as
begin
declare @Lastkey int
Update Allkeys set @Lastkey = Key = Key + @amount where TableName =@TName
if @@Error <> 0
Return 0
else
Return Isnull(@Lastkey,0)
end
and this is very much like your identity. You can even get fancy and create ranges, rollover intervals, etc but this is basically the functionality you asked for
cheers!
* Noel
July 21, 2005 at 9:52 am
autonomous transaction pragma =
http://www.unix.org.ua/orelly/oracle/guide8i/ch02_02.htm
But what does this have to do with the use, or absense of an identifier.?
Are you trying to guarantee the Key values are synchronous, No gaps?
Is there a business reason for this? if so, I would keep the surrogate primary key value separate from this important synchronous value.
July 21, 2005 at 10:59 am
declare @NewKeyValue int
select @NewKeyValue = max(YourIDKeyValue) from yourtable
insert yourtable (YourIDKeyValue ..............)
@NewKeyValue , ......................
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply