IDENTITY (propiedad de Transact-SQL)

 

ESTE TEMA SE APLICA A:síSQL Server (a partir de 2008)noBase de datos SQL de AzurenoAlmacenamiento de datos SQL de Azure noAlmacenamiento de datos paralelos

Crea una columna de identidad en una tabla. Esta propiedad se usa con las instrucciones CREATE TABLE y ALTER TABLE de Transact-SQL.

System_CAPS_ICON_note.jpg Nota


La propiedad IDENTITY es diferente de SQL-DMO identidad que expone la propiedad de identidad de fila de una columna.

Topic link icon Convenciones de sintaxis de Transact-SQL

  
IDENTITY [ (seed , increment) ]  

valor de inicialización
Es el valor que se utiliza para la primera fila cargada en la tabla.

incremento
Se trata del valor incremental que se agrega al valor de identidad de la anterior fila cargada.

Debe especificar tanto el valor de inicialización como el incremento, o bien ninguno de los dos. Si no se especifica ninguno, el valor predeterminado es (1,1).

Las columnas de identidad pueden usarse para generar valores de clave. La propiedad de identidad de una columna garantiza lo siguiente:

  • Cada nuevo valor se genera basándose en el valor de inicialización y el incremento actual.

  • Cada nuevo valor de una transacción determinada es diferente de otras transacciones simultáneas de la tabla.

La propiedad de identidad de una columna no garantiza lo siguiente:

  • Unicidad del valor – exclusividad debe exigirse a través de un PRIMARY KEY o UNIQUE restricción o UNIQUE índice.

  • Valores consecutivos dentro de una transacción : no se garantiza que una transacción insertar varias filas obtenga valores consecutivos para las filas, ya que podría producirse otras inserciones simultáneas en la tabla. Si los valores deben ser consecutivos, a continuación, la transacción debe usar un bloqueo exclusivo en la tabla o usar el SERIALIZABLE nivel de aislamiento.

  • Valores consecutivos después de reiniciar el servidor u otros errores –SQL Server podría guardar en caché los valores de identidad por motivos de rendimiento y algunos de los valores asignados podrían perderse durante un reinicio del servidor o error de base de datos. Esto puede tener como resultado espacios en el valor de identidad al insertarlo. Si no es aceptable que haya espacios, la aplicación debe usar mecanismos propios para generar valores de clave. Mediante un generador de secuencias con el NOCACHE opción puede limitar los espacios a transacciones que nunca se hayan confirmado.

  • Reutilización de valores : para una propiedad de identidad especificada con el valor de inicialización e incremento específico, la identidad que el motor no reutiliza los valores. Si una instrucción de inserción concreta produce un error o si la instrucción de inserción se revierte, los valores de identidad utilizados se pierden y no volverán a generarse. Esto puede tener como resultado espacios cuando se generan los valores de identidad siguientes.

Estas restricciones forman parte del diseño para mejorar el rendimiento y porque son aceptables en muchas situaciones comunes. Si no puede usar valores de identidad debido a estas restricciones, cree una tabla independiente que contenga un valor actual y administre el acceso a la tabla y la asignación de números con su aplicación.

Si una tabla con una columna de identidad se publica para replicarla, la columna de identidad debe administrarse de manera apropiada para el tipo de replicación utilizado. Para obtener más información, vea replicar las columnas de identidad.

Solo se puede crear una columna de identidad para cada tabla.

En las tablas con optimización para memoria, el valor de inicialización y el valor de incremento debe establecerse en 1,1. Si se establece el valor de inicialización o el incremento en un valor distinto de 1 da como resultado el siguiente error: el uso de inicialización e incremento de otros valores de 1 no es compatible con tablas optimizadas en memoria.

A. Utilizar la propiedad IDENTITY con CREATE TABLE

En el siguiente ejemplo se crea una nueva tabla con la propiedad IDENTITY para un número de identificación que se incrementa automáticamente.

