Creare chiavi surrogate con IDENTITYCreate surrogate keys by using IDENTITY

Molti progettisti di modelli di dati preferiscono creare chiavi surrogate sulle tabelle durante la progettazione dei modelli per i data warehouse.Many data modelers like to create surrogate keys on their tables when they design data warehouse models. È possibile usare la proprietà IDENTITY per raggiungere questo obiettivo in modo semplice ed efficace senza effetti sulle prestazioni di caricamento.You can use the IDENTITY property to achieve this goal simply and effectively without affecting load performance.

Introduzione a IDENTITYGet started with IDENTITY

È possibile definire una tabella con la proprietà IDENTITY al momento della creazione, usando una sintassi simile all'istruzione seguente:You can define a table as having the IDENTITY property when you first create the table by using syntax that is similar to the following statement:

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

È quindi possibile usare INSERT..SELECT per popolare la tabella.You can then use INSERT..SELECT to populate the table.

ComportamentoBehavior

La proprietà IDENTITY è progettata per supportare la scalabilità orizzontale tra tutte le distribuzioni nel data warehouse senza influenzare le prestazioni di caricamento.The IDENTITY property is designed to scale out across all the distributions in the data warehouse without affecting load performance. Pertanto, l'implementazione di IDENTITY è orientata al raggiungimento di questi obiettivi.Therefore, the implementation of IDENTITY is oriented toward achieving these goals. Questa sezione illustra le varie sfumature dell'implementazione per comprenderle appieno.This section highlights the nuances of the implementation to help you understand them more fully.

Allocazione dei valoriAllocation of values

La proprietà IDENTITY non garantisce l'ordine di allocazione dei valori surrogati, in modo conforme al comportamento di SQL Server e del database SQL di Azure.The IDENTITY property doesn't guarantee the order in which the surrogate values are allocated, which reflects the behavior of SQL Server and Azure SQL Database. Tuttavia, in Azure SQL Data Warehouse, l'assenza di una garanzia è più evidente.However, in Azure SQL Data Warehouse, the absence of a guarantee is more pronounced.

L'esempio seguente è una dimostrazione:The following example is an illustration:

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');

Nell'esempio precedente due righe raggiungono la distribuzione 1.In the preceding example, two rows landed in distribution 1. La prima riga ha il valore surrogato 1 nella colonna C1 e la seconda riga ha il valore surrogato 61.The first row has the surrogate value of 1 in column C1, and the second row has the surrogate value of 61. Entrambi questi valori sono stati generati dalla proprietà IDENTITY.Both of these values were generated by the IDENTITY property. L'allocazione dei valori non è tuttavia contigua.However, the allocation of the values is not contiguous. Questo comportamento dipende dalla progettazione.This behavior is by design.

Dati asimmetriciSkewed data

L'intervallo di valori per il tipo di dati è distribuito uniformemente tra le distribuzioni.The range of values for the data type are spread evenly across the distributions. Nel caso di una tabella distribuita con dati asimmetrici, l'intervallo di valori disponibili per il tipo di dati può esaurirsi in modo anomalo.If a distributed table suffers from skewed data, then the range of values available to the datatype can be exhausted prematurely. Ad esempio, se tutti i dati vengono destinati a una singola distribuzione, la tabella ha effettivamente accesso solo a un sessantesimo dei valori del tipo di dati.For example, if all the data ends up in a single distribution, then effectively the table has access to only one-sixtieth of the values of the data type. Per questo motivo, la proprietà IDENTITY è limitata solo ai tipi di dati INT e BIGINT.For this reason, the IDENTITY property is limited to INT and BIGINT data types only.

SELECT..INTOSELECT..INTO

Quando una colonna IDENTITY esistente viene selezionata in una nuova tabella, la nuova colonna eredita la proprietà IDENTITY, a meno che non sia vera una delle condizioni seguenti:When an existing IDENTITY column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

  • L'istruzione SELECT contiene un join.The SELECT statement contains a join.
  • Più istruzioni SELECT sono unite in join tramite l'istruzione UNION.Multiple SELECT statements are joined by using UNION.
  • La colonna IDENTITY è elencata più di una volta nell'elenco SELECT.The IDENTITY column is listed more than one time in the SELECT list.
  • La colonna IDENTITY fa parte di un'espressione.The IDENTITY column is part of an expression.

Se una di queste condizioni è vera, la colonna viene creata come NOT NULL invece di ereditare la proprietà IDENTITY.If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property.

CREATE TABLE AS SELECTCREATE TABLE AS SELECT

CREATE TABLE AS SELECT (CTAS) ha lo stesso comportamento di SQL Server documentato per SELECT... INTO.CREATE TABLE AS SELECT (CTAS) follows the same SQL Server behavior that's documented for SELECT..INTO. Tuttavia, non è possibile specificare una proprietà IDENTITY nella definizione di colonna della parte CREATE TABLE dell'istruzione.However, you can't specify an IDENTITY property in the column definition of the CREATE TABLE part of the statement. Non è possibile nemmeno usare la funzione IDENTITY nella parte SELECT dell'istruzione CTAS.You also can't use the IDENTITY function in the SELECT part of the CTAS. Per popolare una tabella, è necessario usare l'istruzione CREATE TABLE per definire la tabella, seguita da INSERT..SELECT per popolarla.To populate a table, you need to use CREATE TABLE to define the table followed by INSERT..SELECT to populate it.

Inserire in modo esplicito valori in una colonna IDENTITYExplicitly insert values into an IDENTITY column

