Sender: gboyer@GBOYER_L00 Newsgroups: comp.databases.oracle,comp.databases.oracle.server,comp.databases.oracle.tools Subject: Re: SQL Server Autonumber equivalent in Oracle 8i References: <8vbgkh02kif@enews3.newsguy.com> From: Galen Boyer Message-ID: Lines: 41 User-Agent: Gnus/5.0807 (Gnus v5.8.7) Emacs/20.7 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Abuse-Info: Please be sure to forward a copy of ALL headers X-Abuse-Info: Otherwise we will be unable to process your complaint properly X-Complaints-To: abuse@webusenet.com NNTP-Posting-Date: Tue, 12 Dec 2000 15:58:42 EST Organization: WebUseNet Corp http://www.usenetserver.com - Home of the fastest NNTP servers on the Net. Date: 12 Dec 2000 15:57:20 -0500 On Mon, 20 Nov 2000, rwmartin@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 "@@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.