USE AdventureWorks2012;  
  
IF OBJECT_ID ('dbo.new_employees', 'U') IS NOT NULL  
   DROP TABLE new_employees;  
GO  
CREATE TABLE new_employees  
(  
 id_num int IDENTITY(1,1),  
 fname varchar (20),  
 minit char(1),  
 lname varchar(30)  
);  
  
INSERT new_employees  
   (fname, minit, lname)  
VALUES  
   ('Karin', 'F', 'Josephs');  
  
INSERT new_employees  
   (fname, minit, lname)  
VALUES  
   ('Pirkko', 'O', 'Koskitalo');  

B. Utilizar la sintaxis genérica para buscar espacios en los valores de identidad

En este ejemplo se muestra la sintaxis genérica utilizada para buscar espacios en valores de identidad cuando se quitan datos.

System_CAPS_ICON_note.jpg Nota


La primera parte del siguiente script de Transact-SQL se ha diseñado solo como ejemplo. Puede ejecutar el script de Transact-SQL que comienza por el comentario: -- Create the img table.

-- Here is the generic syntax for finding identity value gaps in data.  
-- The illustrative example starts here.  
SET IDENTITY_INSERT tablename ON;  
DECLARE @minidentval column_type;  
DECLARE @maxidentval column_type;  
DECLARE @nextidentval column_type;  
SELECT @minidentval = MIN($IDENTITY), @maxidentval = MAX($IDENTITY)  
    FROM tablename  
IF @minidentval = IDENT_SEED('tablename')  
   SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('tablename')  
   FROM tablename t1  
   WHERE $IDENTITY BETWEEN IDENT_SEED('tablename') AND   
      @maxidentval AND  
      NOT EXISTS (SELECT * FROM tablename t2  
         WHERE t2.$IDENTITY = t1.$IDENTITY +   
            IDENT_INCR('tablename'))  
ELSE  
   SELECT @nextidentval = IDENT_SEED('tablename');  
SET IDENTITY_INSERT tablename OFF;  
-- Here is an example to find gaps in the actual data.  
-- The table is called img and has two columns: the first column   
-- called id_num, which is an increasing identification number, and the   
-- second column called company_name.  
-- This is the end of the illustration example.  
  
-- Create the img table.  
-- If the img table already exists, drop it.  
-- Create the img table.  
IF OBJECT_ID ('dbo.img', 'U') IS NOT NULL  
   DROP TABLE img;  
GO  
CREATE TABLE img (id_num int IDENTITY(1,1), company_name sysname);  
INSERT img(company_name) VALUES ('New Moon Books');  
INSERT img(company_name) VALUES ('Lucerne Publishing');  
-- SET IDENTITY_INSERT ON and use in img table.  
SET IDENTITY_INSERT img ON;  
  
DECLARE @minidentval smallint;  
DECLARE @nextidentval smallint;  
SELECT @minidentval = MIN($IDENTITY) FROM img  
 IF @minidentval = IDENT_SEED('img')  
    SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img')  
    FROM img t1  
    WHERE $IDENTITY BETWEEN IDENT_SEED('img') AND 32766 AND  
      NOT    EXISTS (SELECT * FROM img t2  
          WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img'))  
 ELSE  
    SELECT @nextidentval = IDENT_SEED('img');  
SET IDENTITY_INSERT img OFF;  

ALTER TABLE ( Transact-SQL )
CREAR TABLA ( Transact-SQL )
DBCC CHECKIDENT ( Transact-SQL )
IDENT_INCR ( Transact-SQL )
@@IDENTITY ( Transact-SQL )
IDENTIDAD ( Función ) ( Transact-SQL )
IDENT_SEED ( Transact-SQL )
SELECCIONE ( Transact-SQL )
SET IDENTITY_INSERT ( Transact-SQL )
Replicar columnas de identidad

Adiciones de comunidad

AGREGAR
Mostrar: