sql-server - row_number - numero de fila sql server

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

Question

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

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

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

Answer 1

SQL Server: cómo configurar el auto-incremento en una tabla con filas en él:

Esta estrategia copia físicamente las filas alrededor de dos veces, lo que puede llevar 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 principal, luego cargar los datos nuevamente.

Te guiare con un ejemplo:

Paso 1, crea tabla foobar (sin clave principal o autoincremento):

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

Paso 2, inserta 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 foobar en una tabla temporal:

select * into temp_foobar from foobar

Paso 4, drop table foobar:

drop table foobar;

Paso 5, recrea tu tabla con la clave principal 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 nuevamente en foobar

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

Paso 7, suelte su tabla temporal y verifique si funcionó:

drop table temp_foobar;
select * from foobar;

Debería obtener esto, y cuando inspeccione la tabla de foobar, la columna de identificación es autoincremento de 1 y la identificación es una clave principal:

1    one
2    two
3    three

Answer 2

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

Para las personas que encuentren 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 establecer el next value for seq como valor predeterminado de la columna.

Alternativamente, o para versiones anteriores (desde 2005 en adelante), la solución temporal publicada en este elemento de conexión muestra una forma completamente compatible de hacerlo sin necesidad de tamaño de operaciones de datos utilizando ALTER TABLE...SWITCH . También escribí un blog sobre MSDN here . Aunque el código para lograr esto no es muy simple y existen restricciones, como que la tabla que se está modificando no puede ser el objetivo de una restricción de clave externa.

Código de ejemplo

Configure la tabla de prueba sin columna de 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

Altere para tener una columna de identity (más o menos instantánea).

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

Answer 3

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 here .

Answer 4

Sí tu puedes. Vaya a Herramientas> Diseñadores> Tabla y diseñadores y desmarque "Evitar guardar cambios que evitan la recreación de tablas" .

Answer 5

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 nulable

Answer 6

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, soltar la tabla y volver a crear con la columna IDENTIDAD especificada. Luego, usando SET IDENTITY INSERT ON , puede insertar todos sus datos en la tabla temporal en la nueva tabla.