Compartir a través de

Uso de IDENTITY para crear claves suplentes en un grupo de SQL dedicado

  • Artículo

En este artículo, encontrará recomendaciones y ejemplos para usar la propiedad IDENTITY para crear claves suplentes en tablas del grupo de SQL dedicado.

¿Qué es una clave suplente?

Una clave suplente en una tabla es una columna con un identificador único para cada fila. La clave no se genera a partir de los datos de la tabla. A los modeladores de datos les gusta crear claves suplentes en las tablas cuando diseñan modelos de almacenamiento de datos. Puede usar la propiedad IDENTITY para lograr este objetivo de forma sencilla y eficaz sin afectar al rendimiento de la carga.

Nota:

En Azure Synapse Analytics:

  • El valor IDENTITY aumenta por sí mismo en cada distribución y no se superpone con los valores IDENTITY de otras distribuciones. No se garantiza que el valor IDENTITY de Synapse sea único si el usuario inserta explícitamente un valor duplicado con SET IDENTITY_INSERT ON o vuelve a usar IDENTITY. Para detalles, consulte CREATE TABLE (Transact-SQL) IDENTITY (propiedad).
  • UPDATE en la columna de distribución no garantiza que el valor IDENTITY sea único. Utilice DBCC CHECKIDENT (Transact-SQL) después de ACTUALIZAR en la columna de distribución para verificar la unicidad.

Creación de una tabla con una columna IDENTITY

La propiedad IDENTITY está diseñada para escalar horizontalmente en todas las distribuciones del grupo de SQL dedicado sin afectar al rendimiento de la carga. Por lo tanto, la implementación de IDENTITY está orientada a lograr estos objetivos.

Puede definir una tabla como tener la propiedad IDENTITY al crear la tabla por primera vez mediante la sintaxis similar a la siguiente instrucción:

CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1) NOT NULL,
     C2 INT NULL
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

Luego puede usar INSERT..SELECT para rellenar la tabla.

En el resto de esta sección se resaltan los matices de la implementación para ayudarle a comprenderlos más completamente.

Asignación de valores

La propiedad IDENTITY no garantiza el orden en que se asignan los valores suplentes debido a la arquitectura distribuida del almacenamiento de datos. La propiedad IDENTITY está diseñada para escalar horizontalmente en todas las distribuciones del grupo de SQL dedicado sin afectar al rendimiento de la carga.

El ejemplo siguiente sirve de muestra:

CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1) NOT NULL,
     C2 VARCHAR(30) NULL
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

INSERT INTO dbo.T1
VALUES (NULL);

INSERT INTO dbo.T1
VALUES (NULL);

SELECT *
FROM dbo.T1;

DBCC PDW_SHOWSPACEUSED('dbo.T1');

En el ejemplo anterior, dos filas quedaron en la distribución 1. La primera fila tiene el valor suplente de 1 en la columna C1 y la segunda fila tiene el valor suplente de 61. Ambos valores se generaron mediante la propiedad IDENTITY. Sin embargo, la asignación de los valores no es contigua. Este comportamiento es así por diseño.

Datos sesgados

El intervalo de valores del tipo de datos se reparte uniformemente entre las distribuciones. Si una tabla distribuida adolece de datos sesgados, el intervalo de valores disponible para el tipo de datos se puede agotar antes de tiempo. Por ejemplo, si todos los datos terminan en una sola distribución, en la práctica, la tabla tiene acceso a únicamente la sexta parte de los valores del tipo de datos. Por este motivo, la propiedad IDENTITY se limita solo a los tipos de datos INT y BIGINT.

SELECT..INTO

Cuando se selecciona una columna IDENTITY existente en una nueva tabla, la nueva columna hereda la propiedad IDENTITY, a menos que se cumpla una de las condiciones siguientes:

  • La instrucción SELECT contiene una combinación.
  • Varias instrucciones SELECT se unen mediante UNION.
  • La columna IDENTITY aparece más de una vez en la lista de SELECT.
  • La columna IDENTITY forma parte de una expresión.

Si alguna de estas condiciones es true, la columna se crea NOT NULL en lugar de heredar la propiedad IDENTITY.

CREATE TABLE AS SELECT

CREATE TABLE AS SELECT (CTAS) sigue el mismo comportamiento de SQL Server que se documenta para SELECT..INTO. Sin embargo, no se puede especificar una propiedad IDENTITY en la definición de columna de la parte CREATE TABLE de la instrucción. Tampoco puede usar la función IDENTITY en la parte SELECT de CTAS. Para rellenar una tabla, hay que usar CREATE TABLE para definir la tabla seguido de INSERT..SELECT para rellenarla.

