Unique value independent of an IDENTITY column

[NuWood]

Hi

In ORACLE I can get a unique value from a sequence and use it in anyway I need to without reference to a tables column

In SYBASE we have IDENTITY to create a unique value but I need a way to produce a unique value for a column but not for every record. There are certain record types that require this unique value and the rest just have NULL. Is there anything in SYBASE that gives a sequence value independent of IDENTITY columns?

I know I can select max(fieldname)+1 .... but I just wondered if there is a more slick way that doesn't require locking.

 

[dickiebird]

I need to know how to do that, too !

Dickie Bird (-)))

 

[NuWood]

Hi

I got no answer so I ended up doing this

Created a table

CREATE TABLE sss_unique (
id integer IDENTITY NOT NULL,
dummy varchar(10) NOT NULL
)

created a procedure

create procedure sss_get_unique_ref(@ref integer output)
as
begin
begin transaction
insert into sss_unique(dummy) values('')
select @ref = id from sss_unique
rollback transaction
end

I can call the procedure and take the returned parameter @ref but the transaction rolls back so the table never acquires a record.

Bit of a dogs breakfast but it works.

In ORACLE I could just select nextval from a sequence and be done with it.

I am sure SYBASE has a better way but I do not have the time to find it


regards

 

[JeanNiBee]

Hi

In 12.5.0.3 or up you have a new function called newid()

This generates a 16 bytes globally unique identifier. These are in the format of (varchar(32) or varchar(36))

Passing a non-zero value as a parameter 'switches' it from 32 chas to 36 by adding 'dashes' through the GUID.

These number are uniques even across multiple systems on the same network as it uses the MAC address as a piece of the seed value to ensure uniqueness across the globe.

Hope this helps.

-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects