Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to Reset AutoIncrement in SQL Server after all data Deleted

i got a function in sql that generate sequential series of alphanumeric no.like (c000,c0001 .......) , which is working good . but when i deleted all data in table , it starts from last generated no. i want it to reset its value from "c0000" .

code is as follows :-

create table Customers
(
dbID int identity not null primary key,
CustomerName varchar(100)
)

 create function CustomerNumber (@id int) 
  returns char(5) 
  as 
 begin 
 return 'C' + right('0000' + convert(varchar(10), @id), 4) 
  end

  alter table Customers add CustomerNumber as dbo.CustomerNumber(dbID)

thanks in advance....

EDIT 1 -

how to update it to increment based on last value . means if last entry having no. c0053 , and i deleted this record , so when next entry added it should have value "C0053" not "C0054".

thanks

like image 505
speedyraz Avatar asked Oct 16 '25 17:10

speedyraz


2 Answers

Truncate Table Command is good way to reset Identity, but there is other command also to reset Identity after deletion of records.

DBCC CHECKIDENT (TableName, RESEED, 0)

After Deleting you can use this command to reset Identity to 0.

like image 199
Manish Kumar Avatar answered Oct 19 '25 11:10

Manish Kumar


TRUNCATE TABLE

Removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

TRUNCATE TABLE Customers

or remove your in build function.

like image 21
Neeraj Singh Avatar answered Oct 19 '25 09:10

Neeraj Singh