如何在SQL Server中重置标识列？

Question

我在SQL Server中有一个名为personal_info的表。t此表有一个名为vol_id的列，(Is Identity)属性为yes。

现在我插入了大约175568行作为测试。然后我删除了大约568行，但是当我再次插入数据时，列vol_id从175569开始，而不是从175001开始。

      USE [VolunteersAffairs] 

    go 

    /****** Object:  StoredProcedure [dbo].[AddVolunteer]    Script Date: 05/02/2014      21:12:36 ******/ 
    SET ansi_nulls ON 

    go 

    SET quoted_identifier ON 

    go 

    ALTER PROCEDURE [dbo].[Addvolunteer] @Vol_Name                 NVARCHAR(255), 
                                         @Vol_Zone                 NVARCHAR(255), 
                                         @vol_street               NVARCHAR(255), 
                                         @Vol_Sex                  INT, 
                                         @Vol_Date_of_Birth        DATE, 
                                         @Vol_Home_Phone           INT, 
                                         @Vol_Work_Phone           INT, 
                                         @Vol_Mobile1              INT, 
                                         @Vol_Mobile2              INT, 
                                         @Vol_Email                NVARCHAR(255), 
                                         @Vol_Job                  NVARCHAR(255), 
                                         @Vol_Affiliation          NVARCHAR(255), 
                                         @vol_Education            INT, 
                                         @vol_Education_Place      NVARCHAR(255), 
                                         @vol_Education_Department NVARCHAR(255), 
                                         @vol_Interesting          INT, 
                                         @vol_Notes                NVARCHAR(255), 
                                         @Team_ID                  INT 
    AS 
        INSERT INTO personal_info 
                    (vol_name, 
                     vol_zone, 
                     vol_street, 
                     vol_sex, 
                     vol_date_of_birth, 
                     vol_home_phone, 
                     vol_work_phone, 
                     vol_mobile1, 
                     vol_mobile2, 
                     vol_email, 
                     vol_job, 
                     vol_affiliation, 
                     vol_education, 
                     vol_education_place, 
                     vol_education_department, 
                     vol_interesting, 
                     team_id, 
                     vol_notes) 
        VALUES      (@Vol_Name, 
                     @Vol_Zone, 
                     @vol_street, 
                     @Vol_Sex, 
                     @Vol_Date_of_Birth, 
                     @Vol_Home_Phone, 
                     @Vol_Work_Phone, 
                     @Vol_Mobile1, 
                     @Vol_Mobile2, 
                     @Vol_Email, 
                     @Vol_Job, 
                     @Vol_Affiliation, 
                     @vol_Education, 
                     @vol_Education_Place, 
                     @vol_Education_Department, 
                     @vol_Interesting, 
                     @Team_ID, 
                     @vol_Notes) 

请帮我写一下丢失的身份证

Answer 1

您需要重新插入标识列，如下所示

DBCC CHECKIDENT(Personal_Info, RESEED, 175001)

从MSDN引用

权限：

create procedure sp_insert-person
@name varchar(10)
as 
begin
declare @last_id int;
select @last_id = top 1 Vol_ID from Personal_Info order by Vol_ID desc;
insert into Personal_Info(Vol_ID,name) 
values(@last_id+1,@name);
end

exec sp_insert-person @name = 'user2251369'

您的最终过程应该如下所示

USE [VolunteersAffairs]
GO
/****** Object:  StoredProcedure [dbo].[AddVolunteer]    
 Script Date: 05/02/2014      21:12:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[AddVolunteer]
@Vol_Name nvarchar(255), 
@Vol_Zone nvarchar(255), 
@vol_street nvarchar(255), 
@Vol_Sex int, 
@Vol_Date_of_Birth date, 
@Vol_Home_Phone int, 
@Vol_Work_Phone int, 
@Vol_Mobile1 int,    
@Vol_Mobile2 int, 
@Vol_Email nvarchar(255), 
@Vol_Job nvarchar(255), 
@Vol_Affiliation nvarchar(255), 
@vol_Education int, 
@vol_Education_Place nvarchar(255), 
@vol_Education_Department nvarchar(255), 
@vol_Interesting int, 
@vol_Notes nvarchar(255), 
@Team_ID int
As
BEGIN
DECLARE @last_vol_id int;
select top 1 @last_vol_id = Vol_ID from Personal_Info order by Vol_ID desc;

insert into Personal_Info
(Vol_Id,
Vol_Name, 
Vol_Zone, 
vol_street, 
Vol_Sex, 
Vol_Date_of_Birth, 
Vol_Home_Phone, 
Vol_Work_Phone, 
Vol_Mobile1, 
Vol_Mobile2, 
Vol_Email, 
Vol_Job, 
Vol_Affiliation, 
vol_Education, 
vol_Education_Place, 
vol_Education_Department, 
vol_Interesting, 
Team_ID, 
vol_Notes) 
values 
(@last_vol_id+1,
@Vol_Name, 
@Vol_Zone, 
@vol_street, 
@Vol_Sex, 
@Vol_Date_of_Birth, 
@Vol_Home_Phone, 
@Vol_Work_Phone, 
@Vol_Mobile1, 
@Vol_Mobile2, 
@Vol_Email, 
@Vol_Job, 
@Vol_Affiliation, 
@vol_Education, 
@vol_Education_Place, 
@vol_Education_Department, 
@vol_Interesting, 
@Team_ID, 
@vol_Notes);
END