Talking About IDENTITY Columns

La proprietà IDENTITY viene spesso utilizzata per quelle tabelle che hanno la necessità di avere, per ogni riga, un valore univoco.

Il database engine ci solleva, così, dall’onere di verificare ed attribuire un valore che non sia già stato utilizzato.

Questa caratteristica può essere applicata solo su colonne (che siano non nullabili) di tipo:

• tinyint
• smallint
• int
• bigint
• decimal (con scala a 0)

Con altri tipi di dato sarebbe naturalmente impossibile definire questa proprietà:

 Identity column 'idRecord' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.

Ogni data type, è sempre bene ricordarsi, ha un numero finito di cifre disponibili.

Questo significa che è utile monitorare i valori utilizzati da queste colonne per evitare spiacevoli problemi (limite massimo raggiunto) che porterebbero ad errori simili a questo:

 Arithmetic overflow error converting IDENTITY to data type int.

Naturalmente l’idea, quando modelliamo le tabelle, è quella di scegliere il tipo di dato più appropriato che, per i nostri ragionamenti, significa scegliere un tipo intero che sia in grado di memorizzare tutte le righe che dovrò gestire.

 

L’idea potrebbe essere quindi quella di costruire una query che sia in grado di fornirci lo stato di ogni colonna impostata come IDENTITY.

Qualcosa come:

Questo lo script:

 ;WITH identityRange AS 
(
    SELECT
       'BIGINT' AS [name],
       9223372036854775807 AS MaxValue,
       -9223372036854775808 AS MinValue
    UNION
    SELECT
       'INT',
       2147483647,
       -2147483648
    UNION
    SELECT
       'SMALLINT',
       32767,
       -32768
    UNION
    SELECT
       'TINYINT',
       255,
       0
    UNION
    SELECT
       'DECIMAL',
       NULL,
       NULL
),
identityBuffer AS 
(
    SELECT
       QUOTENAME(OBJECT_SCHEMA_NAME(idCol.object_id)) + '.' +
       QUOTENAME(O.name) AS tableName,
       idCol.name AS columnName,
       CASE WHEN IR.name = 'DECIMAL' THEN ('DECIMAL(' +  
                (
                    SELECT CAST(NUMERIC_PRECISION AS VARCHAR(2)) FROM INFORMATION_SCHEMA.columns I 
                    WHERE 
                       I.TABLE_SCHEMA = OBJECT_SCHEMA_NAME(idCol.object_id) AND 
                       I.TABLE_NAME = O.name AND 
                       I.COLUMN_NAME = idCol.Name
                )
        + ')')
          ELSE UPPER(IR.name) END AS dataType,
       CAST(idCol.seed_value AS decimal(38, 0)) AS startingNumber,
       idCol.increment_value as incrementValue,
       CAST(idCol.last_value AS decimal(38, 0)) AS lastValueUsed,
       CAST(
          CASE WHEN IR.name = 'DECIMAL' THEN 
             REPLICATE('9', 
                    (
                    SELECT NUMERIC_PRECISION FROM INFORMATION_SCHEMA.columns I 
                    WHERE 
                       I.TABLE_SCHEMA = OBJECT_SCHEMA_NAME(idCol.object_id) AND 
                       I.TABLE_NAME = O.name AND 
                       I.COLUMN_NAME = idCol.Name)
                    )
          ELSE IR.MaxValue END AS decimal(38, 0)) -
          CAST(ISNULL(idCol.last_value, 0) AS decimal(38, 0)) AS remainingValues,
       CAST(CASE
             WHEN seed_value < 0
             THEN 
             (
                    CAST(
                CASE WHEN IR.name = 'DECIMAL' THEN 
                    REPLICATE('9', 
                          (
                          SELECT NUMERIC_PRECISION FROM INFORMATION_SCHEMA.columns I 
                          WHERE 
                             I.TABLE_SCHEMA = OBJECT_SCHEMA_NAME(idCol.object_id) AND 
                             I.TABLE_NAME = O.name AND 
                             I.COLUMN_NAME = idCol.Name)
                          )
                ELSE IR.MaxValue END AS decimal(38, 0))
             ) - 
             (
                    CAST(
                CASE WHEN IR.name = 'DECIMAL' THEN 
                    '-' + REPLICATE('9', 
                          (
                          SELECT NUMERIC_PRECISION FROM INFORMATION_SCHEMA.columns I 
                          WHERE 
                             I.TABLE_SCHEMA = OBJECT_SCHEMA_NAME(idCol.object_id) AND 
                             I.TABLE_NAME = O.name AND 
                             I.COLUMN_NAME = idCol.Name)
                          )
                ELSE IR.MinValue END AS decimal(38, 0))
             )
             ELSE 
             (
                    CAST(
          CASE WHEN IR.name = 'DECIMAL' THEN 
             REPLICATE('9', 
                    (
                    SELECT NUMERIC_PRECISION FROM INFORMATION_SCHEMA.columns I 
                    WHERE 
                       I.TABLE_SCHEMA = OBJECT_SCHEMA_NAME(idCol.object_id) AND 
                       I.TABLE_NAME = O.name AND 
                       I.COLUMN_NAME = idCol.Name)
                    )
          ELSE IR.MaxValue END AS decimal(38, 0))
             )
          END AS decimal(38, 0)) AS availableRange,
       (
          CAST(
             CASE WHEN IR.name = 'DECIMAL' THEN 
                REPLICATE('9', 
                       (
                       SELECT NUMERIC_PRECISION FROM INFORMATION_SCHEMA.columns I 
                       WHERE 
                          I.TABLE_SCHEMA = OBJECT_SCHEMA_NAME(idCol.object_id) AND 
                          I.TABLE_NAME = O.name AND 
                          I.COLUMN_NAME = idCol.Name)
                       )
             ELSE IR.MaxValue END AS decimal(38, 0))
       ) AS maxValueUsable
    FROM sys.identity_columns idCol 
    JOIN sys.types T ON idCol.system_type_id = T.system_type_id
    JOIN sys.objects O ON idCol.object_id = O.object_id
    JOIN identityRange IR ON T.name = IR.name
    WHERE 
       O.is_ms_shipped = 0
)
SELECT
    identityBuffer.tableName as [Table],
    identityBuffer.columnName as [Column],
    identityBuffer.dataType as [Data Type],
    identityBuffer.startingNumber as [Starting Number],
    identityBuffer.incrementValue as [Increment Value],
    identityBuffer.lastValueUsed as [Last Value Used],
    identityBuffer.maxValueUsable as [Max Value Usable],
    identityBuffer.remainingValues as [Remaining Values],
    CASE
        WHEN identityBuffer.startingNumber < 0
        THEN (-1 * identityBuffer.startingNumber + identityBuffer.lastValueUsed) / identityBuffer.availableRange
        ELSE (identityBuffer.lastValueUsed * 1.0) / identityBuffer.availableRange
    END AS [% Identity Value Consumption]
FROM
    identityBuffer
ORDER BY
    [Table], [Column]

Nota: il fatto che praticamente ogni editor definisca come numero di partenza il numero “1” non significa che questo sia (sempre) buono.

Partendo dall’estremo negativo (se esiste) raddoppiamo le possibilità (le righe).