Insertar valores explícitos en una columna IDENTITY

El grupo de SQL dedicado admite la sintaxis SET IDENTITY_INSERT <your table> ON|OFF. Puede usar esta sintaxis para insertar explícitamente valores en la columna IDENTITY.

A muchos modeladores de datos les gusta usar valores negativos predefinidos para determinadas filas en las dimensiones. Un ejemplo es la fila -1 o miembro desconocido.

El siguiente script muestra cómo agregar explícitamente esta fila mediante SET IDENTITY_INSERT:

SET IDENTITY_INSERT dbo.T1 ON;

INSERT INTO dbo.T1
(   C1,
    C2
)
VALUES (-1,'UNKNOWN');

SET IDENTITY_INSERT dbo.T1 OFF;

SELECT     *
FROM    dbo.T1;

Cargar datos

La presencia de la propiedad IDENTITY tiene algunas implicaciones en el código de carga de datos. En esta sección se resaltan algunos patrones básicos para cargar datos en tablas mediante IDENTITY.

Para cargar datos en una tabla y generar una clave suplente mediante IDENTITY, cree la tabla y, a continuación, use INSERT..SELECT o INSERT..VALUES para realizar la carga.

En el siguiente ejemplo se resalta el patrón básico:

--CREATE TABLE with IDENTITY
CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1),
     C2 VARCHAR(30)
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

--Use INSERT..SELECT to populate the table from an external table
INSERT INTO dbo.T1
(C2)
SELECT     C2
FROM    ext.T1;

SELECT *
FROM   dbo.T1;

DBCC PDW_SHOWSPACEUSED('dbo.T1');

Nota:

No es posible usar CREATE TABLE AS SELECT actualmente al cargar datos en una tabla con una columna de IDENTITY.

Para más información sobre la carga de datos, consulte Diseño de un proceso de extracción, carga y transformación (ELT) para el grupo de SQL dedicado y Procedimientos recomendados para la carga de datos.

Vistas del sistema

Puede usar la vista de catálogo de sys.identity_columns para identificar una columna que tenga la propiedad IDENTITY.

Para ayudarle a comprender mejor el esquema de la base de datos, en este ejemplo se muestra cómo integrar sys.identity_columns con otras vistas de catálogo del sistema:

SELECT  sm.name
,       tb.name
,       co.name
,       CASE WHEN ic.column_id IS NOT NULL
             THEN 1
        ELSE 0
        END AS is_identity
FROM        sys.schemas AS sm
JOIN        sys.tables  AS tb           ON  sm.schema_id = tb.schema_id
JOIN        sys.columns AS co           ON  tb.object_id = co.object_id
LEFT JOIN   sys.identity_columns AS ic  ON  co.object_id = ic.object_id
                                        AND co.column_id = ic.column_id
WHERE   sm.name = 'dbo'
AND     tb.name = 'T1'
;

Limitaciones

No se puede usar la propiedad IDENTITY:

  • Cuando el tipo de datos de columna no se INT oBIGINT
  • Cuando la columna es también la clave de distribución
  • Cuando la tabla es una tabla externa

Las siguientes funciones relacionadas no se admiten en el grupo de SQL dedicado:

Tareas comunes

Puede usar el código de ejemplo siguiente para realizar tareas comunes al trabajar con IDENTITY columnas.

La columna C1 es el IDENTITY en todas las tareas siguientes.

Búsqueda del valor más alto asignado en una tabla

Use la función MAX() para determinar el valor más alto asignado en una tabla distribuida:

SELECT MAX(C1)
FROM dbo.T1

Búsqueda del valor de inicialización e incremento de la propiedad IDENTITY

Puede usar las vistas de catálogo para detectar los valores de configuración de inicialización e incremento de identidad en una tabla mediante la siguiente consulta:

SELECT  sm.name
,       tb.name
,       co.name
,       ic.seed_value
,       ic.increment_value
FROM        sys.schemas AS sm
JOIN        sys.tables  AS tb           ON  sm.schema_id = tb.schema_id
JOIN        sys.columns AS co           ON  tb.object_id = co.object_id
JOIN        sys.identity_columns AS ic  ON  co.object_id = ic.object_id
                                        AND co.column_id = ic.column_id
WHERE   sm.name = 'dbo'
AND     tb.name = 'T1'
;

Contenido relacionado