387 votos

Adición de una identidad a una columna existente

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

Tengo un script para limpiar los identificadores para asegurar son secuenciales a partir de las 1, funciona bien en mi base de datos test.

¿Qué es el comando SQL para alterar la columna para tener una propiedad identity?

422voto

John Sansom Puntos 20087

No se puede modificar los existentes en las columnas de identidad.

Tienes 2 opciones,

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

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

Enfoque 1. (Tabla nueva) Aquí usted puede mantener los valores de datos existentes en el recién creado columna de identidad.

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) Usted no puede retener los valores de datos existentes en el recién creado columna de identidad, La identidad de la columna contendrá la secuencia de número.

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'

Ver el siguiente de Microsoft SQL Server en el Foro de post para más detalles:

Cómo modificar columna identity(1,1)

181voto

Justin Grant Puntos 25644

En SQL 2005 y anteriores, hay un truco para solucionar este problema sin cambiar los datos de la tabla de páginas. Esto es importante para las grandes mesas donde tocar cada página de datos puede tardar minutos u horas. El truco también funciona incluso si la identidad de la columna es una clave primaria, es parte de un clúster o no clúster índice, u otros defectos que pueden viaje hasta el más simple "agregar/quitar/cambiar el nombre de la columna" solución".

Aquí está el truco: se puede utilizar SQL Server ALTER TABLE...SWITCH de instrucción para cambiar el esquema de una tabla sin cambiar los datos, lo que significa que puede reemplazar una tabla con una IDENTIDAD con un idéntico esquema de la tabla, pero sin una columna de IDENTIDAD. El mismo truco funciona para agregar IDENTIDAD a una columna existente.

Normalmente, ALTER TABLE...SWITCH se utiliza de manera eficiente reemplazar una partición completa en una tabla con particiones con un nuevo vacío de la partición. Pero también puede ser utilizado en la no-tablas con particiones.

He utilizado este truco para convertir, en menos de 5 segundos, una columna de una de 2,5 millones de fila de la tabla de la IDENTIDAD a la no-IDENTIDAD (con el fin de ejecutar un multi-horas de consulta cuya consulta plan funcionó mejor para la no-IDENTIDAD columnas), y, a continuación, restaurar la configuración de IDENTIDAD, de nuevo en menos de 5 segundos.

He aquí 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';

 -- see same records
 SELECT * FROM Test; 

Este es obviosuly más implicados que las soluciones en otras respuestas, pero si la tabla es grande, esto puede ser un verdadero salvavidas. Hay algunas advertencias:

  • tendrás que colocar las teclas de relaciones exteriores antes de hacer el switch y restaurarlas después.
  • mismo para CON SCHEMABINDING funciones, vistas, etc.
  • nueva tabla de índices deben coincidir exactamente (columnas, un mismo fin, etc.)
  • viejas y nuevas tablas necesitan estar en el mismo grupo de archivos.
  • sólo funciona en SQL Server 2005 o posterior
  • Yo creía que este truco sólo funciona en las ediciones Enterprise o Developer de SQL Server (dado que las particiones sólo se admite en Enterprise y Developer de versiones), pero Mason G. Zhwiti en su comentario a continuación dice que también funciona en SQL Standard Edition. Supongo que esto significa que la restricción a la Empresa o Desarrollador no se aplican a ALTER TABLE...SWITCH.

Hay un buen artículo en TechNet detallando los requisitos arriba.

63voto

marc_s Puntos 321990

No puedes alterar una columna para ser una columna de identidad. Lo que necesitas hacer es crear una nueva columna que se define como una identidad desde el principio, luego suelte la vieja columna y renombrar una nueva al viejo nombre.

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

ALTER TABLE (yourTable) DROP COLUMN OldColumnName

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

Marc

14voto

greenoldman Puntos 4575

No hay solución fría que se describe aquí: SQL SERVER - Agregar o Quitar Propiedad de Identidad en la Columna

En resumen editar manualmente la tabla en SQL Manager, el interruptor de la identidad, NO GUARDE los cambios, sólo se muestran los comandos que se creará para los cambios, copiarlo y usarlo más tarde.

Es enorme ahorro de tiempo, porque (en el guión) contiene todas las claves foráneas, índices, etc. relacionadas con la tabla de cambiar. La redacción de este manual... Dios no lo quiera.

3voto

SQLMenace Puntos 68670

usted no puede hacer eso, usted necesita agregar otra columna, la caída de la columna original y cambiar el nombre de la nueva columna o o crear una nueva tabla, una copia de los datos y la caída de la mesa de edad, seguido por el cambio de nombre de la nueva tabla para la tabla anterior

si utiliza SSMS y establecer la identidad de propiedades en el diseñador de aquí es lo que hace SQL Server detrás de las escenas. Así que si usted tiene una tabla denominada [usuario] esto es lo que sucede si usted hace el id de usuario y la identidad

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

Después de haber dicho que hay una manera de hackear el sistema de la tabla para lograr estableciendo el bit a bit de valor pero que no es compatible y que 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