删除所有数据后如何在SQL Server中重置自动增量 [英] how to Reset AutoIncrement in SQL Server after all data Deleted

问题描述

我在sql中获得了一个函数，该函数生成连续的字母数字序列 像(c000，c0001 .......)这样的编号，工作正常.但是当我删除表中的所有数据时，它从上次生成的编号开始.我希望它从"c0000"重置其值.

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" .

代码如下:-

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)

提前谢谢....

编辑1-

如何根据上一个值将其更新为增量.表示最后一个条目是否没有. c0053，并且我删除了该记录，因此在添加下一个条目时，其值应为"C0053"而不是"C0054".

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".

谢谢

推荐答案

截断表

从表或表的指定分区中删除所有行，而不记录单个行的删除. TRUNCATE TABLE与没有WHERE子句的DELETE语句相似；但是，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客户

TRUNCATE TABLE Customers

或删除您的内置功能.

or remove your in build function.

这篇关于删除所有数据后如何在SQL Server中重置自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！