Grimpi IT Blog

enero 14, 2011

Identity avanzado en SQL Server

Filed under: Identitys, SQL Server — grimpi @ 4:11 pm

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.

1) 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. No existe algo asi como un ALTER COLUMN Nombre_Campo SET IDENTITY ON (Como dato curioso, Sybase si tiene esta posibilidad). 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) la propiedad 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, y con muy buen criterio, a partir de SQL Server 2008, el Enterprise Manager deshabilita por defecto todas las operaciones sobre edicion de tablas y columnas, que signifiquen 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, este 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.

2) 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 de sistema que nos indique el estado de la tabla con respecto a esta situación.

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’

3) Consultar si una columna es identity

Para esto, debemos ver las vistas de sistema de SQL Server.
La forma mas práctica, es consultar 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.

Recordemos que estos ejemplos solo aplican a SQL Server 2005/2008 en adelante. No funciona en SQL Server 2000.

4) 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 para habilitar la propiedad NOT FOR REPLICATION:

EXEC sys.sp_identitycolumnforreplication OBJECT_ID(“NombreDeLaTabla”), 1



Ejemplo para deshabilitar la propiedad NOT FOR REPLICATION:

EXEC sys.sp_identitycolumnforreplication OBJECT_ID(“NombreDeLaTabla”), 0

5) Buscar gaps o huecos dentro de una columna
Esta es una pregunta habitual, pero la realidad es que no se me ocurre un escenario real donde un hueco entre los valores de una columna identity pueda ser relevante para nosotros. Generalmente quienes consideran relevante esto, es porque estan haciendo un mal uso conceptual de los identities. Recordemos que un identity funciona perfecto como valor interno para una clave primaria. Pero le queremos dar otros usos (como por ejemplo, que sea el codigo numerico de una factura) estamos cometiendo un error suicida. Los identities no son transaccionales y no se puede confiar en una tabla no tenga huecos si se efectuan operaciones de DELETE sobre la misma.

De todos modos, quienes esten buscando gaps, les recomiendo entrar a este post del excelente blog de Pinal Dave, donde no solamente está disponible un script para realizar dicha consulta (para nada algo trivial), sino que ademas hay una muy buena polémica al respecto del uso de los identities.

5 comentarios »

  1. […] Hice un nuevo post con mas tips de como trabajar con columnas identities. Comentarios (17) LikeBe the first […]

    Pingback por Identity en SQL Server « Grimpi IT Blog — enero 17, 2011 @ 7:59 pm

  2. Muy bueno grimpi

    Comentario por Ezequiel — enero 18, 2011 @ 3:45 pm

  3. […] Identity de manera optima Archivado en: Identitys, SQL Server — grimpi @ 8:45 pm En este post habíamos explicado como habilitar/deshabilitar la propiedad identity de una tabla en […]

    Pingback por Habilitar/Deshabilitar una columna Identity de manera optima « Grimpi IT Blog — abril 2, 2011 @ 12:35 am

  4. Excelente me salvaste la vida …muchas gracias

    Comentario por Anónimo — enero 31, 2013 @ 6:34 pm

  5. es mejor desde el visual studio 2008, te conectas a la base
    y editas el diseño de la tabla, NOT FOR REPLICATION le pones true, cierras la tabla, grabas
    luego vuelves a editar el diseño de la tabla y le pones false al campo identity.

    Comentario por Harold — febrero 10, 2014 @ 4:08 pm


RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Blog de WordPress.com.

A %d blogueros les gusta esto: