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

2 comentarios »

  1. Muchisimas gracia colega :D

    Comentario por Anónimo — junio 24, 2012 @ 6:13 am


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: