Re: SQL Server Autonumber equivalent in Oracle 8i

On Mon, 20 Nov 2000, rwmartin_at_sisconet.com wrote:

> I am a newbie to Oracle and I have read that I can create an
> SQL Server Autonumber equivalent by using a sequence and a
> trigger. 

These two things aren't really equivalent, and I would consider the SQLServer implementation much more limiting.

The SQLServer id:
- Is created as you create the table and is completely tied to the
  table.
- You don't need to reference it upon insert, it just gets
  autoincremented.
- It gets refreshed on truncate.

• When creating a transaction to insert parent and children you have to ask for something like "_at__at_id" (I forget the exact syntax), store that in a variable and then reference it in the subsequent child insert.

The Oracle sequence:
- Is an object that can be selected from and isn't tied to a
  table.
- Most designs will tie most of these to tables through
  application use though.
- When you are creating a transaction and inserting into child
  tables, you insert the parent with "seq_name.nextval" and then   you insert the child with "seq_name.currval". No need to   create a variable to hold the generated id for subsequent insert.
- Because they aren't tied to a specific table, you can use them
  in a much more modular fashion, for example, just a specific id   generation tool if needed.

This design along with the case statement make one start really liking Oracle's design. Pound for pound, maybe one of the simplest yet most useful designs I have seen.

HTH and I hope I got it all correct.

-- 
Galen Boyer
New Orleans is sink'n man and I don't want to swim.