Server可重用的DML查询

Question

大家早上好，

目前我有一个DDL：

    /*==============================================================*/
    /* Database name:  Zvcvolkel                                    */
    /* DBMS name:      Microsoft SQL Server 2000                    */                                                
    /*==============================================================*/

    use master
    go


    drop database Zvcvolkel
    go


    /*==============================================================*/
    /* Database: Zvcvolkel                                          */
    /*==============================================================*/
    create database Zvcvolkel
    go


    use Zvcvolkel
    go

    CREATE TABLE Rol(
        Rol_ID  int IDENTITY(1,1),
        R_Naam  varchar(max),

        CONSTRAINT PK_ROL PRIMARY KEY (Rol_ID)
    )
    go

    CREATE TABLE Persoon(
        Persoon_ID      int IDENTITY(1,1),
        Rol_ID          int,
        L_Voornaam      varchar(max),
        L_Achternaam    varchar(max),
        L_Geboortedat   date,
        L_Woonplaats    varchar(max),
        L_Geslacht      char(1),
        L_Rekeningnr    varchar(max),

        CONSTRAINT PK_Persoon PRIMARY KEY (Persoon_ID),
        CONSTRAINT FK_Persoon_Ref_Rol FOREIGN KEY (Rol_ID) REFERENCES Rol (Rol_ID) ON UPDATE CASCADE,
        CONSTRAINT CHK_L_geslacht CHECK (L_geslacht = 'M' OR L_geslacht = 'V')
    )
    go

    CREATE TABLE Medewerker(
        Medewerker_ID   int IDENTITY(1,1),
        Persoon_ID      int,
        M_Salaris       decimal,

        CONSTRAINT PK_Medewerker PRIMARY KEY (Medewerker_ID),
        CONSTRAINT FK_Medewerker_Ref_Persoon FOREIGN KEY (Persoon_ID) REFERENCES Persoon (Persoon_ID) ON UPDATE CASCADE,
        CONSTRAINT CHK_M_Salaris CHECK (M_Salaris >= 0)
    )
    go

    CREATE TABLE Lid(
        Lid_ID          int IDENTITY(1,1),
        Persoon_ID      int,
        L_GebNaam       varchar(max),
        L_Wachtwoord    varchar(max),

        CONSTRAINT PK_Lid PRIMARY KEY (Lid_ID),
        CONSTRAINT FK_Lid_Ref_Persoon FOREIGN KEY (Persoon_ID) REFERENCES Persoon (Persoon_ID) ON UPDATE CASCADE,
    )
    go

    CREATE TABLE Stat(
        Status_ID   int IDENTITY(1,1),
        S_Naam      varchar(max),

        CONSTRAINT PK_Stat PRIMARY KEY (Status_ID)
    )
    go

    CREATE TABLE Hangaar(
        Hangaar_ID  int IDENTITY(1,1),
        H_Naam      varchar(max),
        H_Locatie   varchar(max),

        CONSTRAINT PK_Hangaar PRIMARY KEY (Hangaar_ID)
    )
    go

    CREATE TABLE Vliegtuig(
        Vliegtuig_ID    int IDENTITY(1,1),
        Hangaar_ID      int,
        Status_ID       int,
        V_Naam          varchar(max),
        V_Type          varchar(max),

        CONSTRAINT PK_Vliegtuig PRIMARY KEY (Vliegtuig_ID),
        CONSTRAINT FK_Vliegtuig_Ref_Hangaar FOREIGN KEY (Hangaar_ID) REFERENCES Hangaar (Hangaar_ID) ON UPDATE CASCADE,
        CONSTRAINT FK_Vliegtuig_Ref_Status FOREIGN KEY (Status_ID) REFERENCES Stat (Status_ID) ON UPDATE CASCADE,
    )
    go

    CREATE TABLE Vlucht(
        Vlucht_ID       int IDENTITY(1,1),
        Lid_ID          int,
        Vliegtuig_ID    int,
        Vl_Vertrektijd  date,
        VL_Eindtijd     date,
        VL_Type         char(1),
        Vl_Notitie      varchar(max),

        CONSTRAINT PK_Vlucht PRIMARY KEY (Vlucht_ID),
        CONSTRAINT FK_Vlucht_Ref_Lid FOREIGN KEY (Lid_ID) REFERENCES Lid (Lid_ID) ON UPDATE CASCADE,
        CONSTRAINT FK_Vlucht_Ref_Vliegtuig FOREIGN KEY (Vliegtuig_ID) REFERENCES Vliegtuig (Vliegtuig_ID) ON UPDATE CASCADE,
        CONSTRAINT CHK_VL_type CHECK (VL_type = 'R' OR VL_type = 'L')

    )
    go

接下来是创建一个可重用的DML文件：

    use Zvcvolkel
    go

    DELETE FROM Vlucht
    DELETE FROM Medewerker
    DELETE FROM Lid
    DELETE FROM Persoon
    DELETE FROM Rol
    DELETE FROM Vliegtuig
    DELETE FROM Stat
    DELETE FROM Hangaar

    go

    INSERT INTO Rol 
    VALUES  ('Management'),
            ('Instructeur'),
            ('Technicus'),
            ('Lid'),
            ('Bartender')

    INSERT INTO Stat 
    VALUES  ('Beschikbaar'),
            ('Niet beschikbaar'),
            ('Onderhoud')

    INSERT INTO Hangaar 
    VALUES  ('Hangaar 1', 'Volkel'),
            ('Hangaar 2', 'Volkel')

    INSERT INTO Vliegtuig 
    VALUES  (1, 1, 'PH-143', 'ASK-21'),
            (1, 3, 'PH-1643', 'LS-4'),
            (2, 2, 'PH-831', 'Ka-8')

    INSERT INTO Persoon
    VALUES  (1, 'Chef', 'de Directeur', '01-01-1986', 'Eindhoven', 'M', 'IBAN7277'),
            (2, 'Piet', 'de Instructeur', '02-02-1965', 'Geldrop', 'M', 'IBAN5656'), 
            (3, 'Monica', 'de Technicus', '03-03-1958', 'Helmond', 'V', 'IBAN8579'),
            (4, 'Linda', 'het Lid', '04-04-1976', 'Deurne', 'V', 'IBAN4665'),
            (5, 'Jan', 'de Bartender', '05-05-1966', 'Venray', 'M', 'IBAN5765')

    INSERT INTO Medewerker
    VALUES  (5, 1250.00)

    INSERT INTO Lid
    VALUES  (1, 'Chefgeb', 'Chefww'),
            (2, 'Pietgeb', 'Pietww'),
            (3, 'Monicageb', 'Monicaww'),
            (4, 'Lindageb', 'Lindaww')

    INSERT INTO Vlucht
    VALUES  (2, 1, '10-04-2018 10:34:00', '10-04-2018 17:34:00', 'R', 'Geen notitie')

它在第一次运行时运行得很好，但是当我再次运行它时，它会再次添加行，并且由于自动增加，它会在ID上旋转。当我再次运行查询时，Rol_ID变成6-7-8-9-10，而不是1-2-3-4-5！每次我运行ID的增加，它应该再次从1开始，因为在第一行中有Delete语句。

有人知道我在这里错过了什么吗？

Answer 1

我想你可以用

DBCC CHECKIDENT ('TableName', RESEED, 0)

对于所有使用IDENTITY列的表。

Answer 2

你需要重置身份计数器

管理命令用于重置标识计数器。命令语法是：

DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}])

例如

DBCC CHECKIDENT ('Vliegtuig', RESEED, 0);

去

Answer 3

是的，您需要使用下面的语句将所有表的标识计数器重置为0。

DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}])