Te presento mi nuevo sitio web: "El Futuro de los Datos"

Aunque SQL Server Si!, seguirá activo, iré bajando la frecuencia de publicación.
Si quieres conocer todas las novedades que vaya publicando, te recomiendo que lo visites y te suscribas. Tengo un regalito para mi audiencia:

Tu primer Dashboard en "piloto automático" listo en 30 minutos
Sólo para suscriptores.
.

2 ene. 2009

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

7 comentarios:

Elicx dijo...

Muy buen post, pero en bases de datos grandes con filtros dinamicos no funcionan bien ya que las particiones son horizontales o mixtas y dinamicas, en eso caso es conveniente utilizar columnas UniqueIdentifier lo cual hace que el valor sea de 32 Bits imposible de repetirse en la misma base de datos, aunado a esto podemos indicar que el valor por default sea la funcion NewId() lo que la hace que sea similar a identity, y como propiedad del campo le decimos que sea RowID lo que hace que al publicar la base de datos ya no se modifican los articulos al validar las tablas para valores de identificadores unicos. Saludos Elicx Villaseca.

Salvador Ramos dijo...

Hola Elicx,
Muchas gracias, me alegro que te haya gustado el post.
En cuanto a tus comentarios, hay muchos casos en los que no es recomendable el uso de identity. Como cualquier cosa, tiene sus pros y sus contras, en este post sólo me he centrado en explicar cómo funciona. A ver si más adelante escribo sobre casos en los que es recomendable usarlos y otros en los que no.

Saludos
Salvador Ramos

EMM dijo...

Muchas gracias,
me ha venido muy bien la información sobre los autoincrementables.

Alex Schenck dijo...

Salvador, me gustaría saber como le inserto un identity en un campo que ya existe, está muy bueno el post, muchas gracias por la información.

Alex Schenck dijo...

Aclaro que es para SQL Server 2008, desde ya muchas gracias.

salvador ramos dijo...

Si la tabla está vacía, simplemente insertalo.
Si la tabla ya tiene datos no puedes directamente, piensa que tienes que asignar valores a ese campo. Para ello el proceso a grandes rasgos es:
- Crear nueva tabla con la columna identity
- Con insert into ... select llenarla con las filas
- Eliminar la antigua y renombrar ésta.
- Si además tienes claves externas en esta tabla deberás tenerlo en cuenta y hacer los pasos necesarios para volverlas a habilitar.

Saludos
Salvador Ramos

Agustin Mota dijo...

Salvador, antes que nada agradezco infinitamente que compartas tus conocimientos con la comunidad, tengo una pregunta, actualmente utilizo sqlserver azure, pero me he encontrado con un detalle, en los campos identity he notado que practicamente en todas mis tablas existen "brincos" o saltos en los valores por ejemplo 1,2, 3,4, 5, 1001,1002,2001,2002,2003,3001 etc, he tratado de investigar algo y al parecer tanto la versión 2012 y azure utilizan algo asi como bloques de valores identity por lo que puede estar pasando esta situación, sabras algo al respecto y tendrás algún consejo o tip de como darle solución? de antemano gracias

Google