Creación de campos Autonuméricos (identity) en SQL Server

Se puede definir una columna de valor incremental al momento de crear su tabla o alterar su estructura.

Adicionalmente, se puede definir una "semilla" que se utilizara como valor inicial, en la primera fila, mientras que se utilizara el valor "incremento" para ir calculando los siguientes.

Para realizar esta tarea desde el Administrador Corporativo, bien en la creación o en la modificación de una tabla, tenemos los campos: identidad (identity), iniciación de identidad, e incremento de identidad.

Podemos utilizar cualquier tipo de dato numérico, en la figura anterior hemos utilizado un int, cuyo valor inicial es 100, y su incremento 1.

En el siguiente ejemplo, crea la misma tabla "alumnos" con un campo que representa
un código de identificación que tendrá valores a partir de 100:

CREATE TABLE alumnos (Nombre char(20), ident int IDENTITY (100,1), curso char(5), edad int null)

En el siguiente ejemplo, se altera una tabla para agregar una columna autoincremental:
ALTER TABLE ex_alumnos ADD ex_alumno_Id INT IDENTITY (100,1)

Usar NOT FOR REPLICATION.

La opción NOT FOR REPLICATION se utiliza en la duplicación de Microsoft® SQL Server™ 2000 para implementar intervalos de valores de identidad en un entorno con particiones. La opción NOT FOR REPLICATION es especialmente útil en una duplicación transaccional o de mezcla cuando una tabla publicada se divide en particiones con filas de varios sitios.

Cuando un agente de duplicación se conecta con una tabla con cualquier identificador de inicio de sesión, se activan todas las opciones NOT FOR REPLICATION de la tabla. Cuando se establece la opción, SQL Server 2000 mantiene los valores de identidad originales de las filas agregadas por el agente de duplicación, pero sigue incrementando el valor de identidad en las filas agregadas por otros usuarios. Cuando un usuario agrega una nueva fila a la tabla, el valor de identidad se incrementa de forma normal. Cuando un agente de duplicación duplica dicha fila en un suscriptor, el valor de identidad no se ve modificado cuando la fila se inserta en la tabla del suscriptor.

Por ejemplo, considere una tabla que contenga filas insertadas desde dos orígenes: el Publicador A y el Publicador B. Las filas insertadas en el Publicador A se identifican con valores crecientes entre 1 y 1000, y las filas del Publicador B se identifican con valores entre 1001 y 2000. Si un proceso del Publicador A inserta una fila localmente en la tabla, SQL Server asigna a la primera fila el valor 1, a la siguiente fila el valor 2 y así sucesivamente, en incrementos automáticos. De forma similar, si un proceso del Publicador B inserta una fila localmente en la tabla, a la primera fila se le asigna el valor 1001, a la siguiente fila el valor 1002, y así sucesivamente. Cuando se duplican las filas del Publicador A en el B, los valores de identidad siguen siendo 1, 2, etc., pero los valores de inicio locales no se reinician en el Publicador B.

Independientemente del papel que desempeñe en la duplicación, la propiedad IDENTITY no requiere que sea única por sí misma, simplemente inserta el valor siguiente. Aunque puede proporcionar un valor explícito con SET IDENTITY INSERT, dicha función no es apropiada para la duplicación, ya que también vuelve a iniciar el valor. La opción NOT FOR REPLICATION se ha creado específicamente para las aplicaciones que utilizan la duplicación. Por ejemplo, sin esta opción, en cuanto la primera fila del Publicador B (con valor 1001) se propagara al Publicador A, el siguiente valor de identidad del Publicador A sería 1002. La opción NOT FOR REPLICATION es una forma de indicar a SQL Server 2000 que el proceso de duplicación prescinde de dicho valor cuando suministra uno explícito y que el contador local no tiene que reiniciarse. Cada publicador que utilice esta opción obtiene el mismo permiso para no reiniciar el contador.

Se requieren procedimientos almacenados personalizados que utilicen instrucciones INSERT, UPDATE y DELETE con listas de columnas completas, antes de que la duplicación funcione con propiedades de identidad. Si no se utilizan listas de columnas completas, se devolverá un error.

El siguiente ejemplo de código ilustra cómo implementar identidades con intervalos diferentes en cada publicador:

En el Publicador A, empieza por 1 e incrementa de 1 en 1.
CREATE TABLE authors ( COL1 INT IDENTITY (1, 1) NOT FOR REPLICATION PRIMARY KEY )

En el Publicador B, empieza por 1001 y se incrementa de 1 en 1.
CREATE TABLE authors ( COL1 INT IDENTITY (1001, 1) NOT FOR REPLICATION PRIMARY KEY )

Después de activar la opción NOT FOR REPLICATION, las conexiones de los agentes de duplicación con el Publicador A insertan filas con valores como 1, 2, 3 y 4. Dichas filas se duplican en el Publicador B sin ser modificadas (es decir, 1, 2, 3 y 4). Las conexiones desde agentes de duplicación con el Publicador B obtienen los valores 1001, 1002, 1003 y 1004. Dichas filas se duplican en el Publicador A sin ser modificadas. Cuando se distribuyen o se mezclan todos los datos, ambos Publicadores tienen los valores 1, 2, 3, 4, 1001, 1002, 1003 y 1004. El valor de la siguiente fila insertada localmente en el Publicador A es 5. El valor de la siguiente fila insertada localmente en el Publicador B es 1005.

Se recomienda utilizar siempre la opción NOT FOR REPLICATION con la restricción CHECK para asegurar que los valores de identidad asignados están dentro del intervalo permitido. Por ejemplo:
CREATE TABLE sales
(sale_id INT IDENTITY(100001,1)
NOT FOR REPLICATION
CHECK NOT FOR REPLICATION (sale_id <= 200000),
sales_region CHAR(2),
CONSTRAINT id_pk PRIMARY KEY (sale_id)
)

Incluso si alguien utiliza SET IDENTITY INSERT, todos los valores insertados localmente quedan dentro del intervalo definido. Sin embargo, los procesos de duplicación siguen quedando fuera de la comprobación.

Nota Si va a utilizar la duplicación transaccional con la opción de actualización de suscriptores inmediata, no utilice el diseño IDENTITY NOT FOR REPLICATION. En su lugar, cree la propiedad IDENTITY sólo en el publicador y haga que el suscriptor utilice sólo el tipo de datos de base (por ejemplo, int). Así, el siguiente valor de identidad siempre se genera en el publicador.

Usar DBCC CHECKIDENT.

Se utiliza para cambiar o alterar el contenido de una columna auto incremental (IDENTITY).

Sintaxis
DBCC CHECKIDENT ( 'table_name' [ ,
{ NORESEED | { RESEED [ , ew_reseed_value ] } } ] )

En este ejemplo se establece el valor de identidad actual de la tabla jobs en 30.

USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO