If you have to rely on the next value in a sequence, you
are in serious trouble. The sequences 'NextVal' is to
store a new value in a column which values are retrieved
from a sequence, it shouldn't be used for something
else. 'CurrentVal' or 'Curr' is then used like
'SCOPE_IDENTITY()' (sqlserver2000) or '@@IDENTITY'
(sqlserver 7).
Sequences are nice, but if possible you always should
rely on unique data that is semantically part of the
entity, i.e. already in an attribute.
You can simulate sequences using functions (sqlserver
2000) which update a table with a serialized
transaction. Thus your table contains 1 row per
sequence, and the function uses a serialized transaction
(thus has unique access guaranteed) to retrieve and
update the sequence number. When the sequence function
fails, the insert will then also fail.
It's a tricky business and when doing it by yourself it
can be slower than when the RDBMS does it. How
especially does your code rely on the NExtVal function?
Frans: Not using it to INSERT or anything, i AM using
unique IDENTITY columns. This ID is used by some of our
C++ code to map unique objects in memory. I know, it's
not the best design, but that's how it is with legacy
code. Too ugly to change now ....
btw, Jayme: Your solution seems to be the one. We'll try
it and see what happens.
Thanks !:)
the MAX(id) doesn't work in a multi-user environment. If
after the SELECT MAX(field)... another insert is done in
the same table, you have double keys. The only solution
is a separate table with own counters and a function
which uses a serialized transaction.
There are also issues with DBCC statements in stored
procedures. I'd not use these statements in production
code, since they are ment for DBA's.
MAX(id)+1 isn't exactly the best idea, because what if
the last row was deleted? Now your id generation is off.
Additionally, you can't be gaurenteed that ID. What
happens if between the query and the update someone
inserts a new record? I guess you could make sure the
table was locked down until you update, but performance
is sucky that way.
A much better idea is not to rely on having to know the
identity until after it has been placed in the DB and
you can query it with a SQL statement, or to use GUIDs
or something that you can calculate ahead of time (or
event a GetID function that pulls IDs out of a DB table
or from some other source...seen that done before for
this type of thing). Of course, if it is legacy code,
then I guess you are stuck...
Frans: Yeah, I'm aware of the GetMax()+1 issue with
multi user scenarios. But from what I gather it looks
like it's impossible to do it withoug going through a
million hoops. That's toatlly annoying. Why is this
basic functionality not implemented in SQL server? boo.
Looks like Frans beat me to it. Great minds think alike
I guess ;-)
It isn't there, because you should never do such things.
Jesse: So I guess ORACLE got it all wrong when they
added this feature? Better yet: They Added a
Multi-Column Sequence, allowing you to have multiple
tables implementing one unique sequence.
"Why is this basic functionality not
implemented in SQL server?" Because its not
supportable in a multi-user environment. Its not really
a very simple operation, when you think about it.
IDENTITY works because it is easy (?) to implement - all
you need to do is lock the table, look at the previous
record, add one, insert, and then unlock the table. It
easily passes the ACID test, while what you are
proposing does not.
If you don't like it, you can always port to Oracle. I
hear they are selling db licenses cheap. :)
Jesse :)
Roy: it's not that big of a deal I think. I get the
feeling it's more of a scheduling problem in your code:
which does what first. The row inserted gets a key which
is also the id of an object? but the object has to get
the id first before the row does?
Isn't it better to indeed just drop the 'identity' flag
from the column and implement the sequence then from
code? (f.e. by calling a serialized transaction-based
stored proc which returns a new unique key. That key is
used for the column and is guaranteed unique in hte
database, plus is used in the object. You can keep your
db format then.
Eric: It *should* be supportable, that's my point. No, I
*don't* want to move to oracle just because I don;t have
this functionality.
Frans: We were trying not to change the legacy code...
But it seems like this is what we'll do.
Eric: Oracle's sequencename.NextVal works also in a
multi-user environment (If I may believe the Oracle docs
:) . Calling that function will update the current value
of the central stored sequence. However NextVal is not a
'peek' function, it's an increase. So when another
thread calls nextval again, the value is incremented
again. If you use it wrong, then indeed the multi-user
aspect is killing you :)
Frans: That was the point I guess I failed to make. I
have to agree with you, if its important that the
application have a value before the data is inserted,
then the application should accept the responsibility
for generating this value and ensuring uniqueness.
Roy: When you get down to it, there are many ways to
implement Identity/Sequence, MS chose to integrate it
into the table itself, while Oracle chose to set it up
as a standalone entity. In most cases, it probably
doesn't make a difference, but in your case, it
obviously does. I wouldn't say that it makes Microsoft's
implementation wrong or lacking, just different. In
theory, you could build a mechanism to do this as an
external DLL, but that would probably take a lot of
time.
Why not use GUID's? You get a reduction on database load
because you don't have to go to the database to generate
new keys...
To get the same type of quid string on each system I do:
SQL Server :
(replace(convert(varchar(50),newid()),'-',''))
Oracle: sys_quid()
imho, sequences are awful... I’d much rather have a
distributed key generation mechanism…
if you have Sql2k then use scope_identity() instead of
@@identity...if the table has a trigger, which inserts
to another table with an identity column, then
@@identity will return the value inserted by the
trigger, while scope_identity() returns the ident you
inserted yourself.
check out the [autoval] column in the [syscolumns]
table. that's where this value is stored, i believe...
Kenneth: Yeah . Jayme(first comment) posted a link to a
post about how to use that column. Nice trick there...