nube - sql database download

Restablecer la semilla de identidad después de eliminar registros en SQL Server

Question

He insertado registros en una tabla de base de datos de SQL Server. La tabla tenía una clave principal definida y la semilla de identidad de incremento automático se establece en "Sí". Esto se hace principalmente porque en SQL Azure, cada tabla debe tener una clave primaria e identidad definida.

Pero como tengo que eliminar algunos registros de la tabla, la semilla de identidad de esas tablas se verá afectada y la columna de índice (que se genera automáticamente con un incremento de 1) se verá afectada.

¿Cómo puedo restablecer la columna de identidad después de eliminar los registros para que la columna tenga una secuencia en orden numérico ascendente?

La columna de identidad no se utiliza como clave externa en ninguna parte de la base de datos.

Answer 1

@jacob

DBCC CHECKIDENT (''[TestTable]'', RESEED,0)
DBCC CHECKIDENT (''[TestTable]'', RESEED)

Trabajé para mí, solo tenía que borrar todas las entradas de la tabla y luego agregar lo anterior en un punto de activación después de eliminar. Ahora cada vez que borre una entrada se toma de allí.

Answer 2

Aunque la mayoría de las respuestas sugieren RESEED a 0 , y si bien algunas ven esto como un defecto para las tablas TRUNCATED , Microsoft tiene una solución que excluye la ID

DBCC CHECKIDENT (''[TestTable]'', RESEED)

Esto verificará la tabla y se restablecerá a la siguiente ID . Esto ha estado disponible desde MS SQL 2005 hasta la fecha.

MSDN

Answer 3

Aunque la mayoría de las respuestas sugieren RESEED a 0, pero muchas veces necesitamos simplemente volver a ver la próxima Id disponible

declare @max int
select @max=max([Id])from [TestTable]
if @max IS NULL   //check when max is returned as null
  SET @max = 0
DBCC CHECKIDENT (''[TestTable]'', RESEED,@max)

Esto verificará la tabla y se restablecerá a la siguiente ID.

Answer 4

Ejecute este script para restablecer la columna de identidad. Tendrá que hacer dos cambios. Reemplace tableXYZ con cualquier tabla que necesite actualizar. Además, el nombre de la columna de identidad debe eliminarse de la tabla temporal. Esto fue instantáneo en una tabla con 35,000 filas y 3 columnas. Obviamente, haga una copia de seguridad de la tabla y primero intente esto en un entorno de prueba.

select * 
into #temp
From tableXYZ

set identity_insert tableXYZ ON

truncate table tableXYZ

alter table #temp drop column (nameOfIdentityColumn)

set identity_insert tableXYZ OFF

insert into tableXYZ
select * from #temp

Answer 5

Esta es una pregunta común y la respuesta es siempre la misma: no lo hagas. Los valores de identidad deben tratarse como arbitrarios y, como tales, no hay un orden "correcto".

Answer 6

Intenté @anil shahs responder y restablecer la identidad. Pero cuando se insertó una nueva fila, obtuvo la identity = 2 . Así que en lugar de eso cambié la sintaxis a:

DELETE FROM [TestTable]

DBCC CHECKIDENT (''[TestTable]'', RESEED, 0)
GO

Entonces la primera fila obtendrá la identidad = 1.

Answer 7

Para completar las filas DELETE y restablecer el recuento de IDENTIDAD, uso esto (SQL Server 2008 R2)

USE mydb

-- ##################################################################################################################
-- DANGEROUS!!!! USE WITH CARE
-- ##################################################################################################################

DECLARE
  db_cursor CURSOR FOR
    SELECT TABLE_NAME
      FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_TYPE = ''BASE TABLE''
       AND TABLE_CATALOG = ''mydb''

