如何查看自增长以及重置自增长

how to get the next autoincrement value in sql

回答1

To get the next auto-increment value from SQLServer :

This will fetch the present auto-increment value.

SELECT IDENT_CURRENT('table_name');

Next auto-increment value.

SELECT IDENT_CURRENT('table_name')+1;

------> This will work even if you add a row and then delete it because IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

反驳1

This is just plain not true - the IDENTITY could have an increment of something different than 1 ! Fact is: you CANNOT get the next value for an IDENTITY column in SQL Server - there is no way to do this. The values only get set when the INSERT happens - not before. – marc_s Jul 13 '12 at 11:12

反驳2

@patel.milanb: the increment vale of your IDENTITY could be five - so your next value would be 1515 (and NOT 1511!) - and also: if the next insert is rolled back, a value (like 1515) might be "lost" - and the real next value ends up being 1520. Just adding +1 to the IDENT_CURRENT is a GUESS at best..... – marc_s Jul 13 '12 at 11:19

回答2

SELECT IDENT_CURRENT('table_name')
SELECT IDENT_INCR('table_name')

这个可以看到当前的id，以及自增的

反驳

As marc_s points out, the next auto-incremented identity cannot be guarenteed.

So, if inserting into two tables, where one table has a foreign key (explicit or otherwise) to the other (codependency), then just include both operations in the same transaction (to prevent any race conditions)

有可能插入出错，被回滚了

扩展阅读

Why Rolling back inserts with Identity columns is a bad idea.

SQL SERVER – Reset the Identity SEED After ROLLBACK or ERROR

rollback之后，再重置identity的意义不大，需要锁表，会影响性能。另外自增id不适合用来做展示，比如invoice number会要求是连续的，而自增的id必然会存在gaps(因为rollback的问题)。

Comment1: First of all, above code may have issue with concurrency. That means, after you have retrieved identity value from the table, it is quite possible that in any other process with similar or different code the identity would have been updated and when you reset the identity at that time, you may reset it incorrect value and eventually forfeiting the original purpose of the identity. If you have Primary Key or any other kind of Unique Key, you may start facing error as well and your data integrity would have been compromised.

Here you may come up with the solution that when you enter this transaction you put the lock on the table, but that will additionally complicate the things and your performance will degrade big time. This code will work in the case, when you have single transaction at any point of time accessing the code and there is no issue with concurrency and performance.

Comment2: There should be never any dependance on the identity column of the table. If you need a serial number generated for display purpose, just create a column with int or bigint dataype and increment it at every insert. If you are using an identity column value for display, you should be very much aware of the situation that you will have gaps in that value and you should be able to write business logic around it. For example, you should not use identity columns as order number and if there is a gap in the order number, your business should just accept it. If you are using identity column value as invoice number, I think it is a bad idea as a general rule of accounting says that invoice number should be in sequence.

Well, I guess that is what I wanted to add the code which is displayed above. I would personally follow my both the comments above and will not use identity columns for any display purpose or will not depend on sequence of it. However, I truly appreciate the efforts of DBCore group.