Generar valores automáticos
Es posible que deba generar automáticamente valores secuenciales para una columna de una tabla específica. Transact-SQL proporciona dos maneras de hacerlo: usar la propiedad IDENTITY con una columna específica de una tabla, o definir un objeto SEQUENCE y usar valores generados por ese objeto.
Propiedad IDENTITY
Para usar la propiedad IDENTITY, defina una columna mediante un tipo de datos numérico con una escala de 0 (es decir, solo números enteros) e incluya la palabra clave IDENTITY. Los tipos permitidos incluyen todos los tipos enteros y decimales donde se proporciona explícitamente una escala de 0.
También se puede especificar una inicialización opcional (valor inicial) y un incremento (valor por etapas). Si se omiten la inicialización y el incremento, se establecerán en 1.
Nota
Se especifica la propiedad IDENTITY, en lugar de especificar NULL o NOT NULL en la definición de columna. Cualquier columna con la propiedad IDENTITY no acepta valores NULL automáticamente. Puede especificar NOT NULL solo para la documentación propia, pero si especifica la columna como NULL (lo que significa que acepta valores NULL), la instrucción de creación de tabla generará un error.
Solo una columna de una tabla puede tener establecida la propiedad IDENTITY; se usa con frecuencia como CLAVE PRINCIPAL o como una clave alternativa.
El código siguiente muestra la creación de la tabla Sales.Promotion usada en los ejemplos de la sección anterior, pero esta vez con una columna de identidad denominada PromotionID como clave principal:
CREATE TABLE Sales.Promotion
(
PromotionID int IDENTITY PRIMARY KEY,
PromotionName varchar(20),
StartDate datetime NOT NULL DEFAULT GETDATE(),
ProductModelID int NOT NULL REFERENCES Production.ProductModel(ProductModelID),
Discount decimal(4,2) NOT NULL,
Notes nvarchar(max) NULL
);
Nota
Los detalles completos de la instrucción CREATE TABLE quedan fuera del ámbito de este módulo.
Inserción de datos en una columna de identidad
Cuando se define la propiedad IDENTITY para una columna, las instrucciones INSERT de la tabla no suelen especificar ningún valor para la columna IDENTITY. El motor de base de datos genera un valor utilizando el siguiente valor disponible para la columna.
Por ejemplo, podría insertar una fila en la tabla Sales.Promotion sin especificar un valor para la columna PromotionID:
INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.10, '10% discount')
Tenga en cuenta que, aunque la cláusula VALUES no incluye un valor para la columna PromotionID, no es necesario especificar una lista de columnas en la cláusula INSERT: las columnas de identidad están exentas de este requisito.
Si esta fila es la primera insertada en la tabla, el resultado es una nueva fila como esta:
PromotionID
PromotionName
StartDate
ProductModelID
Descuento
Notas
1
Clearance Sale
2021-01-01T00:00:00
23
0,1
10% de descuento
Cuando se creó la tabla, no se establecieron los valores de inicialización o incremento para la columna IDENTITY, por lo que en la primera fila se inserta un valor de 1. A la siguiente fila que se va a insertar se le asignará un valor de 2 para PromotionID, y así sucesivamente.
Recuperación de un valor de identidad
Para devolver el último valor IDENTITY asignado dentro de la misma sesión y del mismo ámbito, use la función SCOPE_IDENTITY, como se muestra a continuación:
SELECT SCOPE_IDENTITY();
La función SCOPE_IDENTITY devuelve el valor de identidad más reciente generado en el ámbito actual de cualquier tabla. Si necesita el valor de identidad más reciente en una tabla específica, puede usar la función IDENT_CURRENT, como se muestra a continuación:
SELECT IDENT_CURRENT('Sales.Promotion');
Reemplazo de los valores de identidad
Si desea reemplazar el valor generado automáticamente y asignar un valor específico a la columna IDENTITY, primero debe habilitar las inserciones de identidad mediante la instrucción SET IDENTITY INSERT table_name ON. Con esta opción habilitada, puede insertar un valor explícito para la columna de identidad, al igual que cualquier otra columna. Cuando haya terminado, puede usar la instrucción ET IDENTITY INSERT table_name OFF para reanudar el uso de valores de identidad automáticos, utilizando el último valor que escribió explícitamente como una inicialización.
SET IDENTITY_INSERT SalesLT.Promotion ON;
INSERT INTO SalesLT.Promotion (PromotionID, PromotionName, ProductModelID, Discount)
VALUES
(20, 'Another short sale',37, 0.3);
SET IDENTITY_INSERT SalesLT.Promotion OFF;
Como ha aprendido, la propiedad IDENTITY se usa para generar una secuencia de valores para una columna dentro de una tabla. Sin embargo, la propiedad IDENTITY no es adecuada para coordinar valores entre varias tablas dentro de una base de datos. Por ejemplo, suponga que su organización diferencia entre las ventas directas y las ventas a revendedores, y quiere almacenar los datos de estas ventas en tablas independientes. Ambos tipos de ventas pueden necesitar un número de factura único, y es posible que quiera evitar duplicar el mismo valor para dos tipos de ventas diferentes. Una solución para este requisito es mantener un grupo de valores secuenciales únicos en ambas tablas.
Volver a inicializar una columna de identidad
En ocasiones, deberá restablecer u omitir los valores de identidad de la columna. Para ello, se "volverá a inicializar" la columna mediante la función DBCC CHECKIDENT. Puede usarlo para omitir muchos valores o restablecer el siguiente valor de identidad a 1 después de eliminar todas las filas de la tabla. Para obtener información completa sobre el uso de DBCC CHECKIDENT, consulte la documentación de referencia de Transact-SQL.
SEQUENCE
En Transact-SQL, puede usar un objeto de secuencia para definir nuevos valores secuenciales independientemente de una tabla específica. Un objeto de secuencia se crea mediante la instrucción CREATE SEQUENCE y, opcionalmente, proporcionando el tipo de datos (debe ser un tipo entero o decimal o numérico con una escala de 0), el valor inicial, un valor de incremento, un valor máximo y otras opciones relacionadas con el rendimiento.
CREATE SEQUENCE Sales.InvoiceNumber AS INT
START WITH 1000 INCREMENT BY 1;
Para recuperar el siguiente valor disponible de una secuencia, use la construcción NEXT VALUE FOR como se muestra a continuación:
INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);
IDENTITY o SEQUENCE
Al decidir si usar columnas IDENTITY o un objeto SEQUENCE para rellenar automáticamente los valores, tenga en cuenta los siguientes puntos:
Use SEQUENCE si la aplicación requiere compartir una serie única de números entre varias tablas o varias columnas de una tabla.
SEQUENCE permite ordenar los valores por otra columna. La construcción NEXT VALUE FOR puede usar la cláusula OVER para especificar la columna de ordenación. La cláusula OVER garantiza que los valores devueltos se generen en el orden de la cláusula OVER BY de la cláusula ORDER. Esta funcionalidad también permite generar números de fila para las filas a medida que se devuelven en una instrucción SELECT. En el ejemplo siguiente, la tabla Production.Product se ordena por la columna Name, y la primera columna devuelta es un número secuencial.
SELECT NEXT VALUE FOR dbo.Sequence OVER (ORDER BY Name) AS NextID, ProductID, Name FROM Production.Product;Aunque la instrucción anterior solo seleccionaba los valores SEQUENCE para mostrar, los valores se siguen "utilizando" y los valores SEQUENCE mostrados ya no estarán disponibles. Si ejecuta la instrucción SELECT anterior varias veces, obtendrá valores SEQUENCE diferentes cada vez.
Use SEQUENCE si la aplicación requiere que se asignen varios números al mismo tiempo. Por ejemplo, una aplicación necesita reservar cinco números secuenciales. Al solicitar los valores de identidad, podrían producirse lagunas en la serie si se emitieron números simultáneamente para otros procesos. Puede usar el procedimiento sp_sequence_get_range del sistema para recuperar varios números de la secuencia a la vez.
SEQUENCE permite cambiar la especificación de la secuencia, como, por ejemplo, el valor de incremento.
Los valores IDENTITY están protegidos de las actualizaciones. Si intenta actualizar una columna con la propiedad IDENTITY, se producirá un error.