|
Oracle sequences and SQL Server identity
I'm looking at a web application designed for an Oracle back-end. I'm trying to make it work with a SQL back-end. I'm running into this situation:
The app is designed to check the next sequence number in Oracle, get that number, do some processing in the app, use that number to insert the parent data, then use that same number to insert the child data in different table (foreign key relationship).
Doesn't this leave the situation wide open to two processes requesting the next sequence number at the same time, inserting the parent data only once (because the sequence number is already used) and then overwriting or duplicating the child data?
In SQL Server, I would insert the parent data, get the key created when inserted (using identity field), and then get the ID created for my parent data as my foreign key for the child data. Since the ID is created when inserted, there's never a state where the application has an ID for data that doesn't exist in the database.
Why does Oracle use these sequence numbers? What's the benefit? Doesn't it increase the risk of data corruption?
nathan
Wednesday, January 21, 2004
Sequences are built so they will only return incremental numbers - you can't get the same number twice out of a sequence.
The reason for sequences is to make transactions atomic - you can get a PK to work with for the parent record, use it for child records, and commit the whole thing at once.
@@identity had to be created after the fact to deal with this - before it was added (SQL 7?) there were all kinds of contortions necessary to get the PK just created, all the while having a quasi record actually sitting in the database (and if you're in "read uncommitted" then possibly dirtying data)
As things stand now, sequences vs. identity/@@identity are simply two different approaches to accomplish the same functionality.
Philo
Philo
Wednesday, January 21, 2004
Nathan,
This is a case in which selecting actually changes data!!
When you say:
SELECT fooseq.nextval
FROM foo
You burn a sequence number. So if someone else comes along and runs SELECT fooseq.nextval they will get the number after you.
MR
Wednesday, January 21, 2004
(and by "changes data" I really mean "changes something"; not really data)
MR
Wednesday, January 21, 2004
Well, SQL Server has the uniqueidentifier type. If you have disk space to burn, you can use this uniqueidentifier in much the same way.
DECLARE @myvar uniqueidentifier
SET @myvar = NewID()
-- do someting here
INSERT tbl (MyPK)
VALUE (@myvar)
m
Wednesday, January 21, 2004
Is SEQUENCE part of ANSI SQL? Having come across it in Oracle and other databases I prefer the use of SEQUENCES.
Walter Rumsby
Wednesday, January 21, 2004
Ask Joe: http://www.celko.com/
m
Wednesday, January 21, 2004
In Oracle, the statement:
SELECT fooseq.nextval
FROM foo;
will increment the fooseq by the number of rows that are in the foo table. Also it will do a full table scan on foo, which may really slow down your application.
The sequence variable is not associated with any table or column. It is a global entity.
The proper way to retreive the next value to use is:
SELECT fooseq.nextval
FROM DUAL;
The "DUAL" table is a special oddball in Oracle that is a place holder table to satisfy the select syntax requirement of a "from" table. For example to get the current date, do
SELECT sysdate FROM DUAL;
Glade Warner
Wednesday, January 21, 2004
Walter,
I'm not sure if SEQUENCE is part of the ANSI standard, but I do know that it's not universally available across all databse implementations. In particular, it's not in MS SQL Server (just as MS SQL Server's answer to the problem is not implemented by Oracle).
The auto-generation of unique identifiers for records is probably the biggest "gotcha" for trying to write database-portable applications. If portability is important to you then you need to roll your own unique identifier generator (or find a 3rd party library).
Bill Tomlinson
Thursday, January 22, 2004
 Recent Topics
Fog Creek Home
| 
