387 votos

Añadir una identidad a una columna existente

Necesito cambiar la clave primaria de una tabla por una columna de identidad, y ya hay un número de filas en la tabla.

Tengo un script para limpiar los IDs para asegurar que son secuenciales empezando por el 1, funciona bien en mi base de datos de prueba.

¿Cuál es el comando SQL para modificar la columna para que tenga una propiedad de identidad?

422voto

John Sansom Puntos 20087

No se pueden alterar las columnas existentes para la identidad.

Tienes dos opciones,

  1. Crear una nueva tabla con identidad y eliminar la tabla existente

  2. Crear una nueva columna con identidad y soltar la columna existente

Enfoque 1. ( Nueva tabla ) Aquí puede conservar los valores de datos existentes en la columna de identidad recién creada. ¡Tenga en cuenta que perderá todos los datos si no se satisface la condición "si no existe", así que asegúrese de poner la condición en la caída también!

CREATE TABLE dbo.Tmp_Names
    (
      Id int NOT NULL
             IDENTITY(1, 1),
      Name varchar(50) NULL
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT dbo.Tmp_Names ON
go

IF EXISTS ( SELECT  *
            FROM    dbo.Names ) 
    INSERT  INTO dbo.Tmp_Names ( Id, Name )
            SELECT  Id,
                    Name
            FROM    dbo.Names TABLOCKX
go

SET IDENTITY_INSERT dbo.Tmp_Names OFF
go

DROP TABLE dbo.Names
go

Exec sp_rename 'Tmp_Names', 'Names'

Enfoque 2 ( Nueva columna ) No puede conservar los valores de datos existentes en la columna de identidad recién creada, La columna de identidad mantendrá la secuencia de números.

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'

Consulte el siguiente post del foro de Microsoft Sql Server para obtener más detalles:

Cómo modificar la columna a identidad(1,1)

181voto

Justin Grant Puntos 25644

En SQL 2005 y superiores, hay un truco para resolver este problema sin cambiar las páginas de datos de la tabla. Esto es importante para tablas grandes en las que tocar cada página de datos puede llevar minutos u horas. El truco también funciona aunque la columna de identidad sea una clave primaria, forme parte de un índice agrupado o no agrupado, o de otros inconvenientes que pueden hacer fracasar la solución más sencilla de "añadir/eliminar/renombrar columna".

El truco es el siguiente: puede utilizar la función de Sql Server ALTERAR TABLA...CAMBIAR para cambiar el esquema de una tabla sin cambiar los datos, lo que significa que puede reemplazar una tabla con un IDENTITY con un esquema de tabla idéntico, pero sin una columna IDENTITY. El mismo truco funciona para añadir IDENTITY a una columna existente.

Normalmente, ALTERAR TABLA...CAMBIAR se utiliza para reemplazar eficientemente una partición completa en una tabla particionada con una nueva partición vacía. Pero también se puede utilizar en tablas no particionadas.

He utilizado este truco para convertir, en menos de 5 segundos, una columna de una tabla de 2.500 millones de filas de IDENTITY a no IDENTITY (para poder ejecutar una consulta de varias horas cuyo plan de consulta funcionaba mejor para las columnas no IDENTITY), y luego restaurar la configuración de IDENTITY, de nuevo en menos de 5 segundos.

Aquí hay un ejemplo de código de cómo funciona.

 CREATE TABLE Test
 (
   id int identity(1,1),
   somecolumn varchar(10)
 );

 INSERT INTO Test VALUES ('Hello');
 INSERT INTO Test VALUES ('World');

 -- copy the table. use same schema, but no identity
 CREATE TABLE Test2
 (
   id int NOT NULL,
   somecolumn varchar(10)
 );

 ALTER TABLE Test SWITCH TO Test2;

 -- drop the original (now empty) table
 DROP TABLE Test;

 -- rename new table to old table's name
 EXEC sp_rename 'Test2','Test';

 -- update the identity seed
 DBCC CHECKIDENT('Test');

 -- see same records
 SELECT * FROM Test; 

Obviamente, esto es más complicado que las soluciones de otras respuestas, pero si tu tabla es grande puede ser un verdadero salvavidas. Hay algunas advertencias:

  • Que yo sepa, la identidad es lo único que puedes cambiar de las columnas de tu tabla con este método. No se permite añadir/eliminar columnas, cambiar la anulabilidad, etc.
  • Tendrás que soltar las llaves foráneas antes de hacer el cambio y restaurarlas después.
  • Lo mismo para las funciones WITH SCHEMABINDING, las vistas, etc.
  • los índices de la nueva tabla deben coincidir exactamente (mismas columnas, mismo orden, etc.)
  • Las tablas antiguas y las nuevas deben estar en el mismo grupo de archivos.
  • Sólo funciona en Sql Server 2005 o posterior
  • Anteriormente creía que este truco sólo funcionaba en las ediciones Enterprise o Developer de Sql Server (porque las particiones sólo se admiten en las versiones Enterprise y Developer), pero Mason G. Zhwiti en su comentario más abajo dice que también funciona en SQL Standard Edition. Supongo que esto significa que la restricción a Enterprise o Developer no se aplica a ALTER TABLE...SWITCH.

Hay una buena artículo en TechNet detallando los requisitos anteriores.

ACTUALIZACIÓN - Eric Wu tenía un comentario más abajo que añade información importante sobre esta solución. Lo copio aquí para asegurarme de que reciba más atención:

Hay otra advertencia que vale la pena mencionar. Aunque la nueva tabla recibirá felizmente los datos de la tabla antigua, y todas las nuevas filas se insertarán siguiendo un patrón de identidad, comenzarán comenzarán en 1 y potencialmente se romperán si dicha columna es una clave primaria. Considere la posibilidad de ejecutar DBCC CHECKIDENT('<newTableName>') inmediatamente después de el cambio. Ver msdn.microsoft.com/es-us/library/ms176057.aspx para más información.

Si la tabla se está ampliando activamente con nuevas filas (lo que significa que no tiene mucho o ningún tiempo de inactividad entre la adición de IDENTITY y la adición de nuevas filas, entonces en lugar de DBCC CHECKIDENT querrá establecer manualmente el valor de la semilla de identidad en el nuevo esquema de la tabla para que sea mayor que el mayor ID existente en la tabla, por ejemplo IDENTITY (2435457, 1) . Es posible que pueda incluir tanto el ALTER TABLE...SWITCH y el DBCC CHECKIDENT en una transacción (o no-- no lo he probado) pero parece que establecer el valor de la semilla manualmente será más fácil y seguro.

Obviamente, si no se añaden nuevas filas a la tabla (o sólo se añaden ocasionalmente, como un proceso ETL diario), esta condición de carrera no se producirá, así que DBCC CHECKIDENT está bien.

63voto

marc_s Puntos 321990

No se puede modificar una columna para que sea una columna IDENTIDAD. Lo que tienes que hacer es crear una nueva columna que esté definida como IDENTIDAD desde el principio, luego eliminar la columna antigua y renombrar la nueva con el nombre anterior.

ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'

Marc

14voto

greenoldman Puntos 4575

Aquí se describe una solución genial: Sql Server - Añadir o quitar la propiedad de identidad en la columna

En resumen edite manualmente su tabla en SQL Manager, cambie la identidad, NO GUARDE los cambios, solo muestre el script que se creará para los cambios, cópielo y úselo después.

Es un gran ahorro de tiempo, porque (el script) contiene todas las claves foráneas, índices, etc. relacionados con la tabla que cambias. Escribir esto manualmente... Dios no lo quiera.

3voto

SQLMenace Puntos 68670

no se puede hacer así, hay que añadir otra columna, soltar la columna original y cambiar el nombre de la nueva columna, o bien crear una nueva tabla, copiar los datos y soltar la tabla antigua y cambiar el nombre de la nueva tabla por el de la antigua.

si usas SSMS y pones la propiedad identity en ON en el diseñador esto es lo que hace Sql Server entre bastidores. Así que si tienes una tabla llamada [user] esto es lo que pasa si haces UserID e identity

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION

GO

GO
CREATE TABLE dbo.Tmp_User
    (
    UserID int NOT NULL IDENTITY (1, 1),
    LastName varchar(50) NOT NULL,
    FirstName varchar(50) NOT NULL,
    MiddleInitial char(1) NULL

    )  ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Tmp_User ON
GO
IF EXISTS(SELECT * FROM dbo.[User])
 EXEC('INSERT INTO dbo.Tmp_User (UserID, LastName, FirstName, MiddleInitial)
    SELECT UserID, LastName, FirstName, MiddleInitialFROM dbo.[User] TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_User OFF
GO

GO
DROP TABLE dbo.[User]
GO
EXECUTE sp_rename N'dbo.Tmp_User', N'User', 'OBJECT'
GO
ALTER TABLE dbo.[User] ADD CONSTRAINT
    PK_User PRIMARY KEY CLUSTERED 
    (
    UserID
    ) ON [PRIMARY]

GO
COMMIT

Dicho esto, hay una manera de hackear la tabla del sistema para lograrlo estableciendo el valor de los bits, pero eso no es compatible y yo no lo haría

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X