1

Is there any alternative (e.g. Alter Column Query) of DBCC CHECKIDENT in SQL Server?

John
  • 704
  • 3
  • 12
  • 29
  • 2
    An alternative to achieve what exactly? What are you trying to achieve and why is `DBCC CHECKIDENT` not suitable? – Oded Apr 05 '13 at 10:39
  • Actually I just wanted to set auto_increment number like this: ALTER TABLE Emp AUTO_INCREMENT=100. But no luck – John Apr 05 '13 at 10:41
  • My TL dont want to use DBCC commands. Dont know why. Please suggest – John Apr 05 '13 at 10:43
  • 1
    Create a new table, copy over and drop the original table? Killing all the statistics in the process. – Oded Apr 05 '13 at 10:51
  • 4
    You wouldn't actually have to copy the data over. You could use [`ALTER TABLE ... SWITCH`](http://stackoverflow.com/a/6086661/73226). This would be utterly pointless though. Just use `DBCC CHECKIDENT` as that is what it is there for. – Martin Smith Apr 05 '13 at 10:53

1 Answers1

2

You may try like this.

 SET IDENTITY_INSERT [MyTable] ON
INSERT INTO Mytable(ID,name)values (@AutoIncrementvalue-1,'siva')
SET IDENTITY_INSERT [MyTable] OFF

--Insert into table or whatever here
-- finally delete the value which we inserted initially
DELETE FROm MyTable WHERE ID=@AutoIncrementvalue-1

But always I prefer DBCC CHECKIDENT. You may use this to satisfy your TL.

Maximus
  • 792
  • 9
  • 19
  • Had an issue where DBCC CHECKIDENT couldn't execute on a live environment as it couldn't get a table lock I believe so had to use the manual method above. – Deano Oct 02 '18 at 03:36