Hace un tiempo escribí un post sobre como trabajar con las columnas identity. Bueno, acá viene la segunda parte con tips un poco mas avanzados.
Modificar una columna para que sea identity:
No se puede. SQL no permite modificar la propiedad Identity de una columna, ya sea para habilitar o deshabilitar esa propiedad. Pero muchos diran, ¿Como no puede ser posible, si yo lo hago desde el enterprise manager?. Bueno, lo que hace ese programa cuando se habilita (o deshabilita) el identity a una columna existente, es reconstruir toda la tabla. Esto significa borrar todas las dependencias de una tabla,
crear una tabla auxiliar similar a la tabla original pero con la propiedad identity cambiada, copiar todo el contenido de la tabla original a la auxiliar, borrar la tabla original, renombrar la tabla auxiliar por el nombre que tenia la tabla original y reconstruir todas las dependencias.
En una tabla vacia, el costo de hacer esto es casi nulo, en una tabla con 20 millones de registros y 30 tablas asociadas a esta via llaves foraneas es casi suicida. Por tal motivo, a partir de SQL Server 2008, el Enterprise Manager deshabilita por defecto todas las operaciones sobre edicion de tablas y columnas, que signifique una reconstruccion de la tabla. Esto sirve para evitar que algun despistado sin conocimiento sobre lo que hace, reconstruya una tabla en un servidor de produccion.
De todas maneras, si necesitamos hacer esto independientemente del costo que nos representa, esta este es un script de ejemplo:
/*Paso 1: Borro todos los objetos dependedientes de la tabla a modificar*/
ALTER TABLE TablaAsociada DROP CONSTRAINT TablaAsociada_Original_FK
/*Paso 2: Creo una tabla nueva exactamente similar a la original, pero agregando la propiedad Identity*/
CREATE TABLE TablaOriginalAuxiliar
(
ID int IDENTITY(1,1),
Campo1 varchar(200)
)
/*Paso 3: Habilitamos el insertado explicito de valores en la columna de tipo identity para mantener los valores antiguos del campo ID*/
SET IDENTITY_INSERT TablaOriginalAuxiliar ON
/*Paso 4: Copio el contenido de una tabla a la otra*/
INSERT INTO TablaOriginalAuxiliar (ID, Campo1) SELECT ID, Campo1 FROM TablaOriginal
/*Paso5: Deshabilitamos el insertado explicito de valores en la columna de tipo identity*/
SET IDENTITY_INSERT TablaOriginalAuxiliar OFF
/*Paso 6: Borro la tabla original*/
DROP TABLE TablaOriginal
/*Paso 7: Renombro la tabla auxiliar por la tabla original*/
sp_rename ‘TablaOriginalAuxiliar’,‘TablaOriginal’
/*Paso 8: Creo todos los objetos dependedientes de la tabla a modificada que habia borrado en el paso 1*/
ALTER TABLE TablaAsociada ADD CONSTRAINT TablaAsociada_Original_FK
Todo esto tambien aplica en el caso inverso, cuando tenemos una columna identity y queremos deshabilitarla.
Determinar el estado del IDENTITY_INSERT en una tabla:
En algunas ocasiones (no muchas) puede ser necesario verificar si una tabla que tiene una columna identity, tiene en estado ON o OFF la opcion IDENTITY_INSERT. Nuevamente, no es posible de manera directa hacer esto. No hay ninguna vista ni tabla que nos indique el estado de la tabla.
Sin embargo, existen métodos alternativos para hacer esto.
El método mas común es insertar un registro en tabla que queremos seteandole un valor explicito que sabemos que no existe en la columna (ejemplo: un 0, que no suele ser un valor habitual para un ID) en la columna identity. Si tira error, es porque la opción esta deshabilitada. Toda esta operación debe estar encapsulada dentro de una transacción y al finalizar, deberemos hacer un rollback.
Ejemplo:
BEGIN TRANSACTION
DECLARE @err int
INSERT INTO tabla1 (id) values (0)
SET @err=@@error
ROLLBACK TRANSACTION
IF @err =0
print ‘identity_insert = on’
else
print ‘identity_insert = off’
Consultar si una columna es identity
Para esto, debemos ver las vistas de sistema de SQL Server.
La forma mas practica, es consulta la vista sys.column y verificar el valor del campo is_identity.
Ejemplo:
select name, is_identity from sys.columns where OBJECTNAME(object_id) = ‘NombreDeLaTabla’ and name = ‘NombreColumna’
Existe tambien una vista llamada sys.identity_columns que nos devuelve todas las columnas identity de todas las tablas de la base de datos.
Este ejemplo solo aplica a SQL Server 2005/2008 en adelante. No funciona en SQL Server 2000.
Modificar la propiedad NOT FOR REPLICATION de una columna identity sin reconstruir la tabla:
Por alguna razón desconocida, si deseamos modificar la propiedad NOT FOR REPLICATION de una columna identity desde el enterprise manager de SQL Server, este reconstruye la tabla (hace exactamente lo mismo que vimos en el punto 1 al principio del post).
Sin embargo, existe una manera de hacer esto muchísimo mas eficiente y es llamar al store procedure de sistema sys.sp_identitycolumnforreplication.
Internamente este store procedure llama a un proceso interno de SQL Server que modifica la tabla, sin necesidad de reconstruirla.
Ejemplo:
EXEC sys.sp_identitycolumnforreplication OBJECT_ID(“NombreDeLaTabla”), 1
