SQL Server is Missing NextVal Functionality

My blog has moved.
You can view this post at the following address:
http://www.osherove.com/blog/2003/6/18/sql-server-is-missing-nextval-functionality.html
Published Wednesday, June 18, 2003 1:31 PM by RoyOsherove
Filed under:

Comments

Wednesday, June 18, 2003 2:53 AM by Jayme

# re: SQL Server is Missing NextVal Functionality

Maybe this will help?
http://beta.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_10350313.html
Wednesday, June 18, 2003 3:09 AM by Frans Bouma

# re: SQL Server is Missing NextVal Functionality

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?
Wednesday, June 18, 2003 3:13 AM by Roy Osherove

# re: SQL Server is Missing NextVal Functionality

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 !:)
Wednesday, June 18, 2003 3:20 AM by Frans Bouma

# re: SQL Server is Missing NextVal Functionality

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.
Wednesday, June 18, 2003 3:26 AM by Jesse Ezell

# re: SQL Server is Missing NextVal Functionality

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...
Wednesday, June 18, 2003 3:27 AM by Roy Osherove

# re: SQL Server is Missing NextVal Functionality

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.
Wednesday, June 18, 2003 3:28 AM by Jesse Ezell

# re: SQL Server is Missing NextVal Functionality

Looks like Frans beat me to it. Great minds think alike I guess ;-)
Wednesday, June 18, 2003 3:31 AM by Jesse Ezell

# re: SQL Server is Missing NextVal Functionality

It isn't there, because you should never do such things.
Wednesday, June 18, 2003 3:35 AM by Roy Osherove

# re: SQL Server is Missing NextVal Functionality

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.
Wednesday, June 18, 2003 3:37 AM by Eric Kepes

# re: SQL Server is Missing NextVal Functionality

"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. :)
Wednesday, June 18, 2003 3:37 AM by Frans Bouma

# re: SQL Server is Missing NextVal Functionality

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.
Wednesday, June 18, 2003 3:42 AM by Roy Osherove

# re: SQL Server is Missing NextVal Functionality

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.
Wednesday, June 18, 2003 3:43 AM by Frans Bouma

# re: SQL Server is Missing NextVal Functionality

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 :)
Wednesday, June 18, 2003 3:58 AM by Eric Kepes

# re: SQL Server is Missing NextVal Functionality

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.
Wednesday, June 18, 2003 4:14 AM by Chad Brockman

# re: SQL Server is Missing NextVal Functionality

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:

.net : System.Guid.NewGuid().ToString("N")
SQL Server : (replace(convert(varchar(50),newid()),'-',''))
Oracle: sys_quid()

imho, sequences are awful... I’d much rather have a distributed key generation mechanism…


Wednesday, June 18, 2003 5:04 AM by dennis

# re: SQL Server is Missing NextVal Functionality

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.
Wednesday, June 18, 2003 7:54 AM by Kenneth LeFebvre

# re: SQL Server is Missing NextVal Functionality

check out the [autoval] column in the [syscolumns] table. that's where this value is stored, i believe...
Wednesday, June 18, 2003 8:07 AM by Roy Osherove

# re: SQL Server is Missing NextVal Functionality

Kenneth: Yeah . Jayme(first comment) posted a link to a post about how to use that column. Nice trick there...