http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660Laurion, I actually assumed that the reason for that was to keep things simple, but that makes sense as well. I don't _mind_, but I had some code that relied on being able to copy identity value to another table, without taking into account that it can be reused. Just something to be aware of http://ayende.com/3974/esent-identity-and-the-case-of-the-duplicate-key#comment6http://ayende.com/3974/esent-identity-and-the-case-of-the-duplicate-key#comment6Fri, 24 Apr 2009 07:08:43 GMTIt looks like your bookmark is the same and you will hit the first column each loop. Giving the same result? http://ayende.com/3974/esent-identity-and-the-case-of-the-duplicate-key#comment5http://ayende.com/3974/esent-identity-and-the-case-of-the-duplicate-key#comment5Fri, 24 Apr 2009 06:42:21 GMTNH has the same behavior for <increment strategy. >http://ayende.com/3974/esent-identity-and-the-case-of-the-duplicate-key#comment4http://ayende.com/3974/esent-identity-and-the-case-of-the-duplicate-key#comment4Fri, 24 Apr 2009 04:55:21 GMTIn the code above the table is being opened each time and Esent behaves just as described. This is done for performance reasons. Tracking the max autoinc value in the catalog would require updating a second database page on every update, generating more log and database I/O. If the table has a lot of inserts then the autoinc value would become 'hot', meaning that a lot of different threads would want to update this page. This makes checkpoint advancement harder because the page can only be written to disk when it isn't latched. That problem can be solved by our page versioning but creating versions uses up more buffer manager space. One note about this -- when Esent looks for max(id) from the table it will look for an index where the first index segment is on id. If no such index exists then a full table scan has to be performed. No-one has made that mistake yet, but it is something to consider. http://ayende.com/3974/esent-identity-and-the-case-of-the-duplicate-key#comment3http://ayende.com/3974/esent-identity-and-the-case-of-the-duplicate-key#comment3Fri, 24 Apr 2009 01:43:35 GMTThomas, Look again, the creation of the table is outside the loop http://ayende.com/3974/esent-identity-and-the-case-of-the-duplicate-key#comment2http://ayende.com/3974/esent-identity-and-the-case-of-the-duplicate-key#comment2Fri, 24 Apr 2009 01:40:31 GMTAren't you recreating the table in the loop? Then it's not so strange anymore. I would expect a new table to start counting from scratch. http://ayende.com/3974/esent-identity-and-the-case-of-the-duplicate-key#comment1http://ayende.com/3974/esent-identity-and-the-case-of-the-duplicate-key#comment1Fri, 24 Apr 2009 00:35:32 GMT