Rollback disadvantage

drop table student

CREATE TABLE [student](

      [studentID] [int] identity(1,1),

      [studentname] [varchar](50) NULL

) ON [PRIMARY]

INSERT [student] ( [studentname]) VALUES ( N'Faizal')

INSERT [student] ( [studentname]) VALUES ( N'Maria')

INSERT [student] ( [studentname]) VALUES ( N'sophia')

select * from student

--Now start the transaction

Begin tran t1

INSERT [student] ( [studentname]) VALUES ( N'Faizal')

INSERT [student] ( [studentname]) VALUES ( N'Maria')

INSERT [student] ( [studentname]) VALUES ( N'sophia')

rollback tran t1

--Now select the data

select * from student

--Now insert, identity seed value should be 4  due to the rollback

--it will be 7

INSERT [student] ( [studentname]) VALUES ( N'sonia')

--select the data again, will be 7

select * from student

--Then delete

delete from student where [studentname]='sonia'

--Notice that Identity has jumped to some numbers

--To reset that use the command as follows

---If we want the Next ID to be 4 , type 3

DBCC CHECKIDENT (student, reseed, 3)

--Insert agian

INSERT [student] ( [studentname]) VALUES ( N'sonia')

--select the data

select * from student