I have a wishlist entry outstanding for the ability to handle identity columns as primary keys for SQl server, but given the validation path of Uniface, I don't hold out much hope. 

Having a separate path to the DBMS can be costly (as in $$$'s) if you're running in a traditional C/S environment (each user having at least two connections to the DBMS - companies such as Oracle LOVES this... (think concurrent users)). You could open / close the path for each sequence number - but that's impractical due to the performance hit.... However, you could head down the path of using an ASV and all users grab sequence numbers from there. This would solve the double up connection issue since the ASV is running in a separate process and will have its own connection / commit path to the DBMS. As the number of users / transactions go up, you can scale up by allocating specific ASV server processes to sequence number generation - or even one ASV server process to each heavily hit sequence number.

Perhaps the Helppage "Sybase Identity Fields" may help (if functionality implemented in the SQL driver as well)

Hi, As "Sequence" is supported in Sql Server since Sql-server 2012, that would be a good idea. Before that it can be simulated. I suppose that the IDENTITY concept of Sql-Server can't be used with Uniface, as the identifier then is available too late? Regards RogerW.

I would go for a Sequence in the DBMS. It should be faster than a select max and it eliminates the need for a separate transaction and/or database connection path. Wrap it in a Uniface Service so there is only one place to change if you want to use a different DBMS.

Ok, thanks Uli and Iain.  I understand, I have used "separate" transaction somewhere else. This application will probably be used by one service person, but best to do it the right way. So if I understand it right, a late rollback of the main transaction will waste some keys, but that does usually not matter. Regards RogerW.     

and for nostalgics like me who started in uniface-1993: the documentation still holds (good?) old NUMGEN

There are of course the more DBMS specific options too - like sequence numbers in Oracle et al where you fire a SQL statement like: sql "select sequence_name from DUAL;" == Oracle Specific - and in this case Oracle handles the commit on its own... Yes, it requires some SQL code - but major DBMS's have this today and, well, why not use it??? (Of course, the DBMS independence is always there - but speaking from an inhouse development perspective rather than from a VAR/ISV specific perspective). To be honest, I don't know to many ISV/VAR's who'd like to deploy / support their application on CISAM or RMS these days...

The separate transaction requires a counter table, because it would perforce lock up if it tried to use the max statement.  This is what we do, and for the reason Uli mentions. If you are inserting many records in one transaction, and it's possible another user will be trying to insert records in their transaction, then the second user to get there will be locked awaiting the first transaction to finish. (Unless the entire thing goes on so long the locks time out.) The important thing in Uli's comment is the "separate transaction", which requires you to use  newinstance "key_generator_service", <handle/string variable>, "TRANSACTION=TRUE" otherwise the commit will not be 'separate' and will write all the data permanently to the database. 

No, the write trigger does a single insert to the database, which is then locked to your transaction.  Since your transaction then asks the question again the answer is returned, with the new uncommitted data in place and used by the max.  If anyone else asks the question before you run the commit, they will get a database lock until your data is commit. 

You can watch this happen if you step through a multi occurrence insert in the debugger and run the below scripts against your database simultaneously. (Note "with (nolock)" tells SQL server to return uncommitted data, your database may vary) select max(testid) from test with (nolock) -- Will show the new data increment before the commit.  select max(testid) from test -- will lock up with a record lock until the new data is commit. 

Hi Iain! Thanks for the good explanation. I somehow understood that it had to to with an "open" transaction but i just couldn't explain it as you. "Since your transaction then asks the question again the answer is returned, with the new uncommitted data in place and used by the max."  I now have to proceed testing integer-id:s, does the same work for child entities of the form. Unfortunately many tools do need these technical keys to work properly. Regards RogerW.

Hi roger, but you will encounter problems when 2 different users run the same job at the same time because they do not see what the other has created. Better to have some service providing the technical key with a separate transaction and an immediate commit. If you already know how many keys to be generated, you can create them in one go.