posted 12/11/2011 by marcoadf - Views: [1730]
I've previously talked about the sequence object:
http://www.bidn.com/blogs/marcoadf/bidn-blog/2396/sql-server-2012-sequence-object
But I'ld like to call your attention to what happens when generating numbers inside a transaction.
As mencioned, in the blog, we can generate next number by just running statement select. And, what happens if I insert that select inside a transaction, as shown below:
BEGIN TRAN SELECT NEXT VALUE FOR dbo.Seq ROLLBACK TRAN
This means that inspite transaction being rolled back or not, once you call the object to generate a new value, this will be permanent. This is because sequence numbers are generated outside the scope of the transaction and they are generated wether the transaction is committed or rolled back.