SET NOCOUNT ON
Drop Table dbo.Region
GO
CREATE TABLE dbo.Region(
    RegionId      int             IDENTITY(1,1),RegionName    varchar(100)    NOT NULL
)
GO

INSERT INTO dbo.Region (RegionName) 
VALUES  ('Region One'),('Region Two');
GO

SELECT * FROM dbo.Region

你可以从一个表现良好的身份字段中得到的结果.

RegionId    RegionName
----------- ------------------
1           Region One
2           Region Two

现在让我们在Identity列中强制使用几个值.

SET NOCOUNT ON
Drop Table dbo.Region
GO
CREATE TABLE dbo.Region(
    RegionId      int             IDENTITY(1,RegionName    varchar(100)    NOT NULL
)
GO

SET IDENTITY_INSERT dbo.Region ON;
INSERT INTO dbo.Region (RegionId,RegionName) 
VALUES (-9,'Unknown'),(-99,'N/A');
SET IDENTITY_INSERT dbo.Region OFF;

INSERT INTO dbo.Region (RegionName) 
VALUES  ('Region One'),('Region Two');
GO

SELECT * FROM dbo.Region

输出是

RegionId    RegionName
----------- ------------------
-9          Unknown
-99         N/A
2           Region One
3           Region Two

RegionId = 1去哪了？

编辑在进一步研究中,如果您尝试两次相同的特技,sql-Server不会跳过任何内容

SET NOCOUNT ON
Drop Table dbo.Region
GO
CREATE TABLE dbo.Region(
    RegionId      int             IDENTITY(1,('Region Two');
GO
SET IDENTITY_INSERT dbo.Region ON;
INSERT INTO dbo.Region (RegionId,RegionName) 
VALUES (-999,'Known-Unknown'),(-9999,'Really N/A');
SET IDENTITY_INSERT dbo.Region OFF;

INSERT INTO dbo.Region (RegionName) 
VALUES  ('Region Four'),('Region Five');
GO

SELECT * FROM dbo.Region

这里的输出是

RegionId    RegionName
----------- ------------------
-9          Unknown
-99         N/A
2           Region One
3           Region Two
-999        Known-Unknown
-9999       Really N/A
4           Region Four
5           Region Five

在之前的案例中,1失踪了.这里4没有丢失！

所以现在这是不可预测的,遗失的身份！

为什么RegionId = 1会丢失,但RegionId = 4不会丢失？！