DECLARE @tblname VARCHAR(50)
SET @tblname = ''''

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tblname

WHILE @@FETCH_STATUS = 0
BEGIN
  IF CHARINDEX(''mycommonwordforalltablesIwanttodothisto'', @tblname) > 0
    BEGIN
      EXEC(''DELETE FROM '' + @tblname)
      DBCC CHECKIDENT (@tblname, RESEED, 0)
    END

  FETCH NEXT FROM db_cursor INTO @tblname
END

CLOSE db_cursor
DEALLOCATE db_cursor
GO

Answer 8

Primero: Especificación de identidad Solo: "No" >> Guardar proyecto de ejecución de base de datos

Después de eso: Especificación de identidad Just: "YES" >> Guardar proyecto de ejecución de base de datos

Su ID de base de datos, PK Inicio desde 1 >>

Answer 9

Restablecer la columna de identidad con nueva identificación ...

DECLARE @MAX INT
SELECT @MAX=ISNULL(MAX(Id),0) FROM [TestTable]

DBCC CHECKIDENT (''[TestTable]'', RESEED,@MAX)

Answer 10

Se debe tener en cuenta que SI todos los datos se eliminan de la tabla a través de DELETE (es decir, no hay una cláusula WHERE ), entonces siempre que a) los permisos lo permitan, yb) no haya FK que hagan referencia a la tabla (que aparece para ser el caso aquí, se preferiría usar TRUNCATE TABLE , ya que hace un DELETE más eficiente y restablece la semilla de IDENTITY al mismo tiempo. Los siguientes detalles se tomaron de la página de MSDN para TRUNCATE TABLE :

En comparación con la instrucción DELETE, TRUNCATE TABLE tiene las siguientes ventajas:

• Se utiliza menos espacio de registro de transacciones.

  La instrucción DELETE elimina las filas una a la vez y registra una entrada en el registro de transacciones para cada fila eliminada. TRUNCATE TABLE elimina los datos al desasignar las páginas de datos utilizadas para almacenar los datos de la tabla y registra solo las desasignaciones de páginas en el registro de transacciones.

• Normalmente se utilizan menos bloqueos.

  Cuando la instrucción DELETE se ejecuta utilizando un bloqueo de fila, cada fila de la tabla se bloquea para su eliminación. TRUNCATE TABLE siempre bloquea la tabla (incluido un bloqueo de esquema (SCH-M)) y la página, pero no cada fila.

• Sin excepción, cero páginas quedan en la tabla.

  Después de ejecutar una instrucción DELETE, la tabla aún puede contener páginas vacías. Por ejemplo, las páginas vacías en un montón no pueden desasignarse sin al menos un bloqueo de tabla exclusivo (LCK_M_X). Si la operación de eliminación no utiliza un bloqueo de tabla, la tabla (montón) contendrá muchas páginas vacías. Para los índices, la operación de eliminación puede dejar páginas vacías atrás, aunque estas páginas se desasignarán rápidamente mediante un proceso de limpieza en segundo plano.

Si la tabla contiene una columna de identidad, el contador de esa columna se restablece al valor semilla definido para la columna. Si no se definió una semilla, se usa el valor predeterminado 1. Para retener el contador de identidad, use DELETE en su lugar.

Así que lo siguiente:

DELETE FROM [MyTable];
DBCC CHECKIDENT (''[MyTable]'', RESEED, 0);

Se convierte en justo:

TRUNCATE TABLE [MyTable];

Consulte la documentación de TRUNCATE TABLE (enlace arriba) para obtener información adicional sobre restricciones, etc.

Answer 11

Se prefiere Truncate tabla porque borra los registros, restablece el contador y recupera el espacio dis.

Delete and CheckIdent debe usar solo cuando las claves foráneas impidan que trunque

Answer 12

Siempre es mejor usar TRUNCATE cuando sea posible en lugar de eliminar todos los registros, ya que tampoco usa el espacio de registro.

En caso de que necesitemos eliminar y restablecer la semilla, siempre recuerde que si la tabla nunca se DBCC CHECKIDENT(''tablenem'',RESEED,0) y utilizó DBCC CHECKIDENT(''tablenem'',RESEED,0) , el primer registro obtendrá la identidad = 0 como se indica en la MSDN

En su caso, solo reconstruya el índice y no se preocupe por perder la serie de identidad, ya que este es un escenario común.

Answer 13

Utilice este procedimiento almacenado:

IF (object_id(''[dbo].[pResetIdentityField]'') IS NULL)
  BEGIN
    EXEC(''CREATE PROCEDURE [dbo].[pResetIdentityField] AS SELECT 1 FROM DUMMY'');
  END
GO

SET  ANSI_NULLS ON
GO
SET  QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[pResetIdentityField]
  @pSchemaName NVARCHAR(1000)
, @pTableName NVARCHAR(1000) AS
DECLARE @max   INT;
DECLARE @fullTableName   NVARCHAR(2000) = @pSchemaName + ''.'' + @pTableName;

DECLARE @identityColumn   NVARCHAR(1000);

SELECT @identityColumn = c.[name]
FROM sys.tables t
     INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
     INNER JOIN sys.columns c ON c.[object_id] = t.[object_id]
WHERE     c.is_identity = 1
      AND t.name = @pTableName
      AND s.[name] = @pSchemaName

IF @identityColumn IS NULL
  BEGIN
    RAISERROR(
      ''One of the following is true: 1. the table you specified doesn''''t have an identity field, 2. you specified an invalid schema, 3. you specified an invalid table''
    , 16
    , 1);
    RETURN;
  END;

