Este documento, actualizado para la versión 12.0 de Adaptive Server Enterprise (ASE), contiene algunas consideraciones y recomendaciones relacionadas al uso de columnas tipo IDENTITY.
¿Qué son las columnas tipo IDENTITY?
Las columnas tipo IDENTITY contienen valores generados por el sistema, que de manera única identifican cada fila de una tabla. Son utilizados para almacenar números secuenciales, tales como códigos de empleados o consecutivos de órdenes de pedido, que son generados de manera automática por ASE. El valor de la columna IDENTIDAD identifica de manera única cada fila de la tabla.
Definición de una Columna IDENTITY
En una tabla nueva
Utilice el comando create table de la siguiente manera:
create table nombre_de_tabla
(nombre_de_columna numeric(precision, 0) identity)
En el siguiente ejemplo se crea la tabla mi_tabla con una columna IDENTITY llamada id cuyos valores irán entre 1 y 104 - 1, o 9999:
create table mi_tabla
(id numeric(4,0) identity,
col1 char(5))
En una tabla existente
Utilice el comando alter table de la siguiente manera:
alter table nombre_de_tabla
add nombre_de_columna numeric(precision, 0) identity
En el siguiente ejemplo se agrega una columna IDENTITY llamada id a la tabla almacenes:
alter table almacenes
add id numeric(5,0) identity
Cuando se agrega una columna IDENTITY a una tabla, Adaptive Server Enterprise asigna un número secuencial único, comenzando en 1, a cada fila de la tabla. Si la tabla contiene un gran número de filas éste proceso puede ser largo. Si el número de filas excede el valor máximo permitido para la columna (en éste caso 105 - 1, o 99999), el comando alter table falla.
¿Cómo Controlar "saltos" en los Valores de una Columna IDENTITY?
Los valores de una columna IDENTITY pueden ir desde 1 hasta 10precision - 1. Adaptive Server Enterprise divide el conjunto de posibles valores en bloques de números consecutivos, y hace que un bloque esté disponible en memoria, en un momento determinado.
Cuando se asigna un valor a una columna IDENTITY, Adaptive Server Enterprise toma el siguiente valor disponible del bloque. Una vez que todos los números del bloque han sido asignados, Adaptive Server Enterprise hace que el siguiente bloque quede disponible.
El seleccionar el siguiente valor IDENTITY de un bloque de números en memoria puede mejorar el rendimiento, pero puede tener consecuencias como "saltos" en los valores de la columna.
"Saltos" debidos a fallas en Adaptive Server Enterprise
Cuando ASE falla, o es bajado con el comando shutdown with nowait, descarta cualquier valor restante en el bloque actual. Cuando se reinicia Adaptive Server Enterprise, queda disponible el siguiente bloque de números para la columna IDENTITY.
Usted puede utilizar el parámetro de configuración identity burning set factor para controlar el tamaño de los "saltos" resultantes de una falla en el servidor.
"Saltos" debidos a inserciones, borrados y cancelaciones
Las inserciones manuales a la columna IDENTITY, los borrados de filas y la cancelación de transacciones pueden crear "saltos" en los valores de la columna IDENTITY. Estos "saltos" no se ven afectados por el valor del parámetro de configuración identity burning set factor.
Por ejemplo, asuma que usted tiene una columna IDENTITY con los siguientes valores:
select syb_identity
from almacenes
id_col
-------
1
2
3
4
5
(5 rows affected)
Usted puede borrar las filas para las cuales id_col (la columna IDENTITY) está entre 2 y 4:
delete almacenes
where syb_identity between 2 and 4
select syb_identity
from almacenes
id_col
-------
1
5
(2 rows affected)
Esto resulta en un "salto" en los valores de la columna id_col.
Así mismo, si se activa la opción identity_insert para la tabla, el dueño de la tabla, el dueño de la base de datos o un usuario SA pueden, manualmente, insertar cualquier valor legal mayor a 5. Por ejemplo,
insert almacenes (syb_identity, stor_id, stor_name)
values (55, "5052", "Buenas Lecturas")
select syb_identity
from almacenes
id_col
-------
1
5
55
(3 rows affected)
Si la opción identity_insert es desactivada, Adaptive Server Enterprise asigna un valor de 55+1, o 56, para la siguiente fila insertada.
"Saltos" debidos a operaciones de dump/load database
Bajo algunas circunstancias, los comandos dump database y load database pueden causar pérdida de valores de columnas IDENTITY, generando "saltos". La única manera de evitar estos "saltos" es bajando Adaptive Server Enterprise e iniciándolo de nuevo, ANTES de realizar el dump database.
¿Cómo corregir saltos en columnas IDENTITY?
Otra alternativa es a través del comando insert...select de Transact-SQL. Por ejemplo,
Cree la nueva tabla, con la misma estructura de la tabla a corregir:
create nueva_tabla
(id numeric(5,0) identity,
nom char(10))
Copie el contenido de la tabla original, en la nueva tabla, omitiendo los valores de la columna IDENTITY:
insert into nueva_tabla(nom)
select nom
from tabla_original
Borre la tabla original y renombre la nueva:
drop table tabla_original
exec sp_rename nueva_tabla, tabla_original
Finalmente, recree índices y/o restricciones de integridad referencial.
¿Es Posible evitar en un 100% "saltos" en columnas IDENTITY?
ASE Versión 11.x
En ASE versión 11.x en principio NO es posible. Por la naturaleza y funcionalidad de las columnas IDENTITY, siempre existirá el riesgo de que se presenten "saltos" en los valores de la columna. Sybase recomienda que si se requieren generar números secuenciales sin "saltos" (por ejemplo para facturas), se utilice alguna técnica basada en una tabla de consecutivos, que mantenga el siguiente valor a asignar.
El siguiente ejemplo ilustra ésta técnica:
/* Inicie una transacción */
begin tran
/* Obtenga de la tabla consecutivos, el valor a asignar.
Utilice la clásula holdlock del comando select para
evitar que otros usuarios modifiquen el consecutivo
actual antes de finalizar la presente transacción */
declare @doc_id numeric(10,0)
select @doc_id = doc_id
from consecutivos holdlock
/* Inserte el registro identificado con el
número consecutivo obtenido */
insert documentos
values(@doc_id, doc_tema, doc_fecha,
doc_tipo, doc_titulo)
/* Actualice la tabla de consecutivos, con el
siguiente valor */
update concecutivos set doc_id = doc_id + 1
/* Confirme la transacción */
commit tran
ASE Versión 12.x y Posteriores
En ASE 12.x el parámetro identity_gap de los comandos create table y select into permite controlar el tamaño de los "saltos" en una columna tipo IDENTITY, para una tabla en particular.
Por ejemplo, para evitar en un 100% "saltos" en una columna tipo IDENTITY de una tabla T1, defina su identity_gap en 1:
create table T1(
id numeric(5,0),
nom varchar(30))
with identity_gap = 1
El identity_gap de una tabla puede ser cambiado utilizando el procedimiento almacenado del sistema sp_chgattribute, y visualizado usando el procedimiento sp_help.
Note que entre más pequeño sea el identity_gap de una tabla, menos riesgo habrá de "saltos" en las columnas tipo IDENTITY. Sinembargo, un identity_gap pequeño causará cierta penalización desde el punto de vista del rendimiento, ya que ASE deberá realizar lecturas más frecuentes a disco para generar los nuevos números.