Generation of auto number

[vaidyanathanpc]

Hi,
Is there anyway I can generate an auto number without using the 'identity' propery for a table? I don't want to the autonumber by incrementing values programatically.

Thanks in advance
P.C. Vaidyanathan

 

[Allumo]

Yes. You can use triggers. But, why don't you wanna use identity?

 

[DBCustard]

You can maintain a seperate table to hold the last value of the key for all other tables:

NextIdentifier:
TableName varchar(255)
NextIdentifier int


Everytime you need to write row to a table you have to read this table, then .

The big problems with this are:
You always incur another read and write for every insert
Set based instructions to insert rows are difficult, because you have to increment the counter.


Personally I don't understand why identities are such a problem - I find them very convenient, though I have found duplicates in them in the past!

 

[BoghieWanKanobe]

Howdy,

You can use Globally Unique Identifiers (GUIDs) with the default set to newid().

Like the following:
BLAH uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [MyConstraint] DEFAULT (newid())

When a record is inserted into the table the database will call newid() to create the default contents of BLAH. Still learning about the CONSTRAINT stuff though...

Roger Bogh

 

[Rosko]

If you're afraid to have gaps in a range of id's, you can use this query to search the first available number.

ID
--
1
2
3
5
6


select min(id)+1 from table
where id+1 not in (select id from table)

Maybe this is what you are lokking for


Rosko

 

[Rosko]

Which will return 4 in previous example.

Or you can use the RAND function (BOL) to generate your random id


GL


Rosko