DECLARE @sqlString   NVARCHAR(MAX) = N''SELECT @maxOut = max('' + @identityColumn + '') FROM '' + @fullTableName;

EXECUTE sp_executesql @stmt = @sqlString, @params = N''@maxOut int OUTPUT'', @maxOut = @max OUTPUT

IF @max IS NULL
  SET @max = 0

print(@max)

DBCC CHECKIDENT (@fullTableName, RESEED, @max)
go

--exec pResetIdentityField ''dbo'', ''Table''

Solo revisando mi respuesta. Me encontré con un comportamiento extraño en el servidor sql 2008 r2 que debería tener en cuenta.

drop table test01

create table test01 (Id int identity(1,1), descr nvarchar(10))

execute pResetIdentityField ''dbo'', ''test01''

insert into test01 (descr) values(''Item 1'')

select * from test01

delete from test01

execute pResetIdentityField ''dbo'', ''test01''

insert into test01 (descr) values(''Item 1'')

select * from test01

La primera selección produce 0, Item 1 .

El segundo produce 1, Item 1 . Si ejecuta el reinicio justo después de crear la tabla, el siguiente valor es 0. Honestamente, no me sorprende que Microsoft no pueda hacer esto correctamente. Lo descubrí porque tengo un archivo de secuencia de comandos que llena las tablas de referencia que a veces ejecuto después de volver a crear las tablas y otras veces cuando las tablas ya están creadas.

Answer 14

emitir 2 comandos puede hacer el truco

DBCC CHECKIDENT (''[TestTable]'', RESEED,0)
DBCC CHECKIDENT (''[TestTable]'', RESEED)

el primero restablece la identidad a cero, y el siguiente lo establecerá en el siguiente valor disponible - jacob

Answer 15

El comando de administración DBCC CHECKIDENT se usa para restablecer el contador de identidad. La sintaxis del comando es:

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

Ejemplo:

DBCC CHECKIDENT (''[TestTable]'', RESEED, 0);
GO

No se admitía en versiones anteriores de la Base de datos SQL de Azure, pero ahora se admite.

Tenga en cuenta que el argumento new_reseed_value varía según las versiones de SQL Server de acuerdo con la documentación :

Si las filas están presentes en la tabla, la siguiente fila se inserta con el valor new_reseed_value . En la versión SQL Server 2008 R2 y anteriores, la siguiente fila insertada usa new_reseed_value + el valor de incremento actual.

Sin embargo, encuentro que esta información es engañosa (en realidad, simplemente errónea) porque el comportamiento observado indica que al menos SQL Server 2012 todavía usa new_reseed_value + la lógica del valor de incremento actual. Microsoft incluso contradice con su propio Example C encuentra en la misma página:

C. Forzar el valor de identidad actual a un nuevo valor

El siguiente ejemplo fuerza el valor de identidad actual en la columna AddressTypeID en la tabla AddressType a un valor de 10. Como la tabla tiene filas existentes, la siguiente fila insertada usará 11 como valor, es decir, el nuevo valor de incremento actual definido para el valor de la columna más 1.

USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT (''Person.AddressType'', RESEED, 10);  
GO

Aún así, todo esto deja una opción para un comportamiento diferente en las nuevas versiones de SQL Server. Creo que la única manera de estar seguro, hasta que Microsoft aclare las cosas en su propia documentación, es hacer pruebas reales antes de usarlas.

Answer 16

DBCC CHECKIDENT (<TableName>, reseed, 0)

Esto establecerá el valor de identidad actual en 0.

Al insertar el siguiente valor, el valor de identidad se incrementa a 1.

Answer 17

DBCC CHECKIDENT (''TestTable'', RESEED, 0)
GO

Donde 0 es valor de inicio de identity