Inicio > Sql Server 2005 - 2008 > Todo lo que se debe saber del Identity en SQL Server

Todo lo que se debe saber del Identity en SQL Server


Los que trabajamos con alguna versión de SQL Server, trabajamos con los campos Identitys, que son columnas cuyo valor es autoincremental.

Hay 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 se tratara en este articulo.

1) Resetear el valor del campo identity:

Es posible resetear el contador de un identity de una tabla.
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.

La segunda opción es usando el comando DBCC CHECKIDENT. Esta instrucción permite setera el valor que queremos del campo Identity.

Sintaxis:

DBCC CHECKIDENT <MiTabla>,RESEED,NuevoValor

Hay que tener 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 <MiTabla>

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.

SET IDENTITY_INSERT <MiTabla> 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

SET IDENTITY_INSERT <MiTabla> OFF

3) Capturar el valor en un INSERT:

Cuando tenemos una tabla con un campo Identity, al ejecutar un INSERT queremos saber cuál es el valor que insertó en este campo.

Lo 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, 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.

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.

SELECT IDENT_CURRENT(‘MiTabla’)

4) Capturar los valores Identity en un INSERT con multiples registros:

En el supuesto que insertamos en una sola sentencia INSERT, mas de un registro en una tabla. Como se haría para capturar el valor de la columna identity de todos los nuevos registros insertados.

Ejemplo:

CREATE TABLE Maestro (Id_Maestro INT IDENTITY, Nombre VARCHAR(80))

INSERT INTO Maestro
SELECT ‘Aldo’
UNION
SELECT ‘Victorio’
UNION
SELECT ‘Alfredo’

SELECT IDENT_CURRENT(‘Maestro’)

Como ya dijimos antes, IDENT_CURRENT solo nos va a devolver el valor del identity del registro ‘Alfredo’, que fue el último registro que se insertó.

SQL Server 2005, existe la interesante clausula OUTPUT, que permite capturar el valor de las operaciones INSERT y DELETE.

Entonces este ejemplo, que usa OUTPUT y almacena los valores de los registros insertados en una variable de tabla.

DECLARE @NuevosIdentitys TABLE (Id_Mestro INT)
INSERT INTO Maestro
OUTPUT INSERTED. Id_Maestro INTO @NuevosIdentitys(Id_Mestro)

SELECT ‘Aldo’
UNION
SELECT ‘Victorio’
UNION
SELECT ‘Alfredo’

SELECT * FROM @NuevosIdentitys

About these ads
  1. Roberto
    17 septiembre 2010 en 6:24 am

    Muy interesante. Yo busco como hacer
    INSERT INTO TABLA (CAMPO1,CAMPOIDENTITY) VALUES (‘VALOR’,????)
    La primera respuesta es ‘NO pongas el campo identity dentro de la tabla’, pero quiero ponerlo por facilidad en la SQL. (tengo 100 campos en la tabla y uno es el identity).
    He probado con default ó null, pero no funciona.
    Quizas usando el IDENTITY_INSERT ON y calcular yo el siguiente identy, pero ya se aleja de lo que necesito.

  2. Roberto
    17 septiembre 2010 en 6:29 am

    Pues acabo de ver la solución:

    set identity_insert articulo on


    insert into articulo(Articulo,IDArticulo,NombreArticulo )
    values (‘borra3′, (SELECT IDENT_CURRENT(‘articulo’)+1), ‘prueba’)

    Espero no se pierda el IDENT_CURRENT de la tabla… sino liada.

  3. 17 septiembre 2010 en 8:05 am

    Hola Roberto :

    SET IDENTITY_INSERT DEPRUEBA ON
    INSERT INTO DEPRUEBA VALUES((SELECT IDENT_CURRENT(‘DEPRUEBA’)+1), ‘PRUEBA’)
    SET IDENTITY_INSERT DEPRUEBA OFF

    Te debe dar el siguiente error :
    — ERRROR : No se permiten subconsultas en este contexto. Sólo se admiten expresiones escalares.

    Prueba utilizando este código que funciona.

    DECLARE @NuevoCodigo INT
    SET @NuevoCodigo =(IDENT_CURRENT(‘DEPRUEBA’)+1)
    PRINT @NuevoCodigo
    SET IDENTITY_INSERT DEPRUEBA ON INSERT INTO DEPRUEBA(ID) VALUES(@NuevoCodigo)SET IDENTITY_INSERT DEPRUEBA OFF
    GO
    PRINT IDENT_CURRENT(‘DEPRUEBA’)

  4. 29 julio 2011 en 6:52 am

    No tengo muy claro lo que quiere hacer con
    INSERT INTO TABLA (CAMPO1,CAMPOIDENTITY) VALUES (‘VALOR’,????)

    porque si tiene un campo Identity y lo que quiere es que SQL se lo calcule lo normal es

    INSERT INTO TABLA (CAMPO1) VALUES (‘VALOR’)

    y SQL le insertará automáticamente el otro campo (el identity)

    • 15 diciembre 2011 en 12:17 pm

      Es que lo que quire hacer es inscertar un dato en un campo IDENTITY ? , que a veces es nac esario.

      Saludos
      Ricardo

  5. 15 diciembre 2011 en 1:30 pm

    Pero lo que me extraña del caso es que quiere calcular el valor del identity con IDENT_CURRENT por eso digo que no lo entiendo porque en el caso que él indica lo que esta haciendo es insertar el mismo valor que SQL calcularía.

    Si por ejemplo quisiera insertar el valor 8 en el campo identity pues si, con poner

    SET IDENTITY_INSERT DEPRUEBA ON
    INSERT INTO DEPRUEBA (campoIdentity, otrocampo) VALUES (8, ‘pruebas’)
    SET IDENTITY_INSERT DEPRUEBA OFF

    ya estaría

    pero si quiere calcularlo no le veo mucho sentido.

  1. 21 marzo 2010 en 10:34 pm

Deja un comentario

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

Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

A %d blogueros les gusta esto: