SQL Server, ¿Cómo configurar el incremento automático después de crear una tabla sin pérdida de datos?

Tengo una tabla table1 en SQL server 2008 y tiene registros en ella.

Quiero que la columna de clave primaria table1_Sno sea una columna de incremento automático. ¿Se puede hacer esto sin ninguna transferencia de datos o clonación de tabla?

Sé que puedo usar ALTER TABLE para agregar una columna de incremento automático, pero ¿puedo simplemente agregar la opción AUTO_INCREMENT a una columna existente que es la clave principal?

Author: Eric Leschinski, 2011-05-22
Source

7 answers

Cambiar la propiedad IDENTITY es realmente un cambio solo de metadatos. Pero para actualizar los metadatos directamente requiere iniciar la instancia en modo de usuario único y jugar con algunas columnas en sys.syscolpars y no está documentada/no es compatible y no es algo que recomendaría o daría detalles adicionales sobre.

Para las personas que se encuentran con esta respuesta en SQL Server 2012+, la forma más fácil de lograr este resultado de una columna de incremento automático sería crear un objeto SEQUENCE y establezca el next value for seq como la columna predeterminada.

Alternativamente, o para versiones anteriores (a partir de 2005), la solución publicada en este elemento de conexión muestra una forma completamente compatible de hacer esto sin necesidad de operaciones de tamaño de datos utilizando ALTER TABLE...SWITCH. También blogueé sobre MSDN aquí. Aunque el código para lograr esto no es muy simple y hay restricciones , como que la tabla que se cambia no puede ser el objetivo de una clave externa limitación.

Código de ejemplo.

Establezca la tabla de prueba sin la columna identity.

CREATE TABLE dbo.tblFoo 
(
bar INT PRIMARY KEY,
filler CHAR(8000),
filler2 CHAR(49)
)


INSERT INTO dbo.tblFoo (bar)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM master..spt_values v1, master..spt_values v2

Alter it to have an identity column (more or less instant).

BEGIN TRY;
    BEGIN TRANSACTION;

    /*Using DBCC CHECKIDENT('dbo.tblFoo') is slow so use dynamic SQL to
      set the correct seed in the table definition instead*/
    DECLARE @TableScript nvarchar(max)
    SELECT @TableScript = 
    '
    CREATE TABLE dbo.Destination(
        bar INT IDENTITY(' + 
                     CAST(ISNULL(MAX(bar),0)+1 AS VARCHAR) + ',1)  PRIMARY KEY,
        filler CHAR(8000),
        filler2 CHAR(49)
        )

        ALTER TABLE dbo.tblFoo SWITCH TO dbo.Destination;
    '       
    FROM dbo.tblFoo
    WITH (TABLOCKX,HOLDLOCK)

    EXEC(@TableScript)


    DROP TABLE dbo.tblFoo;

    EXECUTE sp_rename N'dbo.Destination', N'tblFoo', 'OBJECT';


    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    PRINT ERROR_MESSAGE();
END CATCH;

Prueba el resultado.

INSERT INTO dbo.tblFoo (filler,filler2) 
OUTPUT inserted.*
VALUES ('foo','bar')

Da 

bar         filler    filler2
----------- --------- ---------
10001       foo       bar

Limpiar

DROP TABLE dbo.tblFoo
 63
Author: Martin Smith,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-01-18 21:30:08

SQL Server: Cómo establecer el incremento automático en una tabla con filas en ella:

Esta estrategia copia físicamente las filas alrededor de dos veces, lo que puede tomar mucho más tiempo si la tabla que está copiando es muy grande.

Puede guardar sus datos, soltar y reconstruir la tabla con el incremento automático y la clave primaria, y luego volver a cargar los datos.

Te guiaré con un ejemplo:

Paso 1, crear tabla foobar (sin clave primaria o auto-incremento):

CREATE TABLE foobar(
    id int NOT NULL,
    name nchar(100) NOT NULL,
)

Paso 2, insertar algunas filas 

insert into foobar values(1, 'one');
insert into foobar values(2, 'two');
insert into foobar values(3, 'three');

Paso 3, copie los datos de foobar en una tabla temporal: 

select * into temp_foobar from foobar

Paso 4, drop table foobar: 

drop table foobar;

Paso 5, vuelva a crear su tabla con la clave primaria y las propiedades de incremento automático: 

CREATE TABLE foobar(
    id int primary key IDENTITY(1, 1) NOT NULL,
    name nchar(100) NOT NULL,
)

Paso 6, inserte sus datos de la tabla temporal de nuevo en foobar 

SET IDENTITY_INSERT temp_foobar ON
INSERT into foobar (id, name) select id, name from temp_foobar;

Paso 7, deje caer su tabla de temporeros y verifique si funcionó: 

drop table temp_foobar;
select * from foobar;

Debe obtener esto, y cuando inspeccione la tabla foobar, la columna id es un incremento automático de 1 y id es una clave primaria: 

1    one
2    two
3    three
 4
Author: Eric Leschinski,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-06-11 18:28:05

Si desea hacer esto a través del diseñador, puede hacerlo siguiendo las instrucciones aquí "Guardar cambios no está permitido" al cambiar una columna existente para que sea nullable

 3
Author: Daveo,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:09:20

Si no desea agregar una nueva columna y puede garantizar que su columna int actual sea única, puede seleccionar todos los datos en una tabla temporal, eliminar la tabla y volver a crear con la columna de IDENTIDAD especificada. Luego, usando SET IDENTITY INSERT ON puede insertar todos sus datos en la tabla temporal en la nueva tabla.

 2
Author: Duncan Howe,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-05-21 21:46:36

Sí, puedes. Vaya a Herramientas > Diseñadores > Tabla y Diseñadores y desmarque "Evitar guardar los cambios Que Impiden la Recreación de la Tabla".

 2
Author: Peter Pauletto,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-08-15 23:14:49

No, no puede agregar una opción de incremento automático a una columna existente con datos, creo que la opción que mencionó es la mejor.

Echa un vistazo aquí.

 1
Author: Homam,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-05-21 21:44:56

El siguiente script puede ser una buena solución.Trabajó en grandes datos.

ALTER DATABASE WMlive SET RECOVERY SIMPLE WITH NO_WAIT

ALTER TABLE WMBOMTABLE DROP CONSTRAINT PK_WMBomTable

ALTER TABLE WMBOMTABLE drop column BOMID

ALTER TABLE WMBOMTABLE ADD bomid int IDENTITY(1, 1) NOT NULL;

ALTER TABLE WMBOMTABLE ADD CONSTRAINT PK_WMBomTable PRIMARY KEY CLUSTERED (BomID);

ALTER DATABASE WMlive SET RECOVERY COMPLETO CON NO_WAIT

 0
Author: Jatin Dave,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-09-29 11:44:25