Sqlserver Field Identity jumps to 1001

Asked

Viewed 25 times

1

I noticed that one of the tables had a balance from 9 to 1001. At first I imagined that some colleague would have done a test in the bank and deleted the record in sequence causing the number to have this jump.

Now it has happened to another table without anyone having made an Index with a later deletion. Has anyone had this problem? This is the table:

CREATE TABLE [dbo].[PcpsRota](
    [RotaId] [int] IDENTITY(1,1) NOT NULL,
    [EmpId] [int] NOT NULL,
    [RotaDes] [varchar](80) NOT NULL,
    [UsuId] [int] NOT NULL,
    [UsuDta] [datetime] NOT NULL,
    [UltUsuId] [int] NOT NULL,
    [UltDta] [datetime] NOT NULL,
    [RotaSit] [smallint] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [RotaId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PcpsRota] ADD  DEFAULT (getdate()) FOR [UltDta]
GO
ALTER TABLE [dbo].[PcpsRota] ADD  CONSTRAINT [DF_RotaSit]  DEFAULT ((2)) FOR [RotaSit]
GO
ALTER TABLE [dbo].[PcpsRota]  WITH CHECK ADD  CONSTRAINT [CHK_PcpsRota_UltimoUsuario_DeveSerInformado] CHECK  (([UltUsuId]>(0)))
GO
ALTER TABLE [dbo].[PcpsRota] CHECK CONSTRAINT [CHK_PcpsRota_UltimoUsuario_DeveSerInformado]
GO
sql-server entity-framework

  • Dude, I read a case that looks a lot like yours. Except it’s related to postgres, but maybe reading about this case can explain why: article. In this case they resolved to stop using sequences generated by the database.

1 answer

0

From the SQL Server 2012 version, for performance reasons, the values IDENTITY are cached and some of these values may be lost during a database crash or when the server restarts, thus resulting in a gap in the sequence of values. This is the default behavior of SQL Server.

If you don’t want this behavior, you have two options:

  1. Use a SEQUENCE with the option NOCACHE.

     CREATE SEQUENCE CountBy1
     START WITH 1
     INCREMENT BY 1
     NOCACHE;
 GO

 CREATE TABLE t1 (
 Id INT PRIMARY KEY DEFAULT NEXT VALUE FOR CountBy1, 
 col INT NOT NULL);
 GO

  2. Or, use trace flag 272.

Microsoft Docs: https://docs.microsoft.com/pt-br/previous-versions/sql/sql-server-2012/ms186775(v=sql.110)? redirectedfrom=MSDN

Browser other questions tagged sql-server entity-framework

You are not signed in. Login or sign up in order to post.