/*USING Identity property*/
If Exists(Select 1 from sys.tables where name='SIdent')
Begin
Drop Table SIdent
End
Create Table SIdent(sno int identity(1,10) Primary Key,Sname varchar(20))
GO
Begin Transaction
Insert into SIdent(sname) Values ('Wizards')
Rollback
GO
Begin Transaction
Insert into SIdent(sname) Values ('Wizards')
Commit
Go
-- first value is 11 and not 1 because rollback does not reset the Identity value.
Select min(sno) as [IdentityValue] from SIdent
Go
/*USING SEQUENCE – SEQUENCE is a new TSQL functionality available from SQL Server 2012*/
If Exists(Select 1 from sys.tables where name='STest')
Begin
Drop Table STest
End
If Exists(Select 1 from sys.sequences where name='SeqVal')
Begin
Drop Sequence SeqVal
End
Create table STest(sno int primary key,sname varchar(20))
Create Sequence SeqVal as int
Start with 1
Increment by 10
Minvalue 1
Maxvalue 100
No Cycle
Begin Transaction
Insert into STest(sno,sname)
Select next value for SeqVal,'Cowboys'
RollBack
Begin Transaction
Insert into STest(sno,sname)
Select next value for SeqVal,'Steelers'
Commit
-- first value is 11 and not 1 because rollback does not reset the Sequence value.
Select min(sno) as [SequenceValue] from Stest