Grimpi IT Blog

abril 1, 2011

Habilitar/Deshabilitar una columna Identity de manera optima

Filed under: Identitys, SQL Server — grimpi @ 8:45 pm

En este post habíamos explicado como habilitar/deshabilitar la propiedad identity de una tabla en SQL Server. Habíamos visto que no existe un comando natural que haga esta tarea (algo así como un ALTER TABLE Cliente ALTER COLUMN ClienteId SET IDENTITY ON/OFF) y por lo tanto, no quedaba otra opción que reconstruir manualmente la tabla entera copiando los datos, con el enorme costo que esto representa.
Sin embargo, existe una forma alternativa, que si tenemos SQL Server 2005/2008 (solo en la edición Developer y Enterprise), podríamos utilizar. Esta solución en consiste en utilizar las opciones de particionamiento que se incorporaron en SQL Server 2005 para mover datos , en vez de utilizar el metodo clasico de reconstrucción de la tabla.
Veamos un ejemplo:

/*Creamos una tabla Paises de ejemplo con el campo row_id con la propiedad IDENTITY habilitada*/
CREATE TABLE dbo.Paises (row_id INTEGER IDENTITY PRIMARY KEY NOT NULL, data SQL_VARIANT NULL);
GO

/*Insertamos información de ejemplo*/
INSERT dbo.Paises (data)
VALUES (‘Argentina’), (‘Brasil’), (‘Chile’), (‘Uruguay’)
GO

/*Paso 1: Borro todos los objetos dependedientes de la tabla a modificar*/
ALTER TABLE ClienteAsociadoAPais DROP CONSTRAINT Paises_Original_FK

/*Paso 2: Creamos una tabla idéntica a la tabla Paises, pero sin la propiedad identity sobre la columna row_id.*/
CREATE TABLE dbo.Destination (row_id INTEGER PRIMARY KEY NOT NULL, data SQL_VARIANT NULL);

/*Paso 3: Movemos el contenido de una tabla a otra.*/
ALTER TABLE dbo.Paises SWITCH TO dbo.Destination;

/*Paso 4: Eliminamos la tabla original (que ahora no tiene datos).*/
DROP TABLE dbo.Paises;

/*Paso 5: Renombramos la tabla para que tenga el nombre original*/
EXECUTE sp_rename N’dbo.Destination’, N’Paises’, ‘OBJECT’;

/*Paso 6: Creo todos los objetos dependedientes de la tabla a modificada que habia borrado en el paso 1*/
ALTER TABLE ClienteAsociadoAPais ADD CONSTRAINT Paises_Original_FK

La clave de esto esta en la sentencia SWITCH, que en realidad no mueve datos, sino que simplemente cambia el puntero de datos de un lugar a otro, con lo cual nos ahorramos un montón de problemas relacionados con lockeos. Este método tiene enorme ventajas por el método que anteriormente habíamos visto en el post anterior. En tabla muy grandes, la diferencia de performance es enorme.
Veamos los siguientes números capturados del profiler, para la tabla ejemplo con varios millones de registros:

Metodo clasico de reconstruccion de tabla:

Duracion CPU Reads Writes
3,856,785 719 115,957 396

Metodo alternativo usando SWITCH:

Duracion CPU Reads Writes
259 140 519 2

Como se puede ver, no hay comparacion posible entre ambos metodos, en tablas de mucho volumen.

Sin embargo, hay un par limitaciones:

  • Esto solo funciona en las versiones Developer y Enterprise de SQL Server, ya que son las únicas que permiten manejo de particiones de tablas.
  • La tabla Destino debe tener exactamente la misma estructura que la tabla origen (con excepción obviamente de la propiedad identity sobre la columna).

Algo que hay que tener en cuenta, es que al igual que el método de reconstrucción de tabla, hay que borrar y crear nuevamente todas las foreign key asociadas a la tabla.

Links relacionados:
Partitioned Tables and Indexes

Anuncios

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.

febrero 12, 2009

Identity en SQL Server

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

Quienes trabajaron mínimamente en alguna versión de SQL Server, conocerán los campos Identitys, que son columnas cuyo valor es autoincremental. Hay muchas discusiones sobre las ventajas y desventajas del uso de este tipo de campos, para las columnas que componen la clave primaria (PK), pero es algo que ya voy a tratar en otro artículo.
Por el momento muestro un listado de tips que cualquier programador que trabaje con SQL Server debería saber al respecto de los campos Identitys:

1) Resetear el valor del campo identity:

Mucha gente piensa que no es posible resetear el contador de un identity de una tabla. FALSO. Existen dos formas de hacerlo, una es mediante TRUNCATE TABLE. Cuando hacemos un TRUNCATE de una tabla, además de borrar todo su contenido, reseteamos el valor del campo Identity. Esto es muy obvio, porque un TRUNCATE TABLE no hace nada más y nada menos que borrar la tabla y volver a crearla.
La segunda opción (y la que nos interesa) es usar el comando DBCC CHECKIDENT. Esta instrucción permite setera el valor que queremos del campo Identity.
Su sintaxis es la siguiente:

DBCC CHECKIDENT <NombreDeTabla>,RESEED,NuevoValor

Hay que tener mucho cuidado con el uso de este comando, ya que podemos setear un valor menor al máximo de la tabla actual y crear en algún momento un valor duplicado. Una opción interesante de este comando es asignar al campo el valor máximo de la tabla + 1, y así garantizar que no tendremos problemas de registros duplicados, para eso solo hay que poner:

DBCC CHECKIDENT <NombreDeTabla>

2) Insertar valores explícitos en un campo Identity:

Cuando se inserta un registro en una tabla con un campo Identity, este se incrementa automáticamente y si queremos modificar o insertar este valor manualmente, el SQL nos tira error y nos indica que no es posible.
Sin embargo, algunas veces puede que necesitemos deshabilitar temporalmente la propiedad Identity, y poder ingresar un valor explícito a la tabla. Generalmente queremos hacer algo así cuando hacemos copias o replicaciones de una tabla de una base a otra tabla.
Para esto, tenemos la opción:


SET IDENTITY_INSERT <NombreDeTabla> ON

Con esta opción, podemos insertar valores en un campo Identity como si fuese un campo más. Luego, cuando terminamos de insertar valores, hay que volver habilitar nuevamente la propiedad Identity del campo. Para eso, corremos el mismo comando anterior, pero con el parámetro OFF.

SET IDENTITY_INSERT <NombreDeTabla> OFF

3) Capturar el valor en un INSERT:

Muchas veces cuando tenemos una tabla con un campo Identity, al hacer un INSERT, queremos saber cuál es el valor que insertó en este campo.
Lo mas común en estos casos es usar @@Identity, que es una variable global que indica el valor del último campo identity insertado en cualquier tabla de la base. Esto último es un detalle muy importante. Si la tabla contiene un trigger que inserta registros en otra tabla, o antes de leer la variable @@Identity, se inserta otro registro en cualquier tabla de la base, el valor @@Identity, tendrá un valor diferente al que estábamos esperando.
Para resolver esta situación, tenemos dos funciones muy útiles: SCOPE_IDENTITY e IDENT_CURRENT. La función SCOPE_IDENTIY nos devuelve el último valor generado dentro de un scope, o sea dentro de un entorno, ya sea un Store Procedure, Function o Trigger. En nuestro caso anterior, como el trigger esta fuera del scope, la función SCOPE_IDENTIY nos devolvería el valor que queremos.
Pero por otro lado, tenemos la función, IDENT_CURRENT, donde se le pasa por parámetro el nombre de la tabla y nos devuelve el ultimo valor identity generado para esta tabla, sin importar el scope. Esta opción en mi opinión es la más clara. Su sintaxis es la siguiente:

SELECT IDENT_CURRENT(‘NombreTabla’)

4) Capturar los valores Identity en un INSERT con multiples registros:

Supongamos que insertamos en una sola sentencia INSERT, mas de un registro en una tabla. Como hacemos para capturar el valor de la columna identity de todos los nuevos registros insertados?. Ni la funcion IDENT_CURRENT ni SCOPE_IDENTITY nos sirven, ya que estas solo devuelven el ultimo valor insertado, pero a nosotros nos interesan todos los valores insertados. Veamos este ejemplo:

CREATE TABLE Cliente (ClienteID INT IDENTITY, Nombre VARCHAR(50))

INSERT INTO Cliente
SELECT ‘Esteban’
UNION
SELECT ‘Juan’
UNION
SELECT ‘Ricardo’

SELECT IDENT_CURRENT(‘Cliente’)

Como ya dijimos antes, IDENT_CURRENT solo nos va a devolver el valor del identity del registro ‘Ricardo’, que fue el último registro que se insertó, por lo tanto, el ejemplo anterior no nos sirve.
Pero a partir de SQL Server 2005, existe la interesante clausula OUTPUT, que permite capturar el valor de las operaciones INSERT y DELETE.
Veamos entonces este ejemplo, que usa OUTPUT y almacena los valores de los registros insertados en una variable de tabla.

DECLARE @NuevosIdentitys TABLE (ID INT)

INSERT INTO Cliente
OUTPUT INSERTED.ClienteID INTO @NuevosIdentitys(ID)

SELECT ‘Esteban’
UNION
SELECT ‘Juan’
UNION
SELECT ‘Ricardo’

SELECT * FROM @NuevosIdentitys

5) Detectar si una columna es Identity:

Usando la función COLUMNPROPERTY, podemos obtener esta información.

SELECT COLUMNPROPERTY(OBJECT_ID(‘<NombreDeTabla>’),’<NombreColumna>‘,‘IsIdentity’)

Otra opción, más potente, consiste en usar la vista sys.identity_columns
(solo a partir de SQL Server 2005), que además de determinar si una columna es Identity, nos permite obtener mayor información sobre la columna relacionada con esta propiedad, como por ejemplo el valor inicial, el valor de incremento, si se aplica o no también para casos de replicación (IsNotForReplication), etc.

SELECT
Name,
is_identity,
seed_value,
increment_value,
is_not_for_replication,
last_value
FROM sys.identity_columns

6) Obtener el valor de la columna Identity aun si saber el nombre del campo:

Supongamos que queremos obtener el valor de una columna que sabemos que es identity, pero solo sabemos el nombre de la tabla (en situaciones donde ejecutamos SQL dinámico podría llegar a darse un caso así).
Existe en SQL Server una función que se llama IDENTITYCOL, que precisamente nos devuelve este valor que queremos averiguar.

SELECT IDENTITYCOL FROM <NombreDeTabla>

Actualizacion:
Hice un nuevo post con mas tips de como trabajar con columnas identities.

Blog de WordPress.com.