Grimpi IT Blog

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.

19 comentarios »

  1. Excelente post, no dejaste cabo suelto. Es de referencia ineludible.

    Comentario por Judge Dredd — junio 26, 2009 @ 2:05 pm

  2. El post es muy bueno, ayuda bastante, tengo una consulta adicional, como puedo obtener el valor de IDENTITY_INSERT?, es decir, quiero averiguar su valor actual es ON u OFF

    Saludos

    Andrea

    Comentario por Andrea — julio 28, 2009 @ 3:45 pm

  3. Excelente Trabajo, muy claro. Me va a ayudar Bastante

    Comentario por Abd — julio 31, 2009 @ 5:03 pm

  4. vale, es lo que andaba buscando

    Comentario por Tito — agosto 11, 2009 @ 9:54 pm

  5. Muy bonito :).
    Pero tengo otra consulta relacionada.
    Verán tengo varias tablas y un sólo script de restauración, muchas de esas tablas han sido llenadas previamente y al usar el DBCC CHECKIDENT ,RESEED,0, restaura las que fueron llenadas a 1 (correcto), pero las que nunca se llenaron a 0 (el primer registro inicia con 0 :S) a pesar que se especificó un identity(1,1).

    Se agradece por anticipado :). Bye.

    Comentario por Lourdes — septiembre 19, 2009 @ 1:26 am

    • Ví esa conducta en el comando DBCC dependiendo si la tabla tuvo o no alguna vez datos. La solución es o bien truncar la tabla y te la deja correctamente (con semilla=1) o bien cuando lanzes el comando NO especifiques “,0”, solo deja el Reseed.

      Espero te haya servido.
      Saludos.

      Comentario por Judge Dredd — septiembre 24, 2009 @ 2:57 pm

  6. Muchas Gracias! muy util!

    Comentario por Gustavo — septiembre 28, 2009 @ 3:46 am

  7. Gracias por responder.
    Lo que sucede es que tengo llaves foráneas en esas tablas y no la puedo truncar y cuando ejecuto la sentencia sin el valor “0”, la respuesta es la siguiente:

    Checking identity information: current identity value ’11’, current column value ’11’.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Como advertencia, no error, y no resetea el valor inicial :(.

    Bueno, muchas gracias. Espero se encuentren bien.

    Comentario por Lourdes — septiembre 28, 2009 @ 4:47 am

  8. excelente post, lo que necesitaba……

    Comentario por Anónimo — octubre 22, 2009 @ 1:41 am

  9. Hola,
    una duda relacionada?
    Verás, yo tengo un procedimiento que consta de :
    1.- select que inserta en una tabla auxiliar una cantidad de registros.
    2.- Inserta los registros de la tabla auxiliar en una tabla real, pero tiene que eliminar duplicados por un campo, el numero de telefono.

    En la tabla auxiliar se insertan ordenados, con lo que cuando un número de teléfono se repite, generalmente por un error de inserción de datos (direccion diferente) sql lo toma como si el registro fuera diferente, cuando en realidad es el mismo.

    Existe alguna forma (que no sea usando un cursor, en la que pueda comprobar que si el campo telefono es igual al campo telefono del registro anterior, no lo inserte y pase al siguiente?

    Gracias

    Comentario por Mari Carmen — marzo 5, 2010 @ 11:27 am

  10. Hola buenas tardes…
    tengo un problema cuando intento copiar filas de una tabla en otra, porque esta ultima tiene la PK con la propiedad identity seteada en si.

    utilice esta sentencia para poder insertar:
    SET IDENTITY_INSERT tabla1 ON
    insert into tabla1
    select * from tabla2
    where codigo not in (select codigo from tabla1)

    y me dio el siguiente error:

    Sólo puede especificarse un valor explícito para la columna de identidad de la tabla tabla1 cuando se usa una lista de columnas e IDENTITY_INSERT es ON.

    alguien puede decirme que puedo hacer???
    gracias.

    Comentario por Luciana — marzo 15, 2010 @ 3:36 pm

  11. Muy buen post con la información que nesecitaba. Muchas gracias.

    Comentario por Jose Luis — junio 15, 2010 @ 5:46 pm

  12. Consulta! del comportamiento del DBCC, al ejecutar el comando “DBCC checkident(tabla,RESEED,0)” sobre una tabla que se llenaron datos y fueron limpiados por DELETE normalmente se reinicia el identity para iniciar con “1”, pero en el caso de que estas tablas no hayen tenido incersiones o hallen sido limpiadas por un TRUNCATE, ocurre que el valor del primer registro se ingresa en “0”. Alguna sugerencia, o una validación para poder trabajar en este comportamiento.

    Comentario por jairkf — octubre 27, 2010 @ 5:32 pm

  13. Muchísimas Gracias!! Justo lo que andaba buscando.

    Comentario por redcap — octubre 28, 2010 @ 9:45 am

  14. hola
    tengo una duda si ya tengo creada una tabla con 1000 registros como declaro una para q sea identity una columna (alter) algo asi lo estoy intentando
    alter table consulta(
    Id_columna INT IDENTITY(1,1)
    NOT FOR REPLICATION)

    pero no me funciona

    Comentario por osaca — noviembre 22, 2010 @ 3:29 pm

  15. […] en SQL Server Archivado en: 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 […]

    Pingback por Identity avanzado en SQL Server « Grimpi IT Blog — enero 17, 2011 @ 4:34 pm

  16. […] Fuente:grimpidev.wordpress.com Share this:TwitterFacebookLike this:LikeBe the first to like this post.   Leave a comment […]

    Pingback por Ultimo ID de columna Identity en SQL « Desde Cero .Net y SQL — septiembre 27, 2011 @ 2:19 pm

  17. como puedo crear un campo indentity que inicie en 100 y avance de 5 en 5??.. disculpen pero fue una pregunta de examen y no supe como hacerlo. Gracias de ante mano

    Comentario por Isaac Robles — diciembre 9, 2011 @ 1:58 pm

  18. Gracias! no me quedaba claro el ON OFF del IDENTITY

    Comentario por Vivi Pico (@vivipicolini) — abril 12, 2013 @ 9:50 pm


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: