sql-server - para - modificar campo identity sql server

El incremento de identidad está saltando en la base de datos de SQL Server

Question

En una de mis tablas, la Fee en la columna "ReceiptNo" en el incremento de identidad de la base de datos de SQL Server 2012 de repente comenzó a saltar a 100 en lugar de a 1, dependiendo de las dos cosas siguientes.

1. si es 1205446, salta a 1206306, si es 1206321, salta a 1207306 y si es 1207314, salta a 1208306. Lo que quiero hacer notar es que los últimos tres dígitos permanecen constantes, es decir, 306 cada vez que se salta ocurre como se muestra en la siguiente imagen.

2. este problema ocurre cuando reinicio mi computadora

Answer 1

Desde SQL Server 2017+ puede usar ALTER DATABASE SCOPED CONFIGURATION :

IDENTITY_CACHE = {ON | APAGADO }

Activa o desactiva el caché de identidad en el nivel de la base de datos. El valor predeterminado es ON. El almacenamiento en caché de identidad se utiliza para mejorar el rendimiento INSERT en tablas con columnas Identity. Para evitar lagunas en los valores de la columna Identity en los casos en que el servidor se reinicia inesperadamente o se transfiere a un servidor secundario, desactive la opción IDENTITY_CACHE. Esta opción es similar a la marca de rastreo del servidor SQL existente 272, excepto que se puede establecer en el nivel de la base de datos y no solo en el nivel del servidor.

(...)

G. Establecer IDENTITY_CACHE

Este ejemplo desactiva la memoria caché de identidad.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

Answer 2

Este problema ocurre después de reiniciar el Servidor SQL.

La solucion es:

• Ejecute el Administrador de configuración de SQL Server .

• Seleccione los servicios del servidor SQL .

• Haga clic derecho en SQL Server y seleccione Propiedades .

• En la ventana de apertura en Parámetros de inicio , escriba -T272 y haga clic en Agregar , luego presione el botón Aplicar y reinicie.

Answer 3

Este sigue siendo un problema muy común entre muchos desarrolladores y aplicaciones, independientemente del tamaño.

Lamentablemente, las sugerencias anteriores no reparan todos los escenarios, es decir, el alojamiento compartido, no puede confiar en que su host establezca el parámetro de inicio -t272.

Además, si tiene tablas existentes que usan estas columnas de identidad para claves primarias, es un gran esfuerzo dejar caer esas columnas y recrear nuevas para usar la solución de secuencia de BS. La solución alternativa de Sequence solo es buena si está diseñando las tablas nuevas desde cero en SQL 2012+

En resumen, si está en Sql Server 2008R2, entonces PERMANEZCA EN ELLO. En serio, quédate ahí. Hasta que Microsoft admita que presentaron un error ENORME, que todavía está allí, incluso en Sql Server 2016, no deberíamos actualizar hasta que lo tengan y CORREGALO.

Microsoft directamente presentó un cambio radical, es decir, rompieron una API en funcionamiento que ya no funciona según lo diseñado, debido a que su sistema olvida su identidad actual en un reinicio. Caché o sin caché, esto es inaceptable, y el desarrollador de Microsoft con el nombre de Bryan debe ser el propietario, en lugar de decirle al mundo que es "por diseño" y una "característica". Claro, el almacenamiento en caché es una característica, pero perder la pista de lo que debería ser la próxima identidad, NO ES UNA FUNCIÓN. Es un ERROR frustrado!

Compartiré la solución alternativa que utilicé, porque mis bases de datos están en servidores de alojamiento compartido, además, no estoy abandonando y recreando mis columnas de clave principal, eso sería un enorme PITA.

En cambio, este es mi truco vergonzoso (pero no tan vergonzoso como este bug POS que Microsoft ha introducido).

Hack / Fix:

Antes de los comandos de inserción, simplemente reseve su identidad antes de cada inserción. Esta solución solo se recomienda si no tiene control de administrador sobre su instancia de Sql Server; de lo contrario, le sugiero resembrar al reiniciar el servidor.

declare @newId int -- where int is the datatype of your PKey or Id column
select @newId = max(YourBuggedIdColumn) from YOUR_TABLE_NAME
DBCC CheckIdent(''YOUR_TABLE_NAME'', RESEED, @newId)

Solo esas 3 líneas inmediatamente antes de su inserción, y debería estar listo para comenzar. Realmente no afectará tanto el rendimiento, es decir, será imperceptible.

Buena suerte.

Answer 4

Hay muchas razones posibles para saltar valores de identidad. Van desde inserciones retrotraídas hasta administración de identidades para la replicación. Lo que está causando esto en su caso no lo sé sin pasar un tiempo en su sistema.

Sin embargo, debes saber que en ningún caso puedes asumir una columna de identidad para ser contiguos. Hay demasiadas cosas que pueden causar lagunas.

Puede encontrar un poco más de información sobre esto aquí: http://sqlity.net/en/792/the-gap-in-the-identity-value-sequence/

Answer 5

Sé que mi respuesta podría llegar tarde a la fiesta. Pero lo he resuelto de otra manera al agregar un procedimiento almacenado de inicio en SQL Server 2012.

Cree un siguiente procedimiento almacenado en el maestro DB.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ResetTableNameIdentityAfterRestart]
AS
BEGIN

begin TRAN
    declare @id int = 0
    SELECT @id =  MAX(id) FROM [DatabaseName].dbo.[TableName]
    --print @id
    DBCC CHECKIDENT (''[DatabaseName].dbo.[TableName]'', reseed, @id)
Commit

END

A continuación, agréguelo a Start up utilizando la siguiente sintaxis.

EXEC sp_procoption ''ResetOrderIdentityAfterRestart'', ''startup'', ''on'';

Esta es una buena idea si tiene pocas tablas. pero si tiene que hacer muchas tablas, este método aún funciona, pero no es una buena idea.

Answer 6

Probablemente estés encontrando el problema aquí (máquina de retorno).

SQL Server 2012 ahora usa un tamaño de caché de 1,000 cuando IDENTITY valores de IDENTITY en una columna int y el reinicio del servicio puede "perder" valores no utilizados (el tamaño de caché es 10,000 para bigint / numeric ).

A partir de los datos que ha mostrado, parece que esto sucedió después de la entrada de datos del 22 de diciembre y luego, cuando se reinició, SQL Server reservó los valores 1206306 - 1207305 . Después de la entrada de datos del 24 al 25 de diciembre se realizó otro reinicio y SQL Server reservó el siguiente rango 1207306 - 1208305 visible en las entradas para el día 28.

A menos que esté reiniciando el servicio con una frecuencia inusual, es poco probable que los valores "perdidos" afecten significativamente el rango de valores permitido por el tipo de datos, por lo que la mejor política es no preocuparse por ello.

Si esto es por alguna razón un problema real para usted, vea las soluciones temporales en el tema Vinculado de Elemento de conexión.

1. Puede usar una SEQUENCE lugar de una columna de identidad y definir un tamaño de caché más pequeño, por ejemplo, y usar NEXT VALUE FOR en una columna predeterminada.
2. O aplique trace flag 272 que hace que la asignación de IDENTITY se registre como en versiones anteriores.

Debe tener en cuenta que ninguna de estas soluciones alternativas no garantiza lagunas. Esto nunca ha sido garantizado por IDENTITY ya que solo sería posible serializando las inserciones en la tabla. Si necesita una columna sin intervalo, deberá usar una solución diferente a la IDENTITY o SEQUENCE