SQL Data Warehouse supporta la sintassi SET IDENTITY_INSERT <your table> ON|OFF.SQL Data Warehouse supports SET IDENTITY_INSERT <your table> ON|OFF syntax. È possibile usare questa sintassi per inserire in modo esplicito i valori nella colonna IDENTITY.You can use this syntax to explicitly insert values into the IDENTITY column.

Molti progettisti di modelli di dati preferiscono usare valori negativi predefiniti per alcune righe nelle dimensioni.Many data modelers like to use predefined negative values for certain rows in their dimensions. Un esempio è la riga -1 o "membro sconosciuto".An example is the -1 or "unknown member" row.

Il prossimo script mostra come aggiungere in modo esplicito questa riga tramite SET IDENTITY_INSERT:The next script shows how to explicitly add this row by using 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
;

Caricare i dati in una tabella con IDENTITYLoad data into a table with IDENTITY

La presenza della proprietà IDENTITY ha alcune implicazioni per il codice di caricamento dei dati.The presence of the IDENTITY property has some implications to your data-loading code. Questa sezione evidenzia alcuni modelli di base per il caricamento di dati nelle tabelle usando IDENTITY.This section highlights some basic patterns for loading data into tables by using IDENTITY.

Caricare dati con PolyBaseLoad data with PolyBase

Per caricare dati in una tabella e generare una chiave surrogata con IDENTITY, creare la tabella e quindi usare INSERT... SELECT o INSERT... VALUES per eseguire il caricamento.To load data into a table and generate a surrogate key by using IDENTITY, create the table and then use INSERT..SELECT or INSERT..VALUES to perform the load.

L'esempio seguente illustra il modello di base:The following example highlights the basic pattern:

--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

Non è attualmente possibile usare CREATE TABLE AS SELECT per il caricamento di dati in una tabella con una colonna IDENTITY.It's not possible to use CREATE TABLE AS SELECT currently when loading data into a table with an IDENTITY column.

Per altre informazioni sul caricamento dei dati tramite lo strumento BCP (Bulk Copy Program), vedere gli articoli seguenti:For more information on loading data by using the bulk copy program (BCP) tool, see the following articles:

Caricare dati con BCPLoad data with BCP

BCP è uno strumento da riga di comando utilizzabile per caricare dati in SQL Data Warehouse.BCP is a command-line tool that you can use to load data into SQL Data Warehouse. Uno dei parametri di questo strumento (-E) controlla il comportamento di BCP quando si caricano dati in una tabella con una colonna IDENTITY.One of its parameters (-E) controls the behavior of BCP when loading data into a table with an IDENTITY column.

Quando viene specificato il parametro -E, vengono mantenuti i valori contenuti nel file di input per la colonna con IDENTITY.When -E is specified, the values held in the input file for the column with IDENTITY are retained. Se -E non viene specificato, i valori in questa colonna vengono ignorati.If -E is not specified, then the values in this column are ignored. Se la colonna IDENTITY non è inclusa, i dati vengono caricati come di consueto.If the identity column is not included, then the data is loaded as normal. I valori vengono generati in base al criterio di incremento e seeding della proprietà.The values are generated according to the increment and seed policy of the property.

Per altre informazioni sul caricamento di dati con BCP, vedere gli articoli seguenti:For more information on loading data by using BCP, see the following articles:

Viste del catalogoCatalog views

SQL Data Warehouse supporta la vista del catalogo sys.identity_columns.SQL Data Warehouse supports the sys.identity_columns catalog view. Questa vista è utilizzabile per identificare una colonna con la proprietà IDENTITY.This view can be used to identify a column that has the IDENTITY property.

Per comprendere meglio lo schema del database, questo esempio mostra come integrare sys.identity_columns con altre viste del catalogo di sistema:To help you better understand the database schema, this example shows how to integrate sys.identity_columns with other system catalog views:

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'
;

LimitazioniLimitations

La proprietà IDENTITY non può essere usata negli scenari seguenti:The IDENTITY property can't be used in the following scenarios:

  • Quando il tipo di dati della colonna non è INT o BIGINTWhere the column data type is not INT or BIGINT
  • Quando la colonna è anche la chiave di distribuzioneWhere the column is also the distribution key
  • Quando la tabella è una tabella esternaWhere the table is an external table

Le funzioni correlate seguenti non sono supportate in SQL Data Warehouse:The following related functions are not supported in SQL Data Warehouse:

AttivitàTasks

Questa sezione include esempi di codice utilizzabili per eseguire attività comuni quando si usano colonne IDENTITY.This section provides some sample code you can use to perform common tasks when you work with IDENTITY columns.

Nota

La colonna C1 è la colonna IDENTITY in tutte le attività seguenti.Column C1 is the IDENTITY in all the following tasks.

Individuare il valore massimo allocato per una tabellaFind the highest allocated value for a table

Usare la funzione MAX() per determinare il valore massimo allocato per una tabella distribuita:Use the MAX() function to determine the highest value allocated for a distributed table:

SELECT  MAX(C1)
FROM    dbo.T1

Trovare il valore di seeding e incremento per la proprietà IDENTITYFind the seed and increment for the IDENTITY property

È possibile usare le viste del catalogo per individuare i valori di configurazione di incremento e seeding di IDENTITY per una tabella usando la query seguente:You can use the catalog views to discover the identity increment and seed configuration values for a table by using the following query:

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'
;

Passaggi successiviNext steps