ALTER TABLE (Transact-SQL)

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di analisi (PDW)Warehouse in Microsoft FabricDatabase SQL in Anteprima di Microsoft Fabric

Modifica una definizione di tabella tramite la modifica, l'aggiunta o l'eliminazione di colonne e vincoli. ALTER TABLE riassegna e ricompila anche le partizioni o disabilita e abilita vincoli e trigger.

La sintassi per ALTER TABLE è diversa per le tabelle basate su disco e le tabelle ottimizzate per la memoria. Usare i collegamenti seguenti per passare direttamente al blocco di sintassi appropriata per i tipi di tabella e agli esempi di sintassi appropriata:

Tabelle basate su disco:

• Syntax
• Examples

Tabelle ottimizzate per la memoria:

• Syntax
• Modifica delle tabelle di Memory-Optimized

Per altre informazioni sulle convenzioni della sintassi, vedere Convenzioni della sintassi Transact-SQL.

Sintassi per le tabelle basate su disco

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
{
    ALTER COLUMN column_name
    {
        [ type_schema_name. ] type_name
            [ (
                {
                   precision [ , scale ]
                 | max
                 | xml_schema_collection
                }
            ) ]
        [ COLLATE collation_name ]
        [ NULL | NOT NULL ] [ SPARSE ]
      | { ADD | DROP }
          { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE | HIDDEN }
      | { ADD | DROP } MASKED [ WITH ( FUNCTION = ' mask_function ') ]
    }
    [ WITH ( ONLINE = ON | OFF ) ]
    | [ WITH { CHECK | NOCHECK } ]

    | ADD
    {
        <column_definition>
      | <computed_column_definition>
      | <table_constraint>
      | <column_set_definition>
    } [ ,...n ]
      | [ system_start_time_column_name datetime2 GENERATED ALWAYS AS ROW START
                [ HIDDEN ] [ NOT NULL ] [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES] ,
                system_end_time_column_name datetime2 GENERATED ALWAYS AS ROW END
                   [ HIDDEN ] [ NOT NULL ][ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES] ,
                start_transaction_id_column_name bigint GENERATED ALWAYS AS TRANSACTION_ID START
                   [ HIDDEN ] NOT NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES],
                  end_transaction_id_column_name bigint GENERATED ALWAYS AS TRANSACTION_ID END
                   [ HIDDEN ] NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES],
                  start_sequence_number_column_name bigint GENERATED ALWAYS AS SEQUENCE_NUMBER START
                   [ HIDDEN ] NOT NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES],
                  end_sequence_number_column_name bigint GENERATED ALWAYS AS SEQUENCE_NUMBER END
                   [ HIDDEN ] NULL [ CONSTRAINT constraint_name ]
            DEFAULT constant_expression [WITH VALUES]
        ]
       PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )
    | DROP
     [ {
         [ CONSTRAINT ][ IF EXISTS ]
         {
              constraint_name
              [ WITH
               ( <drop_clustered_constraint_option> [ ,...n ] )
              ]
          } [ ,...n ]
          | COLUMN [ IF EXISTS ]
          {
              column_name
          } [ ,...n ]
          | PERIOD FOR SYSTEM_TIME
     } [ ,...n ] ]
    | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT
        { ALL | constraint_name [ ,...n ] }

    | { ENABLE | DISABLE } TRIGGER
        { ALL | trigger_name [ ,...n ] }

    | { ENABLE | DISABLE } CHANGE_TRACKING
        [ WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } ) ]

    | SWITCH [ PARTITION source_partition_number_expression ]
        TO target_table
        [ PARTITION target_partition_number_expression ]
        [ WITH ( <low_priority_lock_wait> ) ]

    | SET
        (
            [ FILESTREAM_ON =
                { partition_scheme_name | filegroup | "default" | "NULL" } ]
            | SYSTEM_VERSIONING =
                  {
                    OFF
                  | ON
                      [ ( HISTORY_TABLE = schema_name . history_table_name
                          [, DATA_CONSISTENCY_CHECK = { ON | OFF } ]
                          [, HISTORY_RETENTION_PERIOD =
                          {
                              INFINITE | number {DAY | DAYS | WEEK | WEEKS
                  | MONTH | MONTHS | YEAR | YEARS }
                          }
                          ]
                        )
                      ]
                  }
            | DATA_DELETION =
                {
                      OFF
                    | ON
                        [(  [ FILTER_COLUMN = column_name ]
                            [, RETENTION_PERIOD = { INFINITE | number { DAY | DAYS | WEEK | WEEKS
                                    | MONTH | MONTHS | YEAR | YEARS } } ]
                        )]
                    } )
    | REBUILD
      [ [PARTITION = ALL]
        [ WITH ( <rebuild_option> [ ,...n ] ) ]
      | [ PARTITION = partition_number
           [ WITH ( <single_partition_rebuild_option> [ ,...n ] ) ]
        ]
      ]

    | <table_option>
    | <filetable_option>
    | <stretch_configuration>
}
[ ; ]

-- ALTER TABLE options

<column_set_definition> ::=
    column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

<drop_clustered_constraint_option> ::=
    {
        MAXDOP = max_degree_of_parallelism
      | ONLINE = { ON | OFF }
      | MOVE TO
         { partition_scheme_name ( column_name ) | filegroup | "default" }
    }
<table_option> ::=
    {
        SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )
    }

<filetable_option> ::=
    {
       [ { ENABLE | DISABLE } FILETABLE_NAMESPACE ]
       [ SET ( FILETABLE_DIRECTORY = directory_name ) ]
    }

<stretch_configuration> ::=
    {
      SET (
        REMOTE_DATA_ARCHIVE
        {
            = ON (<table_stretch_options>)
          | = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED )
          | ( <table_stretch_options> [, ...n] )
        }
            )
    }

<table_stretch_options> ::=
    {
     [ FILTER_PREDICATE = { null | table_predicate_function } , ]
       MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }
    }

<single_partition_rebuild__option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE}
    | ONLINE = { ON [( <low_priority_lock_wait> ) ] | OFF }
}

<low_priority_lock_wait>::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ],
        ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

Per altre informazioni, vedi:

• ALTER TABLE column_constraint
• ALTER TABLE column_definition
• ALTER TABLE computed_column_definition
• ALTER TABLE index_option
• ALTER TABLE table_constraint

Sintassi per le tabelle con ottimizzazione per la memoria

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
{
    ALTER COLUMN column_name
    {
        [ type_schema_name. ] type_name
            [ (
                {
                   precision [ , scale ]
                }
            ) ]
        [ COLLATE collation_name ]
        [ NULL | NOT NULL ]
    }

    | ALTER INDEX index_name
    {
        [ type_schema_name. ] type_name
        REBUILD
        [ [ NONCLUSTERED ] WITH ( BUCKET_COUNT = bucket_count )
        ]
    }

    | ADD
    {
        <column_definition>
      | <computed_column_definition>
      | <table_constraint>
      | <table_index>
      | <column_index>
    } [ ,...n ]

    | DROP
     [ {
         CONSTRAINT [ IF EXISTS ]
         {
              constraint_name
          } [ ,...n ]
        | INDEX [ IF EXISTS ]
      {
         index_name
       } [ ,...n ]
          | COLUMN [ IF EXISTS ]
          {
              column_name
          } [ ,...n ]
          | PERIOD FOR SYSTEM_TIME
     } [ ,...n ] ]
    | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT
        { ALL | constraint_name [ ,...n ] }

    | { ENABLE | DISABLE } TRIGGER
        { ALL | trigger_name [ ,...n ] }

    | SWITCH [ [ PARTITION ] source_partition_number_expression ]
        TO target_table
        [ PARTITION target_partition_number_expression ]
        [ WITH ( <low_priority_lock_wait> ) ]

}
[ ; ]

-- ALTER TABLE options

< table_constraint > ::=
 [ CONSTRAINT constraint_name ]
{
   {PRIMARY KEY | UNIQUE }
     {
       NONCLUSTERED (column [ ASC | DESC ] [ ,... n ])
       | NONCLUSTERED HASH (column [ ,... n ] ) WITH ( BUCKET_COUNT = bucket_count )
     }
    | FOREIGN KEY
        ( column [ ,...n ] )
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
    | CHECK ( logical_expression )
}

<column_index> ::=
  INDEX index_name
{ [ NONCLUSTERED ] | [ NONCLUSTERED ] HASH WITH (BUCKET_COUNT = bucket_count) }

<table_index> ::=
  INDEX index_name
{ [ NONCLUSTERED ] HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count)
  | [ NONCLUSTERED ] (column [ ASC | DESC ] [ ,... n ] )
      [ ON filegroup_name | default ]
  | CLUSTERED COLUMNSTORE [ WITH ( COMPRESSION_DELAY = { 0 | delay [MINUTES] } ) ]
      [ ON filegroup_name | default ]
}

Sintassi per Azure Synapse Analytics e Parallel Data Warehouse

ALTER TABLE { database_name.schema_name.source_table_name | schema_name.source_table_name | source_table_name }
{
    ALTER COLUMN column_name
        {
            type_name [ ( precision [ , scale ] ) ]
            [ COLLATE Windows_collation_name ]
            [ NULL | NOT NULL ]
        }
    | ADD { <column_definition> | <column_constraint> FOR column_name} [ ,...n ]
    | DROP { COLUMN column_name | [CONSTRAINT] constraint_name } [ ,...n ]
    | REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_option> ] ]
      }
    | { SPLIT | MERGE } RANGE (boundary_value)
    | SWITCH [ PARTITION source_partition_number
        TO target_table_name [ PARTITION target_partition_number ] [ WITH ( TRUNCATE_TARGET = ON | OFF ) ] ]
}
[ ; ]

<column_definition>::=
{
    column_name
    type_name [ ( precision [ , scale ] ) ]
    [ <column_constraint> ]
    [ COLLATE Windows_collation_name ]
    [ NULL | NOT NULL ]
}

<column_constraint>::=
    [ CONSTRAINT constraint_name ]
    {
        DEFAULT constant_expression
        | PRIMARY KEY NONCLUSTERED (column_name [ ,... n ]) NOT ENFORCED -- Applies to Azure Synapse Analytics only
        | UNIQUE (column_name [ ,... n ]) NOT ENFORCED -- Applies to Azure Synapse Analytics only
    }
<rebuild_option > ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
}

<single_partition_rebuild_option > ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
}

Sintassi per warehouse in infrastruttura

ALTER TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
{
  ADD  { column_name <data_type> [COLLATE collation_name] [ <column_options> ] } [ ,...n ]
| ADD { <column_constraint> FOR column_name} [ ,...n ]
| DROP { COLUMN column_name | [CONSTRAINT] constraint_name } [ ,...n ]
}
[ ; ]

<column_options> ::=
[ NULL ] -- default is NULL

<data type> ::=
datetime2 ( n )
| date
| time ( n )
| float [ ( n ) ]
| real [ ( n ) ]
| decimal [ ( precision [ , scale ] ) ]
| numeric [ ( precision [ , scale ] ) ]
| bigint
| int
| smallint
| bit
| varchar [ ( n ) ]
| char [ ( n ) ]
| varbinary [ ( n ) ]
| uniqueidentifier

<column_constraint>::=
    [ CONSTRAINT constraint_name ]
    {
       PRIMARY KEY NONCLUSTERED (column_name [ ,... n ]) NOT ENFORCED
        | UNIQUE NONCLUSTERED (column_name [ ,... n ]) NOT ENFORCED
    | FOREIGN KEY
        ( column [ ,...n ] )
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ] NOT ENFORCED
    }

Arguments

database_name

Nome del database in cui è stata creata la tabella.

schema_name

Nome del processo a cui appartiene la tabella.

table_name

Nome della tabella da modificare. Se la tabella non è inclusa nel database corrente o nello schema di proprietà dell'utente corrente, è necessario specificare in modo esplicito il database e lo schema.

ALTER COLUMN

Specifica che la colonna denominata deve essere cambiata o modificata.

Non è consentita la modifica di queste colonne:

• Colonna con tipo di dati timestamp.

• Oggetto ROWGUIDCOL per la tabella.

• Colonne calcolate o utilizzate in una colonna calcolata.

• Utilizzato nelle statistiche generate dall'istruzione CREATE STATISTICS . Gli utenti devono eseguire DROP STATISTICS per eliminare le statistiche prima ALTER COLUMN di poter avere esito positivo. Eseguire questa query per ottenere tutte le statistiche e le colonne delle statistiche create dall'utente per una tabella.

  SELECT s.name AS statistics_name,
         c.name AS column_name,
         sc.stats_column_id
  FROM sys.stats AS s
       INNER JOIN sys.stats_columns AS sc
           ON s.object_id = sc.object_id
          AND s.stats_id = sc.stats_id
       INNER JOIN sys.columns AS c
           ON sc.object_id = c.object_id
          AND c.column_id = sc.column_id
  WHERE s.object_id = OBJECT_ID('<table_name>');
  

  Note

  Le statistiche generate automaticamente da Query Optimizer vengono eliminate automaticamente da ALTER COLUMN.

• Utilizzato in un PRIMARY KEY vincolo o [FOREIGN KEY] REFERENCES .

• Utilizzato in un CHECK vincolo o UNIQUE . Tuttavia, la modifica della lunghezza di una colonna a lunghezza variabile usata in un CHECK vincolo o UNIQUE è consentita.

• Colonne associate a una definizione DEFAULT. Se il tipo di dati non viene modificato, è tuttavia possibile modificare la lunghezza, la precisione o la scala di una colonna.

Il tipo di dati di colonne text, ntext e image può essere modificato solo nei modi seguenti:

• Da text a varchar(max), nvarchar(max) o xml
• Da ntext a varchar(max), nvarchar(max) o xml
• Da image a varbinary(max)

Alcune modifiche al tipo di dati potrebbero causare una modifica dei dati. Ad esempio, la modifica di una colonna nchar o nvarchar in char o varchar potrebbe causare la conversione di caratteri estesi. Per altre informazioni, vedere CAST e CONVERT. La riduzione della precisione o della scala di una colonna può causare il troncamento dei dati.

Quando si usa Always Encrypted (senza enclave sicuri), se la colonna da modificare viene crittografata con ENCRYPTED WITH, è possibile modificare il tipo di dati in un tipo di dati compatibile ,ad esempio INT in BIGINT, ma non è possibile modificare le impostazioni di crittografia.

Quando si usa Always Encrypted con enclave sicuri, è possibile modificare qualsiasi impostazione di crittografia, se la chiave di crittografia che protegge la colonna (e la nuova chiave di crittografia della colonna, se si modifica la chiave) supporta i calcoli dell'enclave (crittografati con le chiavi master della colonna abilitate per le enclave). Per informazioni dettagliate, vedere Always Encrypted con enclave sicuri.

Quando si modifica una colonna, il motore di database tiene traccia di ogni modifica aggiungendo una riga in una tabella di sistema e contrassegnando la modifica della colonna precedente come colonna eliminata. Nel raro caso in cui si modifica una colonna troppe volte, il motore di database potrebbe raggiungere il limite di dimensioni del record. In questo caso, viene visualizzato l'errore 511 o 1708. Per evitare questi errori, ricompilare periodicamente l'indice cluster nella tabella o ridurre il numero di modifiche alle colonne.

column_name

Nome della colonna da modificare, aggiungere o eliminare. column_name può essere composto da un massimo di 128 caratteri. Nel caso di nuove colonne, è possibile omettere column_name per le colonne che sono state create con il tipo di dati timestamp. Viene usato il nome timestamp se non si specifica il nome column_name per una colonna con il tipo di dati timestamp.

[ type_schema_name. ] type_name

Nuovo tipo di dati per la colonna modificata o tipo di dati per la colonna aggiunta. Non è possibile specificare type_name per colonne esistenti di tabelle partizionate. type_name può essere uno dei tipi seguenti:

• Tipo di dati di sistema di SQL Server.
• Tipo di dati alias basato su un tipo di dati di sistema di SQL Server. È possibile creare tipi di dati alias con l'istruzione CREATE TYPE prima di poterli usare in una definizione di tabella.
• Tipo definito dall'utente di .NET Framework e schema di appartenenza. È possibile creare tipi definiti dall'utente con l'istruzione CREATE TYPE prima di poterli usare in una definizione di tabella.

Di seguito sono riportati i criteri per type_name di una colonna modificata:

• Il tipo di dati precedente deve supportare la conversione implicita nel nuovo tipo di dati.
• type_name non può essere timestamp.
• ANSI_NULL le impostazioni predefinite sono sempre attivate per ALTER COLUMN; se non specificato, la colonna è nullable.
• ANSI_PADDING la spaziatura interna è sempre ON per ALTER COLUMN.
• Se la colonna modificata è una colonna Identity, il tipo di dati di new_data_type deve supportare la proprietà Identity.
• L'impostazione corrente per SET ARITHABORT viene ignorata. ALTER TABLE funziona come se ARITHABORT fosse impostato su ON.

precision

Precisione del tipo di dati specificato. Per altre informazioni sui valori di precisione validi, vedere Precisione, scala e lunghezza.

scale

Scala per il tipo di dati specificato. Per altre informazioni sui valori di scala validi, vedere Precisione, scala e lunghezza.

max

Viene applicato solo ai tipi di dati varchar, nvarchar e varbinary per l'archiviazione di 2^31-1 byte di dati di tipo carattere, binario e Unicode.

xml_schema_collection

Si applica a: SQL Server e database SQL di Azure.

Viene applicato solo al tipo di dati xml per l'associazione di uno XML Schema al tipo. Prima di tipizzare una colonna xml in una raccolta di schemi, si crea la raccolta nel database usando CREATE XML SCHEMA COLLECTION.

COLLATE <collation_name>

Specifica le nuove regole di confronto per la colonna modificata. Se viene omesso, alla colonna vengono assegnate le regole di confronto predefinite del database. È possibile usare nomi di regole di confronto di Windows o SQL. Per un elenco e altre informazioni, vedere Nome delle regole di confronto di Windows e Nome delle regole di confronto di SQL Server.

La COLLATE clausola modifica le regole di confronto solo delle colonne dei tipi di dati char, varchar, nchar e nvarchar . Per modificare le regole di confronto di una colonna del tipo di dati alias definito dall'utente, usare istruzioni separate ALTER TABLE per modificare la colonna in un tipo di dati di sistema di SQL Server. Modificare quindi le regole di confronto e ripristinare un tipo di dati alias per la colonna.

ALTER COLUMN non può avere una modifica delle regole di confronto se esistono una o più delle condizioni seguenti:

• Un CHECK vincolo, FOREIGN KEY un vincolo o colonne calcolate fa riferimento alla colonna modificata.
• Qualsiasi indice, statistiche o indice full-text viene creato nella colonna . Le statistiche create automaticamente nella colonna modificata vengono eliminate se si modificano le regole di confronto della colonna.
• Una vista o una funzione associata a schema fa riferimento alla colonna.

Per altre informazioni sulle regole di confronto supportate, vedere COLLATE.

NULL | NOT NULL

Specifica se la colonna consente valori Null. Le colonne che non consentono valori Null vengono aggiunte solo ALTER TABLE se hanno un valore predefinito specificato o se la tabella è vuota. È possibile specificare NOT NULL per le colonne calcolate solo se è stato specificato PERSISTEDanche . Le nuove colonne che consentono valori Null ma a cui non è associato alcun valore predefinito contengono un valore Null per ogni riga della tabella. Se la nuova colonna consente valori Null e si aggiunge una definizione predefinita con la nuova colonna, è possibile usare WITH VALUES per archiviare il valore predefinito nella nuova colonna per ogni riga esistente della tabella.

Se la nuova colonna non consente valori Null e la tabella non è vuota, è necessario aggiungere una DEFAULT definizione con la nuova colonna. Il valore predefinito viene quindi caricato automaticamente in ogni riga esistente delle nuove colonne.

È possibile specificare in ALTER COLUMN per forzare NULL una NOT NULL colonna per consentire valori Null, ad eccezione delle colonne nei PRIMARY KEY vincoli. È possibile specificare NOT NULL in ALTER COLUMN solo se la colonna non contiene valori Null. I valori Null devono essere aggiornati a un valore prima ALTER COLUMNNOT NULL che sia consentito, ad esempio:

UPDATE MyTable SET NullCol = N'some_value' WHERE NullCol IS NULL;

ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR (20) NOT NULL;

Quando si crea o si modifica una tabella con le CREATE TABLE istruzioni o ALTER TABLE , le impostazioni del database e della sessione influiscono ed eventualmente eseguono l'override del valore Nullbility del tipo di dati usato in una definizione di colonna. Assicurarsi di definire sempre in modo esplicito una colonna come NULL o NOT NULL per le colonne non calcolate.

Se si aggiunge una colonna con un tipo di dati definito dall'utente, assicurarsi di definire per la colonna la stessa impostazione relativa al supporto dei valori Null del tipo di dati definito dall'utente. Specificare quindi un valore predefinito per la colonna. Per altre informazioni, vedere CREATE TABLE.

[ {ADD | DROP} ROWGUIDCOL ]

Si applica a: SQL Server e database SQL di Azure.

Specifica che la ROWGUIDCOL proprietà viene aggiunta o eliminata dalla colonna specificata. ROWGUIDCOL indica che la colonna è una colonna GUID di riga. È possibile impostare una sola colonna uniqueidentifier per tabella come ROWGUIDCOL colonna. Inoltre, è possibile assegnare la ROWGUIDCOL proprietà solo a una colonna uniqueidentifier . Non è possibile assegnare ROWGUIDCOL a una colonna di un tipo di dati definito dall'utente.

ROWGUIDCOL non impone l'univocità dei valori archiviati nella colonna e non genera automaticamente valori per le nuove righe inserite nella tabella. Per generare valori univoci per ogni colonna, usare la funzione NEWID() o NEWSEQUENTIALID() nelle istruzioni INSERT. In alternativa, specificare la funzione NEWID() o NEWSEQUENTIALID() come predefinita per la colonna.

[ {ADD | DROP} PERSISTED ]

Specifica che la PERSISTED proprietà viene aggiunta o eliminata dalla colonna specificata. La colonna interessata deve essere una colonna calcolata definita con un'espressione deterministica. Per le colonne specificate come PERSISTED, il motore di database archivia fisicamente i valori calcolati nella tabella e aggiorna i valori quando vengono aggiornate le altre colonne su cui dipende la colonna calcolata. Contrassegnando una colonna calcolata come PERSISTED, è possibile creare indici su colonne calcolate definite in espressioni deterministiche, ma non precise. Per altre informazioni, vedere Indici sulle colonne calcolate.

SET QUOTED_IDENTIFIER deve essere ON quando si creano o si modificano indici in colonne calcolate o viste indicizzate. Per altre informazioni, vedere SET QUOTED_IDENTIFIER.

Qualsiasi colonna calcolata usata come colonna di partizionamento di una tabella partizionata deve essere contrassegnata in modo esplicito.PERSISTED

DROP NOT FOR REPLICATION

Si applica a: SQL Server e database SQL di Azure.

Specifica che i valori vengono incrementati nelle colonne Identity quando gli agenti di replica eseguono operazioni di inserimento. È possibile specificare questa clausola solo se column_name corrisponde a una colonna Identity.

SPARSE

Indica che la colonna è di tipo sparse. L'archiviazione delle colonne di tipo sparse è ottimizzata per valori Null. Non è possibile impostare colonne di tipo sparse come NOT NULL. Quando si converte una colonna da sparse a nonsparse o da nonsparse a sparse, questa opzione blocca la tabella per la durata dell'esecuzione del comando. Potrebbe essere necessario usare la REBUILD clausola per recuperare eventuali risparmi di spazio. Per altre restrizioni e altre informazioni sulle colonne di tipo sparse, vedere Usare colonne di tipo sparse.

ADD MASKED WITH ( FUNCTION = 'mask_function')

Si applica a: SQL Server 2016 (13.x) e versioni successive e database SQL di Azure.

Specifica una maschera dati dinamica. mask_function è il nome della funzione di maschera con i parametri appropriati. Sono disponibili tre funzioni:

• default()
• email()
• partial()
• random()

È richiesta ALTER ANY MASK l'autorizzazione.

Per eliminare una maschera, usare DROP MASKED. Per i parametri della funzione, vedere Maschera dati dinamica.

Aggiungere e rilasciare una maschera richiedono ALTER ANY MASK l'autorizzazione.

WITH ( ONLINE = ON | OFF) <come si applica per modificare una colonna>

Si applica a: SQL Server 2016 (13.x) e versioni successive e database SQL di Azure.

Consente l'esecuzione di molte azioni di modifica colonna mentre la tabella rimane disponibile. Il valore predefinito è OFF. È possibile eseguire l'operazione di modifica colonna online per le modifiche relative al tipo di dati, alla lunghezza o precisione della colonna, al supporto dei valori Null, all'impostazione del tipo sparse e alle regole di confronto.

La colonna di modifica online consente all'utente di creare e statistiche automatiche di fare riferimento alla colonna modificata per la durata dell'operazione, consentendo l'esecuzione ALTER COLUMN delle query come di consueto. Al termine dell'operazione, le statistiche automatiche che fanno riferimento alla colonna vengono eliminate e le statistiche create dall'utente vengono invalidate. L'utente deve aggiornare manualmente le statistiche generate dall'utente dopo il completamento dell'operazione. Se la colonna fa parte di un'espressione filtro per statistiche o indici, non è possibile eseguire un'operazione di modifica colonna.

• Durante l'esecuzione dell'operazione di modifica colonna online, qualsiasi operazione DDL che potrebbe dipendere da tale colonna , ad esempio la creazione o la modifica di indici, viste e così via, viene bloccata o ha esito negativo con un errore appropriato. Questo comportamento garantisce che l'operazione di modifica colonna online non avrà esito negativo a causa delle dipendenze introdotte durante l'esecuzione dell'operazione.

• La modifica di una colonna da NOT NULL a NULL non è supportata come operazione online quando viene fatto riferimento alla colonna modificata da indici non cluster.

• Online ALTER non è supportato quando la colonna fa riferimento a un vincolo CHECK e l'operazione ALTER limita la precisione della colonna (numerica o datetime).

• L'opzione WAIT_AT_LOW_PRIORITY non può essere usata con l'operazione di modifica colonna online.

• ALTER COLUMN ... ADD/DROP PERSISTED non è supportato per l'operazione di modifica colonna online.

• ALTER COLUMN ... ADD/DROP ROWGUIDCOL/NOT FOR REPLICATION non è interessato dall'operazione di modifica colonna online.

• L'operazione di modifica colonna online non supporta la modifica di una tabella in cui è abilitato il rilevamento modifiche o che è una tabella di pubblicazione della replica di tipo merge.

• L'operazione di modifica colonna online non supporta la modifica dai tipi di dati CLR o viceversa.

• L'operazione di modifica colonna online non supporta la modifica in un tipo di dati XML con una raccolta di schemi diversa dalla raccolta di schemi corrente.

• L'operazione di modifica colonna online non consente di ridurre le restrizioni che stabiliscono quando una colonna può essere modificata. Riferimenti da indici/statistiche e così via potrebbero causare l'esito negativo dell'operazione di modifica.

• L'operazione di modifica colonna online non supporta la modifica di più colonne contemporaneamente.

• L'operazione di modifica colonna online non influisce in caso di una tabella temporale con controllo delle versioni di sistema. ALTER la colonna non viene eseguita come online indipendentemente dal valore specificato per ONLINE l'opzione.

L'operazione di modifica colonna online prevede requisiti, restrizioni e funzionalità simili a quelli dell'operazione di ricompilazione indice online, ad esempio:

• L'operazione di ricompilazione indice online non è supportata quando la tabella contiene colonne LOB o filestream legacy oppure quando la tabella include un indice columnstore. Le stesse limitazioni si applicano all'operazione di modifica colonna online.
• La modifica di una colonna esistente richiede un'allocazione dello spazio doppia: per la colonna originale e per la colonna nascosta appena creata.
• La strategia di blocco durante un'operazione di modifica colonna online segue lo stesso criterio di blocco usato per l'operazione di compilazione indice online.

WITH CHECK | WITH NOCHECK

Specifica se i dati nella tabella sono o non vengono convalidati rispetto a un vincolo o appena aggiunto o CHECK riabilitatoFOREIGN KEY. Se non si specifica, WITH CHECK viene assunto per i nuovi vincoli e WITH NOCHECK viene utilizzato per i vincoli riabilitati.

Se non si desidera verificare nuovi CHECK vincoli o FOREIGN KEY rispetto ai dati esistenti, usare WITH NOCHECK. È tuttavia consigliabile effettuare questa scelta solo in casi rari. Il nuovo vincolo viene valutato in tutti gli aggiornamenti successivi dei dati. Eventuali violazioni dei vincoli eliminate da WITH NOCHECK quando viene aggiunto il vincolo potrebbero causare l'esito negativo degli aggiornamenti futuri se aggiornano le righe con dati che non seguono il vincolo. Query Optimizer non considera i vincoli definiti WITH NOCHECK. Tali vincoli vengono ignorati fino a quando non vengono abilitati usando ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL. Per altre informazioni, vedere Disabilitare i vincoli di chiave esterna con istruzioni INSERT e UPDATE.

ALTER INDEX index_name

Specifica che il numero di bucket per index_name deve essere cambiato o modificato.

La sintassi ALTER TABLE ... ADD/DROP/ALTER INDEX è supportata solo per le tabelle ottimizzate per la memoria.

ADD

Specifica l'aggiunta di una o più definizioni di colonna, definizioni di colonna calcolata o vincoli di tabella. In alternativa, specifica l'aggiunta delle colonne che il sistema usa per il controllo delle versioni di sistema. Per le tabelle ottimizzate per la memoria, è possibile aggiungere un indice.

PERIOD FOR SYSTEM_TIME ( system_start_time_column_name, system_end_time_column_name )

Si applica a: SQL Server 2017 (14.x) e versioni successive e database SQL di Azure.

Specifica i nomi delle colonne che il sistema usa per registrare il periodo di validità di un record. È possibile specificare colonne esistenti o creare nuove colonne come parte dell'argomento ADD PERIOD FOR SYSTEM_TIME . Configurare le colonne con il tipo di dati datetime2 e definirle come NOT NULL. Se si definisce una colonna punto come NULL, viene restituito un errore. È possibile definire un column_constraint e/o Specificare i valori predefiniti per le colonne system_start_time e system_end_time. Vedere l'esempio A negli esempi di Controllo delle versioni di sistema seguenti, che illustrano l'uso di un valore predefinito per la colonna system_end_time.

Utilizzare questo argomento con l'argomento SET SYSTEM_VERSIONING per creare una tabella temporale esistente. Per altre informazioni, vedere Tabelle temporali e Introduzione alle tabelle temporali.

A partire da SQL Server 2017 (14.x), gli utenti possono contrassegnare una o entrambe le colonne period con HIDDEN flag per nascondere in modo implicito queste colonne in modo che SELECT * FROM <table_name> non restituiscano un valore per le colonne. Per impostazione predefinita, le colonne periodo non vengono nascoste. Per poter essere usate, le colonne nascoste devono essere incluse in modo esplicito in tutte le query che fanno direttamente riferimento alla tabella temporale.

DROP

Specifica la rimozione di una o più definizioni di colonna, definizioni di colonna calcolata o vincoli di tabella o l'eliminazione della specifica per le colonne che il sistema usa per il controllo delle versioni di sistema.

CONSTRAINT constraint_name

Specifica che constraint_name viene rimosso dalla tabella. È possibile elencare più vincoli.

È possibile determinare il nome definito dall'utente o fornito dal sistema del vincolo eseguendo una query sulle sys.check_constraintviste del catalogo , sys.default_constraints, sys.key_constraintse sys.foreign_keys .

Non è possibile eliminare un PRIMARY KEY vincolo se esiste un indice XML nella tabella.

INDEX index_name

Specifica che index_name viene rimosso dalla tabella.

La sintassi ALTER TABLE ...ALTER INDEXADD/DROP/è supportata solo per le tabelle ottimizzate per la memoria.

COLUMN column_name

Specifica che constraint_name o column_name viene rimosso dalla tabella. È possibile elencare più colonne.

Non è possibile eliminare una colonna se:

• Usato in un indice, sia come colonna chiave che come colonna INCLUDE
• Utilizzato in un CHECKvincolo , FOREIGN KEY, UNIQUEo PRIMARY KEY .
• Associato a un valore predefinito definito con la DEFAULT parola chiave o associato a un oggetto predefinito.
• È associata a una regola.

PERIOD FOR SYSTEM_TIME

Si applica a: SQL Server 2016 (13.x) e versioni successive e database SQL di Azure.

Elimina la specifica per le colonne usate dal sistema per il controllo delle versioni del sistema.

WITH <drop_clustered_constraint_option>

Specifica l'impostazione di una o più opzioni di eliminazione dei vincoli cluster.

MAXDOP = max_degree_of_parallelism

Si applica a: SQL Server e database SQL di Azure.

Esegue l'override dell'opzione di configurazione max degree of parallelism solo per la durata dell'operazione. Per altre informazioni, vedere Configurazione del server: max degree of parallelism.

Usare l'opzione MAXDOP per limitare il numero di processori usati nell'esecuzione di piani paralleli. Il valore massimo è 64 processori.

max_degree_of_parallelism può essere uno dei valori seguenti:

• 1

  Disattiva la generazione di piani paralleli.

• >1

  Limita il numero massimo di processori utilizzati in un'operazione parallela sull'indice in base al numero specificato.

• 0 (impostazione predefinita)

  Usa il numero effettivo di processori o meno in base al carico di lavoro del sistema corrente.

Per altre informazioni, vedere Configurazione di operazioni parallele sugli indici.

ONLINE = { ON | OFF } <come si applica a drop_clustered_constraint_option>

Specifica se le tabelle sottostanti e gli indici associati sono disponibili per le query e la modifica dei dati durante l'operazione sugli indici. Il valore predefinito è OFF. È possibile eseguire REBUILD come ONLINE operazione.

• ON

  I blocchi di tabella a lungo termine non vengono mantenuti per la durata dell'operazione sugli indici. Durante la fase principale dell'operazione sull'indice, nella tabella di origine viene mantenuto solo un blocco di Condivisione finalità (IS). Questo comportamento consente l'esecuzione di query o l'aggiornamento della tabella sottostante e degli indici. All'inizio dell'operazione viene mantenuto un blocco condiviso (S) sull'oggetto di origine per un breve periodo. Al termine dell'operazione, per un breve periodo viene acquisito un blocco condiviso (S) sull'origine, se viene creato un indice non cluster. In alternativa, un blocco di Sch-M (modifica dello schema) viene acquisito quando un indice cluster viene creato o eliminato online e quando viene ricompilato un indice cluster o non cluster. ONLINE non può essere impostato su ON quando viene creato un indice in una tabella temporanea locale. È consentita solo l'operazione di ricompilazione dell'heap a thread singolo.

  Per eseguire il DDL per SWITCH o la ricompilazione dell'indice online, è necessario completare tutte le transazioni di blocco attive in esecuzione in una determinata tabella. Durante l'esecuzione, l'operazione di ricompilazione impedisce l'avvio SWITCH di nuove transazioni e potrebbe influire significativamente sulla velocità effettiva del carico di lavoro e ritardare temporaneamente l'accesso alla tabella sottostante.

• OFF

  I blocchi di tabella si applicano per la durata dell'operazione sugli indici. Un'operazione sugli indici offline che crea, ricompila o elimina un indice cluster oppure ricompila o elimina un indice non cluster acquisisce un blocco di modifica dello schema (SCH-M) sulla tabella. Il blocco impedisce agli utenti di accedere alla tabella sottostante per la durata dell'operazione. Un'operazione sugli indici offline che crea un indice non cluster acquisisce un blocco condiviso (S) sulla tabella. Questo blocco impedisce gli aggiornamenti alla tabella sottostante, ma consente operazioni di lettura, ad esempio SELECT istruzioni. Sono consentite operazioni di ricompilazione dell'heap multithread.

  Per altre informazioni, vedere Funzionamento delle operazioni sugli indici online.

  Note

  Le operazioni sugli indici online non sono disponibili in ogni edizione di SQL Server. Per altre informazioni, vedere Edizioni e funzionalità supportate di SQL Server 2022.

MOVE TO { partition_scheme_name(column_name [ ,... n ] ) | filegroup | "default" }

Si applica a: SQL Server e database SQL di Azure.

Specifica una posizione in cui spostare le righe di dati attualmente presenti a livello foglia nell'indice cluster. La tabella viene spostata nella nuova posizione. Questa opzione è valida solo per i vincoli che creano un indice cluster.

{ CHECK | NOCHECK } CONSTRAINT

Specifica che constraint_name è abilitato o disabilitato. Questa opzione può essere usata solo con FOREIGN KEY i vincoli e CHECK . Quando NOCHECK viene specificato, il vincolo è disabilitato e gli inserimenti o gli aggiornamenti futuri alla colonna non vengono convalidati in base alle condizioni del vincolo. DEFAULTI vincoli , PRIMARY KEYe UNIQUE non possono essere disabilitati.

• ALL

  Specifica che tutti i vincoli sono disabilitati con l'opzione NOCHECK o abilitati con l'opzione CHECK .

{ ENABLE | DISABLE } TRIGGER

Specifica che trigger_name è abilitato o disabilitato. Quando un trigger è disabilitato, è comunque definito per la tabella. Tuttavia, quando INSERTle istruzioni , UPDATEo DELETE vengono eseguite sulla tabella, le azioni nel trigger non vengono eseguite finché il trigger non viene riabilitato.

• ALL

  Specifica l'abilitazione o la disabilitazione di tutti i trigger della tabella.

• trigger_name

  Specifica il nome del trigger da abilitare o disabilitare.

{ ENABLE | DISABLE } CHANGE_TRACKING

Si applica a: SQL Server e database SQL di Azure.

Specifica se il rilevamento delle modifiche è abilitato o disabilitato per la tabella. Per impostazione predefinita, il rilevamento delle modifiche è disabilitato.

Questa opzione è disponibile solo quando il rilevamento delle modifiche è abilitato per il database. Per altre informazioni, vedere Opzioni ALTER DATABASE SET.

Per abilitare il rilevamento delle modifiche, nella tabella deve essere presente una chiave primaria.

WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )

Si applica a: SQL Server e database SQL di Azure.

Indica se il motore di database tiene traccia dell'aggiornamento delle colonne per le quali è abilitato il rilevamento delle modifiche. Il valore predefinito è OFF.

SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name. ] target_table [ PARTITION target_partition_number_expression ]

Si applica a: SQL Server e database SQL di Azure.

Trasferisce un blocco di dati in uno dei modi seguenti:

• Riassegna tutti i dati di una tabella come partizione a una tabella partizionata già esistente.
• Sposta una partizione da una tabella partizionata a un'altra.
• Riassegna tutti i dati in una partizione di una tabella partizionata a una tabella non partizionata esistente.

Se table è una tabella partizionata, è necessario specificare source_partition_number_expression. Se target_table è partizionata, è necessario specificare target_partition_number_expression. Quando si riassegnano i dati di una tabella come partizione a una tabella esistente già partizionata o se si sposta una partizione da una tabella partizionata a un'altra, la partizione di destinazione deve essere già esistente e vuota.

Quando si riassegnano i dati di una partizione per formare un'unica tabella, la tabella di destinazione deve esistere già ed essere vuota. Sia la tabella o la partizione di origine che la tabella o la partizione di destinazione devono trovarsi nello stesso filegroup. È inoltre necessario che gli indici o le partizioni degli indici corrispondenti si trovino nello stesso filegroup. Al trasferimento di partizioni vengono applicate molte ulteriori restrizioni. table e target_table non possono essere la stessa tabella. target_table può essere un identificatore in più parti.

source_partition_number_expression e target_partition_number_expression sono espressioni costanti che possono fare riferimento a variabili e funzioni, incluse variabili con tipo definito dall'utente (UDT) e funzioni definite dall'utente, ma non a espressioni Transact-SQL.

Una tabella partizionata con un indice columstore cluster presenta lo stesso comportamento di un heap partizionato:

• La chiave primaria deve includere la chiave di partizione.
• La chiave di partizione deve essere inclusa in indice univoco. Ma l'inclusione della chiave di partizione con un indice univoco esistente può modificarne l'univocità.
• Per cambiare le partizioni, tutti gli indici non cluster devono includere la chiave di partizione.

Per SWITCH restrizioni quando si usa la replica, vedere Replicare tabelle e indici partizionati.

Gli indici columnstore non cluster sono stati compilati in un formato di sola lettura prima di SQL Server 2016 (13.x) e per database SQL prima della versione V12. È necessario ricompilare gli indici columnstore non cluster nel formato corrente (aggiornabile) prima di poter eseguire qualsiasi PARTITION operazione.

Limitations

Se entrambe le tabelle sono partizionate in modo identico, inclusi gli indici non cluster e la tabella di destinazione non include indici non cluster, è possibile che venga visualizzato un errore 4907.

Output di esempio:

Msg 4907, Level 16, State 1, Line 38
'ALTER TABLE SWITCH' statement failed. The table 'MyDB.dbo.PrtTable1' has 4 partitions while index 'MS1' has 6 partitions.

SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | "default" | "NULL" })

Si applica a: SQL Server. Il database SQL di Azure non supporta FILESTREAM.

Specifica dove vengono archiviati i dati FILESTREAM.

ALTER TABLE con la SET FILESTREAM_ON clausola ha esito positivo solo se la tabella non contiene colonne FILESTREAM. È possibile aggiungere colonne FILESTREAM usando una seconda ALTER TABLE istruzione.

Se si specifica partition_scheme_name, vengono applicate le regole per CREATE TABLE. Assicurarsi che la tabella sia già partizionata per i dati delle righe e che il relativo schema di partizione usi la stessa funzione e le stesse colonne di partizione dello schema di partizione FILESTREAM.

filestream_filegroup_name specifica il nome di un filegroup FILESTREAM. Il filegroup deve avere un file definito per il filegroup usando un'istruzione CREATE DATABASE o ALTER DATABASE oppure viene visualizzato un errore.

"default" specifica il filegroup FILESTREAM con il set di DEFAULT proprietà. Se non è presente alcun filegroup FILESTREAM, viene visualizzato un errore.

"NULL" specifica che tutti i riferimenti ai filegroup FILESTREAM per la tabella vengono rimossi. È necessario eliminare innanzitutto tutte le colonne FILESTREAM. Usare SET FILESTREAM_ON = "NULL" per eliminare tutti i dati FILESTREAM associati a una tabella.

SET ( SYSTEM_VERSIONING = { OFF | ON [ ( HISTORY_TABLE = schema_name . history_table_name [ , DATA_CONSISTENCY_CHECK = { ON | OFF } ] ) ] } )

Si applica a: SQL Server 2016 (13.x) e versioni successive e database SQL di Azure.

Disabilita o abilita il controllo delle versioni di sistema di una tabella. Per abilitare il controllo delle versioni di sistema di una tabella, il sistema verifica che siano soddisfatti il tipo di dati, il vincolo di nullità e i requisiti dei vincoli di chiave primaria per il controllo delle versioni del sistema. Il sistema registra la cronologia di ogni record nella tabella con controllo delle versioni di sistema in una tabella di cronologia separata. Se l'argomento HISTORY_TABLE non viene usato, il nome di questa tabella di cronologia è MSSQL_TemporalHistoryFor<primary_table_object_id>. Se la tabella di cronologia non esiste, il sistema genera una nuova tabella di cronologia corrispondente allo schema della tabella corrente, crea un collegamento tra le due tabelle e consente al sistema di registrare la cronologia di ogni record nella tabella corrente nella tabella di cronologia. Se si usa l'argomento HISTORY_TABLE per creare un collegamento e usare una tabella di cronologia esistente, il sistema crea un collegamento tra la tabella corrente e la tabella specificata. Quando si crea un collegamento a una tabella di cronologia esistente, è possibile scegliere di eseguire una verifica coerenza dei dati. Questa verifica coerenza dei dati garantisce che i record esistenti non si sovrappongano. L'impostazione predefinita prevede l'esecuzione della verifica coerenza dei dati. Usare l'argomento SYSTEM_VERSIONING = ON in una tabella definita con la clausola PERIOD FOR SYSTEM_TIME per trasformare la tabella esistente in una tabella temporale. Per altre informazioni, vedere Tabelle temporali.

HISTORY_RETENTION_PERIOD = { INFINITE | number { DAY | DAYS | SETTIMANA | SETTIMANE | MONTH | MESI | YEAR | YEARS } }

Si applica a: SQL Server 2017 (14.x), Database SQL di Azure.

Specifica il periodo di conservazione finito o infinito per i dati cronologici in una tabella temporale. Se è omesso, si applicherà il periodo di conservazione infinito.

DATA_DELETION

Si applica a: solo SQL Edge di Azure

Abilita la pulizia basata sui criteri di conservazione dei dati non recenti o obsoleti dalle tabelle all'interno di un database. Per altre informazioni, vedere Abilitare e disabilitare la conservazione dei dati. Per abilitare la conservazione dei dati, è necessario specificare i parametri seguenti.

• FILTER_COLUMN = { column_name }

  Specifica la colonna che deve essere utilizzata per determinare se le righe della tabella sono obsolete o meno. Per la colonna di filtro sono consentiti i tipi di dati seguenti.

  • date
  • datetime
  • datetime2
  • smalldatetime
  • datetimeoffset

• RETENTION_PERIOD = { INFINITE | number { DAY | DAYS | SETTIMANA | SETTIMANE | MONTH | MESI | YEAR | YEARS } }

  Specifica i criteri del periodo di conservazione per la tabella. Il periodo di conservazione viene specificato come combinazione tra un valore intero positivo e l'unità della parte della data.

SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )

Si applica a: SQL Server e database SQL di Azure.

Specifica i metodi consentiti di escalation blocchi per una tabella.

• AUTO

  Questa opzione consente al motore di database di SQL Server di selezionare la granularità dell'escalation blocchi appropriata per lo schema della tabella.

  • Se la tabella è partizionata, l'escalation dei blocchi è consentita alla granularità dell'heap o dell'albero B (HoBT). In altre parole, l'escalation è consentita al livello di partizione. Dopo l'escalation del blocco al livello HoBT, il blocco non verrà inoltrato in un secondo momento alla TABLE granularità.

  • Se la tabella non è partizionata, l'escalation dei blocchi viene eseguita sulla TABLE granularità.

• TABLE

  L'escalation blocchi viene eseguita con una granularità a livello di tabella, indipendentemente dal partizionamento o meno della tabella. TABLE è il valore predefinito.

• DISABLE

  Evita che venga eseguita l'escalation blocchi nella maggior parte dei casi. I blocchi a livello di tabella non vengono completamente disattivati. Ad esempio, quando si esegue l'analisi di una tabella in cui non è presente alcun indice cluster in corrispondenza del livello di isolamento serializzabile, il motore di database dovrà acquisire un blocco di tabella per proteggere l'integrità dei dati.

REBUILD

Usare la REBUILD WITH sintassi per ricompilare un'intera tabella, incluse tutte le partizioni in una tabella partizionata. Se la tabella ha un indice cluster, l'opzione REBUILD ricompila l'indice cluster. REBUILD può essere eseguito come ONLINE operazione.

Usare la REBUILD PARTITION sintassi per ricompilare una singola partizione in una tabella partizionata.

PARTITION = ALL

Si applica a: SQL Server e database SQL di Azure.

Ricompila tutte le partizioni in caso di modifica delle impostazioni di compressione della partizione.

REBUILD WITH ( <rebuild_option> )

Tutte le opzioni vengono applicate a una tabella con un indice cluster. Se nella tabella non è presente un indice cluster, sulla struttura di heap influiranno solo alcune opzioni.

Quando non viene specificata un'impostazione di compressione specifica con l'operazione REBUILD , viene usata l'impostazione di compressione corrente per la partizione. Per restituire l'impostazione corrente, eseguire una query sulla data_compression colonna nella vista del sys.partitions catalogo.

Per una descrizione completa delle opzioni di ricompilazione, vedere ALTER TABLE index_option.

DATA_COMPRESSION

Si applica a: SQL Server e database SQL di Azure.

Specifica l'opzione di compressione dei dati per la tabella, il numero di partizione o l'intervallo di partizioni specificato. Le opzioni sono le seguenti:

• NONE

  La tabella o le partizioni specificate non vengono compresse. Questa opzione non si applica alle tabelle columnstore.

• FILA

  Le partizioni di tabella o specificate vengono compresse usando la compressione di riga. Questa opzione non si applica alle tabelle columnstore.

• PAGINA

  Le partizioni di tabella o specificate vengono compresse usando la compressione di pagina. Questa opzione non si applica alle tabelle columnstore.

• COLUMNSTORE

  Si applica a: SQL Server 2014 (12.x) e versioni successive e database SQL di Azure.

  Si applica solo alle tabelle columnstore. COLUMNSTORE specifica di decomprimere una partizione compressa con l'opzione COLUMNSTORE_ARCHIVE . Quando i dati vengono ripristinati, continuano a essere compressi con la compressione columnstore usata per tutte le tabelle columnstore.

• COLUMNSTORE_ARCHIVE

  Si applica a: SQL Server 2014 (12.x) e versioni successive e database SQL di Azure.

  Si applica solo alle tabelle columnstore, ovvero tabelle archiviate con un indice columnstore cluster. COLUMNSTORE_ARCHIVE comprime ulteriormente la partizione specificata in una dimensione inferiore. Usare questa opzione per l'archiviazione o altre situazioni in cui sono richieste dimensioni di archiviazione inferiori ed è possibile concedere più tempo per l'archiviazione e il recupero.

  Per ricompilare contemporaneamente più partizioni, vedere index_option. Se la tabella non dispone di un indice cluster, la modifica della compressione dei dati comporta la ricompilazione dell'heap e degli indici non cluster. Per altre informazioni sulla compressione, vedere Compressione dei dati.

  ALTER TABLE REBUILD PARTITION WITH DATA COMPRESSION = ROW o PAGE non è consentito nel database SQL in Microsoft Fabric Preview.

XML_COMPRESSION

Si applica a: SQL Server 2022 (16.x) e versioni successive Database SQL di Azure e Istanza gestita di SQL di Azure

Specifica l'opzione di compressione XML per tutte le colonne con tipo di dati xml incluse nella tabella. Le opzioni sono le seguenti:

• ON

  Le colonne che usano il tipo di dati xml vengono compresse.

• OFF

  Le colonne che usano il tipo di dati xml non vengono compresse.

ONLINE = { ON | OFF } <come si applica a single_partition_rebuild_option>

Specifica se una singola partizione delle tabelle sottostanti e degli indici associati è disponibile per le query e le modifiche dei dati durante l'operazione sull'indice. Il valore predefinito è OFF. È possibile eseguire REBUILD come ONLINE operazione.

• ON

  I blocchi di tabella a lungo termine non vengono mantenuti per la durata dell'operazione sugli indici. È necessario un blocco condiviso (S) sulla tabella all'inizio della ricompilazione dell'indice e un blocco di modifica schema (Sch-M) sulla tabella alla fine della ricompilazione dell'indice online. Sebbene entrambi i blocchi siano blocchi di metadati brevi, il blocco Sch-M deve attendere il completamento di tutte le transazioni bloccanti. Durante il tempo di attesa, il blocco Sch-M impedisce tutte le altre transazioni in attesa dovute a questo blocco in caso di accesso alla stessa tabella.

  Note

  Con la ricompilazione dell'indice online è possibile impostare le opzioni low_priority_lock_wait descritte più avanti in questa sezione.

• OFF

  I blocchi di tabella vengono applicati per la durata dell'operazione sugli indici. Il blocco impedisce agli utenti di accedere alla tabella sottostante per la durata dell'operazione.

column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

Si applica a: SQL Server e database SQL di Azure.

Nome del set di colonne. Un set di colonne è una rappresentazione XML non tipizzata che combina tutte le colonne di tipo sparse di una tabella in un output strutturato. Un set di colonne non può essere aggiunto a una tabella che contiene colonne di tipo sparse. Per altre informazioni sui set di colonne, vedere Usare set di colonne.

{ ENABLE | DISABLE } FILETABLE_NAMESPACE

Si applica a: SQL Server.

Consente di abilitare o disabilitare i vincoli definiti dal sistema su una tabella FileTable. Può essere utilizzato solo con una tabella FileTable.

SET ( FILETABLE_DIRECTORY = directory_name )

Si applica a: SQL Server. Il database SQL di Azure non supporta FileTable.

Specifica un nome di directory FileTable compatibile con Windows. Questo nome deve essere univoco tra tutti i nomi di directory FileTable nel database. Il confronto di univocità non supporta la distinzione tra maiuscole e minuscole, nonostante le impostazioni delle regole di confronto SQL. Può essere utilizzato solo con una tabella FileTable.

REMOTE_DATA_ARCHIVE

Si applica a: SQL Server 2017 (14.x) e versioni successive.

Consente di abilitare o disabilitare Stretch Database per una tabella. Per altre informazioni, vedere Stretch Database.

Abilitare Stretch Database per una tabella

Quando si specifica ON per abilitare Stretch per una tabella, è necessario specificare anche MIGRATION_STATE = OUTBOUND per iniziare subito la migrazione dei dati o MIGRATION_STATE = PAUSED per posticiparla. Il valore predefinito è MIGRATION_STATE = OUTBOUND. Per altre informazioni sull'abilitazione di Stretch per una tabella, vedere Abilitare Stretch Database per una tabella.

Prerequisites. Prima di abilitare Stretch per una tabella, è necessario abilitare la funzionalità nel server e nel database. Per altre informazioni, vedere Abilitare Stretch Database per un database.

Permissions. L'abilitazione di Stretch per un database o una tabella richiede autorizzazioni db_owner. L'abilitazione di Stretch per una tabella richiede ALTER anche le autorizzazioni per la tabella.

Disabilitare Stretch Database per una tabella

Quando si disabilita Stretch per una tabella, esistono due opzioni disponibili per i dati remoti che sono già stati migrati in Azure. Per altre informazioni, vedere Disabilitare Stretch Database e ripristinare i dati remoti.

• Per disabilitare l'estensione per una tabella e copiare i dati remoti per la tabella da Azure a SQL Server, eseguire il comando seguente. Questo comando non può essere annullato.

  ALTER TABLE <table_name>
     SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = INBOUND ) ) ;
  

Questa operazione comporta costi di trasferimento dati e non può essere annullata. Per altre informazioni, vedere i dettagli sui prezzi dei trasferimenti di dati.

Dopo aver copiato tutti i dati remoti da Azure a SQL Server, l'estensione viene disabilitata per la tabella.

• Per disabilitare l'estensione per una tabella e abbandonare i dati remoti, eseguire il comando seguente.

  ALTER TABLE <table_name>
     SET ( REMOTE_DATA_ARCHIVE = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED ) ) ;
  

Dopo aver disabilitato Stretch Database per una tabella, la migrazione di dati viene interrotta e i risultati della query non includono più i risultati della tabella remota.

Se si disabilita Stretch, la tabella remota non viene rimossa. Se si vuole eliminare la tabella remota, eseguire l'operazione tramite il portale di Azure.

[ FILTER_PREDICATE = { null | predicato } ]

Si applica a: SQL Server 2017 (14.x) e versioni successive.

Specifica facoltativamente un predicato di filtro per selezionare le righe di cui eseguire la migrazione da una tabella che contiene sia dati cronologici sia dati correnti. Il predicato deve eseguire la chiamata a una funzione inline con valori di tabella. Per altre informazioni, vedere Abilitare Stretch Database per una tabella e Selezionare le righe di cui eseguire la migrazione tramite una funzione di filtro.

Se non si specifica un predicato del filtro, viene eseguita la migrazione dell'intera tabella.

Quando si specifica un predicato di filtro, è necessario specificare MIGRATION_STATEanche .

MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }

Si applica a: SQL Server 2017 (14.x) e versioni successive.

• Specificare OUTBOUND per eseguire la migrazione dei dati da SQL Server ad Azure.

• Specificare INBOUND per copiare i dati remoti per la tabella da Azure a SQL Server e disabilitare Stretch per la tabella. Per altre informazioni, vedere Disabilitare Stretch Database e ripristinare i dati remoti.

  Questa operazione comporta costi di trasferimento dati e non può essere annullata.

• Specificare PAUSED per sospendere o posticipare la migrazione dei dati. Per altre informazioni, vedere Sospendere e riprendere la migrazione dei dati.

WAIT_AT_LOW_PRIORITY

Si applica a: SQL Server 2014 (12.x) e versioni successive e database SQL di Azure.

Per una ricompilazione di indice online è necessario attendere il blocco delle operazioni su questa tabella. WAIT_AT_LOW_PRIORITY indica che l'operazione di ricompilazione dell'indice online attende blocchi con priorità bassa, consentendo ad altre operazioni di continuare mentre l'operazione di compilazione dell'indice online è in attesa. L'omissione dell'opzione WAIT AT LOW PRIORITY equivale a WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = tempo [ MINUTES ]

Si applica a: SQL Server 2014 (12.x) e versioni successive e database SQL di Azure.

Il tempo di attesa, ovvero un valore intero specificato in minuti, che l'indice SWITCH online o ricompila blocchi attende con priorità bassa quando si esegue il comando DDL. Se l'operazione viene bloccata per il MAX_DURATION tempo, viene eseguita una delle ABORT_AFTER_WAIT azioni. MAX_DURATION il tempo è sempre espresso in minuti ed è possibile omettere la parola MINUTES.

ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS }

Si applica a: SQL Server 2014 (12.x) e versioni successive e database SQL di Azure.

• NONE

  Continuare ad attendere il blocco con priorità normale (regolare).

• SELF

  Uscire dall'operazione DDL di ricompilazione dell'indice SWITCH online o attualmente in esecuzione senza eseguire alcuna azione.

• BLOCKERS

  Terminare tutte le transazioni utente che attualmente bloccano l'operazione DDL di ricompilazione dell'indice SWITCH online o in modo che l'operazione possa continuare.

  È richiesta ALTER ANY CONNECTION l'autorizzazione.

IF EXISTS

Si applica a: SQL Server 2016 (13.x) e versioni successive e database SQL di Azure.

Elimina in modo condizionale la colonna o il vincolo solo se è già esistente.

RESUMABLE = { ON | OFF}

Si applica a: SQL Server 2022 (16.x) e versioni successive.

Specifica se un'operazione ALTER TABLE ADD CONSTRAINT sull'indice online è ripristinabile. L'operazione di aggiunta di un vincolo alla tabella è ripristinabile quando l'impostazione è ON. L'operazione Aggiungi vincolo di tabella non è ripristinabile quando OFF. Il valore predefinito è OFF. L'opzione RESUMABLE può essere usata come parte dell'istruzione ALTER TABLE index_option nell'istruzione ALTER TABLE table_constraint.

MAX_DURATION se usato con RESUMABLE = ON (richiede ONLINE = ON) indica l'ora (un valore intero specificato in minuti) che un'operazione di aggiunta online ripristinabile viene eseguita prima di essere sospesa. Se l'opzione non è specificata, l'operazione continua fino al completamento.

Per altre informazioni sull'abilitazione e l'uso di operazioni ripristinabili, vedere Aggiungere vincoli di tabella ripristinabiliALTER TABLE ADD CONSTRAINT.

Remarks

Per aggiungere nuove righe di dati, usare INSERT. Per rimuovere righe di dati, usare DELETE o TRUNCATE TABLE. Per modificare i valori nelle righe esistenti, usare UPDATE.

Se nella cache delle procedure sono presenti piani di esecuzione che fanno riferimento alla tabella, ALTER TABLE contrassegnarli per essere ricompilati alla successiva esecuzione.

Nel database SQL in Anteprima di Microsoft Fabric è possibile creare alcune funzionalità della tabella, ma non eseguire il mirroring in Fabric OneLake. Per altre informazioni, vedere Limitazioni per il mirroring del database SQL di Fabric (anteprima).

Modificare le dimensioni di una colonna

È possibile modificare la lunghezza, la precisione o la scala di una colonna specificando nuove dimensioni per il tipo di dati della colonna. Usare la ALTER COLUMN clausola . Se nella colonna sono presenti dati, le nuove dimensioni non possono essere minori delle dimensioni massime dei dati. Inoltre, non è possibile definire la colonna in un indice, a meno che la colonna non sia un tipo di dati varchar, nvarchar o varbinary e l'indice non sia il risultato di un PRIMARY KEY vincolo. Vedere l'esempio nella sezione breve intitolata Modifica di una definizione di colonna.

Blocchi e ALTER TABLE

Le modifiche specificate nell'implementazione ALTER TABLE vengono implementate immediatamente. Se le modifiche richiedono modifiche delle righe nella tabella, ALTER TABLE aggiorna le righe. ALTER TABLE acquisisce un blocco di modifica dello schema (Sch-M) sulla tabella per assicurarsi che nessun'altra connessione faccia riferimento anche ai metadati per la tabella durante la modifica, ad eccezione delle operazioni sugli indici online che richiedono un breve blocco Sch-M alla fine. In un'operazione ALTER TABLE...SWITCH il blocco viene acquisito sia sulle tabelle di origine sia in quelle di destinazione. Le modifiche apportate alla tabella vengono registrate e possono essere recuperate completamente. Le modifiche che interessano tutte le righe di tabelle di grandi dimensioni, ad esempio l'eliminazione di una colonna o, in alcune edizioni di SQL Server, l'aggiunta di una NOT NULL colonna con un valore predefinito può richiedere molto tempo per completare e generare molti record di log. Eseguire queste ALTER TABLE istruzioni con la stessa attenzione di qualsiasi INSERTistruzione , UPDATEo DELETE che influisce su molte righe.

Si applica al warehouse in Microsoft Fabric.

ALTER TABLE non può far parte di una transazione esplicita.

Eventi estesi (XEvent) per il commutatore di partizione

I seguenti eventi XEvent sono correlati alle ALTER TABLE ... SWITCH PARTITION ricompilazione degli indici online e online.

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

Aggiungere colonne NOT NULL come operazione online

In SQL Server 2012 (11.x) Enterprise Edition e versioni successive, l'aggiunta di una NOT NULL colonna con un valore predefinito è un'operazione online quando il valore predefinito è una costante di runtime. Questo implica che l'operazione viene completata quasi istantaneamente nonostante l'elevato numero di righe nella tabella: durante l'operazione, le righe esistenti nella tabella non vengono aggiornate, ma il valore predefinito viene archiviato solo nei metadati della tabella e il valore viene cercato in base alle necessità nelle query che accedono a tali righe. Questo comportamento è automatico. Non è necessaria alcuna sintassi aggiuntiva per implementare l'operazione online oltre la ADD COLUMN sintassi. Una costante di runtime è un'espressione che produce lo stesso valore durante il runtime per ogni riga nella tabella nonostante il relativo determinismo. Ad esempio, l'espressione "My temporary data"costante o la funzione GETUTCDATETIME() di sistema sono costanti di runtime. Al contrario, le funzioni NEWID() e NEWSEQUENTIALID() non sono costanti di runtime perché viene generato un valore univoco per ogni riga della tabella. L'aggiunta di una NOT NULL colonna con un valore predefinito che non è una costante di runtime viene sempre eseguita offline e viene acquisito un blocco esclusivo (Sch-M) per la durata dell'operazione.

Mentre le righe esistenti fanno riferimento al valore archiviato nei metadati, il valore predefinito viene archiviato nella riga per tutte le nuove righe inserite e che non specificano un altro valore per la colonna. Il valore predefinito archiviato nei metadati passa a una riga esistente quando la riga viene aggiornata (anche se la colonna effettiva non è specificata nell'istruzione) o se la tabella o l'indice UPDATE cluster viene ricompilato.

Le colonne di tipo varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, geometry, geography o CLR non possono essere aggiunte in un'operazione online. Non è possibile aggiungere una colonna online se con tale operazione le dimensioni massime possibili per la riga superano il limite di 8.060 byte. In tal caso, la colonna viene aggiunta come operazione offline.

Esecuzione di piani paralleli

In SQL Server 2012 (11.x) Enterprise Edition e versioni successive, il numero di processori impiegati per eseguire una singola ALTER TABLE ADD istruzione (basata su indice) CONSTRAINT o DROP (indice cluster) CONSTRAINT è determinata dall'opzione di configurazione max degree of parallelism e dal carico di lavoro corrente. Se il motore di database rileva che il sistema è occupato, il grado di parallelismo dell'operazione viene ridotto automaticamente prima dell'avvio dell'esecuzione dell'istruzione. È possibile configurare manualmente il numero di processori usati per eseguire l'istruzione specificando l'opzione MAXDOP . Per altre informazioni, vedere Configurazione del server: max degree of parallelism.

Tabelle partizionate

Oltre a eseguire operazioni SWITCH che coinvolgono tabelle partizionate, usare ALTER TABLE per modificare lo stato delle colonne, dei vincoli e dei trigger di una tabella partizionata esattamente come per le tabelle non partizionate. Non è tuttavia possibile usare questa istruzione per modificare il modo in cui la tabella stessa è partizionata. Per la ripartizione di una tabella partizionata, usare ALTER PARTITION SCHEME e ALTER PARTITION FUNCTION. Non è inoltre possibile modificare il tipo di dati di una colonna di una tabella partizionata.

Restrizioni per tabelle con viste associate a schema

Le restrizioni che si applicano alle istruzioni nelle tabelle con viste associate a ALTER TABLE schema sono le stesse delle restrizioni attualmente applicate durante la modifica di tabelle con un indice semplice. È possibile aggiungere una colonna mentre non è consentito rimuovere o modificare una colonna che fa parte di una vista associata a uno schema. Se l'istruzione ALTER TABLE richiede la modifica di una colonna utilizzata in una vista associata a schema, ALTER TABLE ha esito negativo e il motore di database genera un messaggio di errore. Per altre informazioni sull'associazione allo schema e sulle viste indicizzate, vedere CREATE VIEW.

La creazione di una vista associata a uno schema che fa riferimento a tabelle di base non influisce sull'aggiunta o sulla rimozione di trigger in tali tabelle.

Indici e ALTER TABLE

Gli indici creati nell'ambito di un vincolo vengono eliminati con l'eliminazione del vincolo. Gli indici creati con CREATE INDEX devono essere eliminati con DROP INDEX. Usare l'istruzione ALTER INDEX per ricompilare una parte di indice di una definizione di vincolo. Il vincolo non deve essere eliminato e aggiunto di nuovo con ALTER TABLE.

Tutti gli indici e i vincoli basati su una colonna devono essere rimossi prima della rimozione della colonna.

Quando si elimina un vincolo con cui è stato creato un indice cluster, le righe di dati archiviate a livello foglia nell'indice cluster vengono archiviate in una tabella non cluster. È possibile eliminare l'indice cluster e spostare la tabella risultante in un altro filegroup o schema di partizione in una singola transazione specificando l'opzione MOVE TO . L'opzione MOVE TO presenta le restrizioni seguenti:

• MOVE TO non è valido per le viste indicizzate o gli indici non cluster.

• Lo schema di partizione o il filegroup deve essere già esistente.

• Se MOVE TO non viene specificato, la tabella si trova nello stesso schema di partizione o filegroup definito per l'indice cluster.

Quando si elimina un indice cluster, specificare l'opzione ONLINE = ON in modo che la DROP INDEX transazione non blocchi le query e le modifiche ai dati sottostanti e agli indici non cluster associati.

ONLINE = ON presenta le restrizioni seguenti:

• ONLINE = ON non è valido anche per gli indici cluster disabilitati. Gli indici disabilitati devono essere eliminati tramite ONLINE = OFF.
• È possibile eliminare un solo indice alla volta.
• ONLINE = ON non è valido per le viste indicizzate, gli indici non cluster o gli indici nelle tabelle temporanee locali.
• ONLINE = ON non è valido per gli indici columnstore.

Per l'eliminazione di un indice cluster, lo spazio su disco temporaneo deve essere uguale alle dimensioni dell'indice cluster esistente. Questo spazio aggiuntivo viene rilasciato al termine dell'operazione.

Replicare le modifiche dello schema

Quando si esegue ALTER TABLE in una tabella pubblicata in un server di pubblicazione di SQL Server, per impostazione predefinita, tale modifica viene propagata a tutti i Sottoscrittori di SQL Server. Questa funzionalità presenta alcune restrizioni e può essere disabilitata. Per altre informazioni, vedere Apportare modifiche allo schema nei database di pubblicazione.

Compressione dei dati

Le tabelle di sistema non possono essere abilitate per la compressione. Se la tabella è un heap, l'operazione di ricompilazione per ONLINE la modalità è a thread singolo. Usare OFFLINE la modalità per un'operazione di ricompilazione dell'heap multithreading. Per altre informazioni sulla compressione dei dati, vedere Compressione dei dati.

Per valutare come la modifica dello stato di compressione influisce su una tabella, un indice o una partizione, utilizzare la stored procedure di sistema sp_estimate_data_compression_savings .

Alle tabelle partizionate vengono applicate le restrizioni seguenti:

• Non è possibile modificare l'impostazione di compressione di una singola partizione se la tabella include indici non allineati.
• La sintassi ALTER TABLE <table> REBUILD PARTITION ... consente di ricompilare la partizione specificata.
• La sintassi ALTER TABLE <table> REBUILD WITH ... consente di ricompilare tutte le partizioni.

Eliminare colonne ntext

Quando si eliminano colonne usando il tipo di dati ntext deprecato, la pulizia dei dati eliminati viene eseguita come operazione serializzata su tutte le righe. L'operazione di pulizia può richiedere una grande quantità di tempo. Quando si elimina una colonna ntext in una tabella con un numero elevato di righe, aggiornare prima la colonna ntext al NULL valore e quindi eliminare la colonna. È possibile eseguire questa opzione con operazioni parallele rendendola molto più rapida.

Ricompilazione dell'indice online

Per eseguire l'istruzione DDL per una ricompilazione dell'indice online, è necessario completare tutte le transazioni bloccanti attive in esecuzione in una specifica tabella. Quando la ricompilazione dell'indice online viene avviata, blocca tutte le nuove transazioni pronte per l'esecuzione in questa tabella. Sebbene la durata del blocco della ricompilazione dell'indice online sia breve, l'attesa del completamento di tutte le transazioni aperte in una tabella specificata e il blocco dell'avvio di nuove transazioni potrebbero influire in modo significativo sulla velocità effettiva. Ciò può provocare un rallentamento o un timeout del carico di lavoro e limitare notevolmente l'accesso alla tabella sottostante. L'opzione WAIT_AT_LOW_PRIORITY consente agli amministratori di database di gestire i blocchi S-lock e Sch-M necessari per le ricompilazione degli indici online. In tutti e tre i casi: NONE, SELFe BLOCKERS, se durante il tempo di attesa ((MAX_DURATION = n [minutes])) non sono presenti attività di blocco, la ricompilazione dell'indice online viene eseguita immediatamente senza attendere e l'istruzione DDL viene completata.

Supporto per la compatibilità

L'istruzione ALTER TABLE supporta solo nomi di tabella in due parti (schema.object). In SQL Server l'uso di un nome di tabella basato sui formati riportati di seguito comporta la generazione dell'errore 117 in fase di compilazione.

• server.database.schema.table
• .database.schema.table
• ..schema.table

Nelle versioni precedenti l'uso del formato server.database.schema.table genera l'errore 4902. La specifica del formato .database.schema.table o del formato ..schema.table ha esito positivo.

Per risolvere il problema, rimuovere l'uso di un prefisso in quattro parti.

Permissions

È necessaria ALTER l'autorizzazione per la tabella.

ALTER TABLE Le autorizzazioni si applicano a entrambe le tabelle coinvolte in un'istruzione ALTER TABLE SWITCH . Tutti i dati trasferiti ereditano la sicurezza della tabella di destinazione.

Se sono state definite colonne nell'istruzione ALTER TABLE in modo che siano di un tipo clr (Common Language Runtime) definito dall'utente o di un tipo di dati alias, REFERENCES è necessaria l'autorizzazione per il tipo.

L'aggiunta o la modifica di una colonna che aggiorna le righe della tabella richiede UPDATE l'autorizzazione per la tabella. Ad esempio, l'aggiunta di una NOT NULL colonna con un valore predefinito o l'aggiunta di una colonna Identity quando la tabella non è vuota.

Examples

Gli esempi di codice in questo articolo usano il AdventureWorks2022 database di esempio o AdventureWorksDW2022 , che è possibile scaricare dalla home page degli esempi di Microsoft SQL Server e dei progetti della community .

Aggiungere colonne e vincoli

Negli esempi di questa sezione viene illustrata l'aggiunta di colonne e vincoli a una tabella.

A. Aggiungere una nuova colonna

Nell'esempio seguente viene aggiunta una colonna che consente valori Null e non ha valori forniti tramite una DEFAULT definizione. Nella nuova colonna ogni riga ha NULL.

CREATE TABLE dbo.doc_exa (column_a INT);
GO

ALTER TABLE dbo.doc_exa
    ADD column_b VARCHAR (20) NULL;
GO

B. Aggiungere una colonna con un vincolo

Nell'esempio seguente viene aggiunta una nuova colonna con un vincolo UNIQUE.

CREATE TABLE dbo.doc_exc (column_a INT);
GO

ALTER TABLE dbo.doc_exc
    ADD column_b VARCHAR (20) NULL
        CONSTRAINT exb_unique UNIQUE;
GO

EXECUTE sp_help doc_exc;
GO

DROP TABLE dbo.doc_exc;
GO

C. Aggiungere un vincolo CHECK non verificato a una colonna esistente

Nell'esempio seguente viene aggiunto un vincolo a una colonna esistente nella tabella. Nella colonna è presente un valore che viola il vincolo. Pertanto, viene utilizzato WITH NOCHECK per evitare che il vincolo venga convalidato in base alle righe esistenti e consentire l'aggiunta del vincolo.

CREATE TABLE dbo.doc_exd (column_a INT);
GO

INSERT INTO dbo.doc_exd VALUES (-1);
GO

ALTER TABLE dbo.doc_exd WITH NOCHECK
    ADD CONSTRAINT exd_check CHECK (column_a > 1);
GO

EXECUTE sp_help doc_exd;
GO

DROP TABLE dbo.doc_exd;
GO

D. Aggiungere un vincolo DEFAULT a una colonna esistente

Nell'esempio seguente viene creata una tabella con due colonne e viene inserito un valore nella prima colonna e l'altra rimane NULL. Viene quindi aggiunto un vincolo DEFAULT alla seconda colonna. Per verificare l'applicazione del vincolo, viene inserito un altro valore nella prima colonna e viene eseguita una query sulla tabella.

CREATE TABLE dbo.doc_exz
(
    column_a INT,
    column_b INT
);
GO

INSERT INTO dbo.doc_exz (column_a) VALUES (7);
GO

ALTER TABLE dbo.doc_exz
    ADD CONSTRAINT col_b_def
        DEFAULT 50 FOR column_b;
GO

INSERT INTO dbo.doc_exz (column_a) VALUES (10);
GO

SELECT * FROM dbo.doc_exz;
GO

DROP TABLE dbo.doc_exz;
GO

E. Aggiungere diverse colonne con vincoli

Nell'esempio seguente vengono aggiunte più colonne con vincoli. I vincoli vengono definiti con la nuova colonna. Alla prima colonna è associata la proprietà IDENTITY. Nella colonna Identity di ogni riga della tabella sono presenti nuovi valori incrementali.

CREATE TABLE dbo.doc_exe
(
    column_a INT
        CONSTRAINT column_a_un UNIQUE
);
GO

ALTER TABLE dbo.doc_exe

    -- Add a PRIMARY KEY identity column.
    ADD column_b INT IDENTITY
        CONSTRAINT column_b_pk PRIMARY KEY,

    -- Add a column that references another column in the same table.
        column_c INT NULL
            CONSTRAINT column_c_fk FOREIGN KEY REFERENCES doc_exe (column_a),

    -- Add a column with a constraint to enforce that
    -- nonnull data is in a valid telephone number format.
        column_d VARCHAR (16) NULL
            CONSTRAINT column_d_chk CHECK (column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
                                       OR column_d LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),

    -- Add a nonnull column with a default.
        column_e DECIMAL (3, 3)
            CONSTRAINT column_e_default DEFAULT .081;
GO

EXECUTE sp_help doc_exe;
GO

DROP TABLE dbo.doc_exe;
GO

F. Aggiungere una colonna nullable con valori predefiniti

Nell'esempio seguente viene aggiunta una colonna che ammette i valori Null con una definizione DEFAULT e viene specificato WITH VALUES per l'assegnazione di valori a ogni riga della tabella. Se WITH VALUES non viene usato, ogni riga ha il valore NULL nella nuova colonna.

CREATE TABLE dbo.doc_exf (column_a INT);
GO

INSERT INTO dbo.doc_exf VALUES (1);
GO

ALTER TABLE dbo.doc_exf
    ADD AddDate SMALLDATETIME
        CONSTRAINT AddDateDflt DEFAULT GETDATE() WITH VALUES NULL;
GO

DROP TABLE dbo.doc_exf;
GO

G. Creare un vincolo PRIMARY KEY con opzioni di compressione di indice o dati

Nell'esempio seguente viene creato il PRIMARY KEY vincolo PK_TransactionHistoryArchive_TransactionID e vengono impostate le opzioni FILLFACTOR, ONLINEe PAD_INDEX. L'indice cluster risultante ha lo stesso nome del vincolo.

Si applica a: SQL Server e database SQL di Azure.

USE AdventureWorks2022;
GO

ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK
    ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID
        PRIMARY KEY CLUSTERED (TransactionID) WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON);
GO

In questo esempio simile viene applicata la compressione di pagina durante l'applicazione della chiave primaria in cluster.

USE AdventureWorks2022;
GO

ALTER TABLE Production.TransactionHistoryArchive WITH NOCHECK
    ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID
        PRIMARY KEY CLUSTERED (TransactionID) WITH (DATA_COMPRESSION = PAGE);
GO

H. Aggiungere una colonna di tipo sparse

Negli esempi seguenti si illustrano l'aggiunta e la modifica di colonne di tipo sparse nella tabella T1. Il codice per creare la tabella T1 è il seguente:

CREATE TABLE T1
(
    C1 INT PRIMARY KEY,
    C2 VARCHAR (50) SPARSE NULL,
    C3 INT SPARSE NULL,
    C4 INT
);
GO

Per aggiungere una colonna di tipo sparse aggiuntiva C5, eseguire l'istruzione riportata di seguito.

ALTER TABLE T1
    ADD C5 CHAR (100) SPARSE NULL;
GO

Per convertire la colonna non di tipo sparse C4 in una colonna di tipo sparse, eseguire l'istruzione riportata di seguito.

ALTER TABLE T1
    ALTER COLUMN C4 ADD SPARSE;
GO

Per convertire la colonna di tipo sparse C4 in una colonna non di tipo sparse, eseguire l'istruzione riportata di seguito.

ALTER TABLE T1
    ALTER COLUMN C4 DROP SPARSE;
GO

I. Aggiungere un set di colonne

Negli esempi seguenti viene illustrata l'aggiunta di una colonna alla tabella T2. Un set di colonne non può essere aggiunto a una tabella che contiene già colonne di tipo sparse. Il codice per creare la tabella T2 è il seguente:

CREATE TABLE T2
(
    C1 INT PRIMARY KEY,
    C2 VARCHAR (50) NULL,
    C3 INT NULL,
    C4 INT
);
GO

Le tre istruzioni seguenti determinano l'aggiunta di un set di colonne denominato CS, quindi la modifica delle colonne C2 e C3 in SPARSE.

ALTER TABLE T2
    ADD CS XML COLUMN_SET FOR ALL_SPARSE_COLUMNS;
GO

ALTER TABLE T2
    ALTER COLUMN C2 ADD SPARSE;
GO

ALTER TABLE T2
    ALTER COLUMN C3 ADD SPARSE;
GO

J. Aggiungere una colonna crittografata

L'istruzione seguente aggiunge una colonna crittografata denominata PromotionCode.

ALTER TABLE Customers
    ADD PromotionCode NVARCHAR (100)
        ENCRYPTED WITH (
            COLUMN_ENCRYPTION_KEY = MyCEK,
            ENCRYPTION_TYPE = RANDOMIZED,
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
        );

K. Aggiungere una chiave primaria con un'operazione ripristinabile

Operazione ALTER TABLE ripristinabile per l'aggiunta di una chiave primaria in cluster nella colonna (a) con MAX_DURATION di 240 minuti.

ALTER TABLE table1
ADD CONSTRAINT PK_Constrain PRIMARY KEY CLUSTERED (a)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);

Eliminare colonne e vincoli

Negli esempi di questa sezione viene illustrata l'eliminazione di colonne e vincoli.

A. Eliminare una colonna o colonne

Nel primo esempio viene modificata una tabella per rimuovere una colonna. Nel secondo esempio vengono rimosse più colonne.

CREATE TABLE dbo.doc_exb
(
    column_a INT,
    column_b VARCHAR (20) NULL,
    column_c DATETIME,
    column_d INT
);
GO

-- Remove a single column.
ALTER TABLE dbo.doc_exb DROP COLUMN column_b;
GO

-- Remove multiple columns.
ALTER TABLE dbo.doc_exb DROP COLUMN column_c, column_d;

B. Eliminare vincoli e colonne

Nel primo esempio viene rimosso un vincolo UNIQUE da una tabella. Nel secondo esempio vengono rimossi due vincoli e una singola colonna.

CREATE TABLE dbo.doc_exc
(
    column_a INT NOT NULL
        CONSTRAINT my_constraint UNIQUE
);
GO

-- Example 1. Remove a single constraint.
ALTER TABLE dbo.doc_exc DROP my_constraint;
GO

DROP TABLE dbo.doc_exc;
GO

CREATE TABLE dbo.doc_exc
(
    column_a INT NOT NULL
        CONSTRAINT my_constraint UNIQUE,
    column_b INT NOT NULL
        CONSTRAINT my_pk_constraint PRIMARY KEY
);
GO

-- Example 2. Remove two constraints and one column
-- The keyword CONSTRAINT is optional. The keyword COLUMN is required.
ALTER TABLE dbo.doc_exc
    DROP CONSTRAINT my_constraint, my_pk_constraint, COLUMN column_b;
GO

C. Eliminare un vincolo PRIMARY KEY nella modalità ONLINE

Nell'esempio seguente viene eliminato un PRIMARY KEY vincolo con l'opzione ONLINE impostata su ON.

ALTER TABLE Production.TransactionHistoryArchive
    DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
    WITH (ONLINE = ON);
GO

D. Aggiungere ed eliminare un vincolo FOREIGN KEY

Nell'esempio seguente viene creata la tabella ContactBackup, successivamente modificata con l'aggiunta di un vincolo FOREIGN KEY che fa riferimento alla tabella Person.Person. Il vincolo FOREIGN KEY viene quindi rimosso.

CREATE TABLE Person.ContactBackup (ContactID INT);
GO

ALTER TABLE Person.ContactBackup
    ADD CONSTRAINT FK_ContactBackup_Contact
        FOREIGN KEY (ContactID) REFERENCES Person.Person (BusinessEntityID);
GO

ALTER TABLE Person.ContactBackup
    DROP CONSTRAINT FK_ContactBackup_Contact;
GO

DROP TABLE Person.ContactBackup;

Modificare una definizione di colonna

A. Cambiare il tipo di dati di una colonna

Nell'esempio seguente la colonna di una tabella viene modificata da INT a DECIMAL.

CREATE TABLE dbo.doc_exy (column_a INT);
GO

INSERT INTO dbo.doc_exy (column_a) VALUES (10);
GO

ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2);
GO

DROP TABLE dbo.doc_exy;
GO

B. Modificare le dimensioni di una colonna

Nell'esempio seguente vengono aumentate le dimensioni di una colonna varchar e la precisione e la scala di una colonna decimal. Poiché le colonne contengono dati, le relative dimensioni possono solo essere aumentate. Si noti inoltre che col_a è definito in un indice univoco. Le dimensioni di possono comunque essere aumentate perché il tipo di col_a dati è varchar e l'indice non è il risultato di un PRIMARY KEY vincolo.

-- Create a two-column table with a unique index on the varchar column.
CREATE TABLE dbo.doc_exy
(
    col_a VARCHAR (5) UNIQUE NOT NULL,
    col_b DECIMAL (4, 2)
);
GO

INSERT INTO dbo.doc_exy VALUES ('Test', 99.99);
GO

-- Verify the current column size.
SELECT name,
       TYPE_NAME(system_type_id),
       max_length,
       precision,
       scale
FROM sys.columns
WHERE object_id = OBJECT_ID(N'dbo.doc_exy');
GO

-- Increase the size of the varchar column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_a VARCHAR (25);
GO

-- Increase the scale and precision of the decimal column.
ALTER TABLE dbo.doc_exy ALTER COLUMN col_b DECIMAL (10, 4);
GO

-- Insert a new row.
INSERT INTO dbo.doc_exy VALUES ('MyNewColumnSize', 99999.9999);
GO

-- Verify the current column size.
SELECT name,
       TYPE_NAME(system_type_id),
       max_length,
       precision,
       scale
FROM sys.columns
WHERE object_id = OBJECT_ID(N'dbo.doc_exy');

C. Modificare le regole di confronto delle colonne

Nell'esempio seguente si illustra come modificare le regole di confronto di una colonna. Innanzitutto, viene creata una tabella con le regole di confronto predefinite dell'utente.

CREATE TABLE T3
(
    C1 INT PRIMARY KEY,
    C2 VARCHAR (50) NULL,
    C3 INT NULL,
    C4 INT
);
GO

In seguito le regole di confronto della colonna C2 vengono impostate su Latin1_General_BIN. Il tipo di dati è richiesto, anche se non è modificato.

ALTER TABLE T3
    ALTER COLUMN C2 VARCHAR (50) COLLATE Latin1_General_BIN;
GO

D. Crittografare una colonna

L'esempio seguente illustra come crittografare una colonna usando Always Encrypted con enclave sicuri.

In primo luogo, viene creata una tabella senza colonne crittografate.

CREATE TABLE T3
(
    C1 INT PRIMARY KEY,
    C2 VARCHAR (50) NULL,
    C3 INT NULL,
    C4 INT
);
GO

Successivamente, la colonna "C2" viene crittografata con una chiave di crittografia, denominata CEK1, e la crittografia casuale. Perché l'istruzione seguente abbia esito positivo:

• La chiave di crittografia della colonna deve essere abilitata per l'enclave, Ciò significa che deve essere crittografato con una chiave master della colonna (CMK) che consente i calcoli dell'enclave.
• L'istanza di SQL Server di destinazione deve supportare Always Encrypted con enclave sicuri.
• L'istruzione deve essere eseguita tramite una connessione configurata per Always Encrypted con enclave sicuri e usando un driver client supportato.
• L'applicazione chiamante deve avere accesso alla chiave gestita dal cliente, proteggendo CEK1.

ALTER TABLE T3 ALTER COLUMN C2 VARCHAR (50)  ENCRYPTED WITH (
     COLUMN_ENCRYPTION_KEY = [CEK1],
     ENCRYPTION_TYPE = RANDOMIZED,
     ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    ) NULL;
GO

Modificare una definizione di tabella

Negli esempi di questa sezione viene illustrato come modificare la definizione di una tabella.

A. Modificare una tabella per modificare la compressione

Nell'esempio seguente viene modificata la compressione di una tabella non partizionata. L'heap o l'indice cluster viene ricompilato. Se la tabella è un heap, tutti gli indici non cluster vengono ricompilati.

ALTER TABLE T1 REBUILD
    WITH (DATA_COMPRESSION = PAGE);

Nell'esempio seguente viene modificata la compressione di una tabella partizionata. La sintassi REBUILD PARTITION = 1 consente di ricompilare solo il numero di partizione 1.

Si applica a: SQL Server.

ALTER TABLE PartitionTable1 REBUILD PARTITION = 1
    WITH (DATA_COMPRESSION = NONE);
GO

Se per la stessa operazione viene utilizzata la sintassi alternativa seguente, vengono ricompilate tutte le partizioni della tabella.

Si applica a: SQL Server.

ALTER TABLE PartitionTable1 REBUILD PARTITION = ALL
    WITH (DATA_COMPRESSION = PAGE ON PARTITIONS (1));

Per altri esempi di compressione dei dati, vedere Compressione dei dati.

B. Modificare una tabella columnstore per modificare la compressione dell'archivio

Nell'esempio seguente viene compressa una partizione di tabella columnstore applicando un algoritmo di compressione aggiuntivo. Questa compressione riduce le dimensioni della tabella, ma aumenta il tempo necessario per l'archiviazione e il recupero. È utile per l'archiviazione o in situazioni in cui è richiesto uno spazio inferiore ed è possibile concedere più tempo per l'archiviazione e il recupero.

Si applica a: SQL Server 2014 (12.x) e versioni successive e database SQL di Azure.

ALTER TABLE PartitionTable1 REBUILD PARTITION = 1
    WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
GO

Nell'esempio seguente viene decompressa una partizione di tabella columnstore compressa con COLUMNSTORE_ARCHIVE l'opzione . Quando i dati vengono ripristinati, continuano a essere compressi con la compressione columnstore usata per tutte le tabelle columnstore.

Si applica a: SQL Server 2014 (12.x) e versioni successive e database SQL di Azure.

ALTER TABLE PartitionTable1 REBUILD PARTITION = 1
    WITH (DATA_COMPRESSION = COLUMNSTORE);
GO

C. Passare da una tabella all'altra

Nell'esempio seguente viene creata una tabella partizionata, presupponendo che nel database sia già stato creato lo schema di partizione myRangePS1. Verrà quindi creata una tabella non partizionata con la stessa struttura della tabella partizionata e nello stesso filegroup di PARTITION 2 della tabella PartitionTable. I dati di PARTITION 2 della tabella PartitionTable vengono quindi trasferiti nella tabella NonPartitionTable.

CREATE TABLE PartitionTable
(
    col1 INT,
    col2 CHAR (10)
) ON myRangePS1 (col1);
GO

CREATE TABLE NonPartitionTable
(
    col1 INT,
    col2 CHAR (10)
) ON test2fg;
GO

ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable;
GO

D. Consenti escalation blocchi nelle tabelle partizionate

Nell'esempio seguente viene abilitata l'escalation blocchi a livello di partizione in una tabella partizionata. Se la tabella non è partizionata, l'escalation dei blocchi viene impostata a TABLE livello.

Si applica a: SQL Server e database SQL di Azure.

ALTER TABLE dbo.T1 SET (LOCK_ESCALATION = AUTO);
GO

E. Configurare il rilevamento delle modifiche in una tabella

Nell'esempio seguente viene abilitato il rilevamento delle modifiche per la tabella Person.Person.

Si applica a: SQL Server e database SQL di Azure.

USE AdventureWorks2022;

ALTER TABLE Person.Person ENABLE CHANGE_TRACKING;

Nell'esempio seguente viene abilitato il rilevamento delle modifiche e il rilevamento delle colonne aggiornate durante una modifica.

Si applica a: SQL Server.

USE AdventureWorks2022;
GO

ALTER TABLE Person.Person ENABLE CHANGE_TRACKING
    WITH (TRACK_COLUMNS_UPDATED = ON);

Nell'esempio seguente viene disabilitato il rilevamento delle modifiche per la tabella Person.Person.

Si applica a: SQL Server e database SQL di Azure.

USE AdventureWorks2022;
GO

ALTER TABLE Person.Person DISABLE CHANGE_TRACKING;

Disabilitare e abilitare vincoli e trigger

A. Disabilitare e riabilitare un vincolo

Nell'esempio seguente viene disabilitato un vincolo che limita i dati relativi agli stipendi accettabili. NOCHECK CONSTRAINT viene utilizzata con ALTER TABLE per disabilitare il vincolo e consentire un inserimento che in genere violerebbe il vincolo. CHECK CONSTRAINT abilita nuovamente il vincolo.

CREATE TABLE dbo.cnst_example
(
    id INT NOT NULL,
    name VARCHAR (10) NOT NULL,
    salary MONEY NOT NULL
        CONSTRAINT salary_cap CHECK (salary < 100000)
);

-- Valid inserts
INSERT INTO dbo.cnst_example VALUES (1, 'Joe Brown', 65000);
INSERT INTO dbo.cnst_example VALUES (2, 'Mary Smith', 75000);

-- This insert violates the constraint.
INSERT INTO dbo.cnst_example VALUES (3, 'Pat Jones', 105000);

-- Disable the constraint and try again.
ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (3, 'Pat Jones', 105000);

-- Re-enable the constraint and try another insert; this fails.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (4, 'Eric James', 110000);

B. Disabilitare e riabilitare un trigger

Nell'esempio seguente viene utilizzata l'opzione DISABLE TRIGGER di ALTER TABLE per disabilitare il trigger e consentire un inserimento che altrimenti violerebbe il trigger. ENABLE TRIGGER viene quindi utilizzato per abilitare nuovamente il trigger.

CREATE TABLE dbo.trig_example
(
    id INT,
    name VARCHAR (12),
    salary MONEY
);
GO

-- Create the trigger.
CREATE TRIGGER dbo.trig1
    ON dbo.trig_example
    FOR INSERT
    AS IF (SELECT COUNT(*)
           FROM INSERTED
           WHERE salary > 100000) > 0
           BEGIN
               PRINT 'TRIG1 Error: you attempted to insert a salary > $100,000';
               ROLLBACK;
           END
GO

-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (1, 'Pat Smith', 100001);
GO

-- Disable the trigger.
ALTER TABLE dbo.trig_example DISABLE TRIGGER trig1;
GO

-- Try an insert that would typically violate the trigger.
INSERT INTO dbo.trig_example VALUES (2, 'Chuck Jones', 100001);
GO

-- Re-enable the trigger.
ALTER TABLE dbo.trig_example ENABLE TRIGGER trig1;
GO

-- Try an insert that violates the trigger.
INSERT INTO dbo.trig_example VALUES (3, 'Mary Booth', 100001);
GO

Operazioni online

A. Ricompilazione dell'indice online usando le opzioni di attesa con priorità bassa

L'esempio seguente illustra come eseguire la ricompilazione di un indice online specificando le opzioni di attesa con priorità bassa.

Si applica a: SQL Server 2014 (12.x) e versioni successive e database SQL di Azure.

ALTER TABLE T1 REBUILD WITH (
    PAD_INDEX = ON,
    ONLINE = ON (
        WAIT_AT_LOW_PRIORITY (MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)
    )
);

B. Modifica colonna online

Nell'esempio seguente viene illustrato come eseguire un'operazione di modifica colonna con l'opzione ONLINE .

Si applica a: SQL Server 2016 (13.x) e versioni successive e database SQL di Azure.

CREATE TABLE dbo.doc_exy (column_a INT);
GO

INSERT INTO dbo.doc_exy (column_a) VALUES (10);
GO

ALTER TABLE dbo.doc_exy
    ALTER COLUMN column_a DECIMAL (5, 2) WITH (ONLINE = ON);
GO

EXECUTE sp_help doc_exy;
DROP TABLE dbo.doc_exy;
GO

Controllo delle versioni del sistema

I quattro esempi seguenti consentono di acquisire familiarità con la sintassi per l'uso del controllo delle versioni di sistema. Per altre informazioni, vedere Introduzione alle tabelle temporali con controllo delle versioni di sistema.

Si applica a: SQL Server 2016 (13.x) e versioni successive e database SQL di Azure.

A. Aggiungere il controllo delle versioni di sistema a tabelle esistenti

Nell'esempio seguente viene illustrato come aggiungere il controllo delle versioni di sistema a una tabella esistente e creare una tabella di cronologia futura. In questo esempio si presuppone l'esistenza di una tabella denominata InsurancePolicy con una chiave primaria definita. In questo esempio si popolano le colonne periodo appena create per il controllo delle versioni del sistema usando valori predefiniti per l'ora di inizio e di fine in quanto questi valori non possono essere Null. In questo esempio viene utilizzata la HIDDEN clausola per garantire che non venga applicato alcun effetto alle applicazioni esistenti che interagiscono con la tabella corrente. Usa anche HISTORY_RETENTION_PERIOD che è disponibile solo nel database SQL.

--Alter non-temporal table to define periods for system versioning
ALTER TABLE InsurancePolicy
    ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
            DEFAULT SYSUTCDATETIME() NOT NULL,
        ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
            DEFAULT CONVERT (DATETIME2, '9999-12-31 23:59:59.99999999') NOT NULL,
        PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

--Enable system versioning with 1 year retention for historical data
ALTER TABLE InsurancePolicy SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_RETENTION_PERIOD=1 YEAR
    )
);

B. Eseguire la migrazione di una soluzione esistente per usare il controllo delle versioni di sistema

Nell'esempio seguente viene illustrato come eseguire la migrazione per il controllo delle versioni di sistema da una soluzione che usa i trigger per simulare il supporto temporale. Nell'esempio si presuppone che sia presente una soluzione esistente che usa una ProjectTask tabella e una ProjectTaskHistory tabella per la soluzione esistente, che usa le Changed Date colonne e Revised Date per i relativi periodi, che queste colonne di periodo non usano il tipo di dati datetime2 e che la ProjectTask tabella ha una chiave primaria definita.

-- Drop existing trigger
DROP TRIGGER ProjectTask_HistoryTrigger;

-- Adjust the schema for current and history table
-- Change data types for existing period columns
ALTER TABLE ProjectTask ALTER COLUMN [Changed Date] DATETIME2 NOT NULL;
ALTER TABLE ProjectTask ALTER COLUMN [Revised Date] DATETIME2 NOT NULL;
ALTER TABLE ProjectTaskHistory ALTER COLUMN [Changed Date] DATETIME2 NOT NULL;
ALTER TABLE ProjectTaskHistory ALTER COLUMN [Revised Date] DATETIME2 NOT NULL;

-- Add SYSTEM_TIME period and set system versioning with linking two existing tables
-- (a certain set of data checks happen in the background)
ALTER TABLE ProjectTask
    ADD PERIOD FOR SYSTEM_TIME ([Changed Date], [Revised Date]);

ALTER TABLE ProjectTask SET (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE=dbo.ProjectTaskHistory, DATA_CONSISTENCY_CHECK=ON
    )
);

C. Disabilitare e riabilitare il controllo delle versioni del sistema per modificare lo schema della tabella

In questo esempio viene illustrato come disabilitare il controllo delle versioni di sistema nella tabella Department, aggiungere una colonna e riabilitare il controllo delle versioni di sistema. Per modificare lo schema tabella, è necessario disabilitare il controllo delle versioni di sistema. Eseguire questi passaggi all'interno di una transazione per impedire l'aggiornamento di entrambe le tabelle durante l'aggiornamento dello schema tabella. In questo modo, gli amministratori di database non dovranno verificare la coerenza dei dati quando il controllo delle versioni di sistema viene riabilitato e si garantiscono prestazioni migliori. Per altre attività come la creazione delle statistiche, il cambio delle partizioni o l'applicazione della compressione a una o entrambe le tabelle, non è necessaria la disabilitazione del controllo delle versioni di sistema.

BEGIN TRAN
/* Takes schema lock on both tables */
ALTER TABLE Department
    SET (SYSTEM_VERSIONING = OFF) ;
/* expand table schema for temporal table */
ALTER TABLE Department
     ADD Col5 int DEFAULT NOT NULL 0 ;
/* Expand table schema for history table */
ALTER TABLE DepartmentHistory
    ADD Col5 int DEFAULT NOT NULL 0 ;
/* Re-establish versioning again*/
ALTER TABLE Department
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.DepartmentHistory,
                                 DATA_CONSISTENCY_CHECK = OFF)) ;
COMMIT

D. Rimuovere il controllo delle versioni di sistema

In questo esempio viene illustrato come rimuovere completamente il controllo delle versioni di sistema della tabella Department ed eliminare la tabella DepartmentHistory. Facoltativamente, è anche possibile eliminare le colonne periodo usate dal sistema per registrare informazioni sul controllo delle versioni di sistema. Non è possibile eliminare le tabelle Department o DepartmentHistory mentre il controllo delle versioni di sistema è abilitato.

ALTER TABLE Department
    SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE Department
    DROP PERIOD FOR SYSTEM_TIME;

DROP TABLE DepartmentHistory;

Esempi: Azure Synapse Analytics e Piattaforma di strumenti analitici (PDW)

Gli esempi seguenti da A a C usano la FactResellerSales tabella nel database AdventureWorksPDW2022 .

A. Determinare se una tabella è partizionata

Tramite la query seguente vengono restituite una o più righe se la tabella FactResellerSales è partizionata. Se la tabella non è partizionata, non viene restituita alcuna riga.

SELECT *
FROM sys.partitions AS p
     INNER JOIN sys.tables AS t
         ON p.object_id = t.object_id
WHERE p.partition_id IS NOT NULL
      AND t.name = 'FactResellerSales';

B. Determinare i valori limite per una tabella partizionata

Tramite la query seguente vengono restituiti i valori limite per ogni partizione nella tabella FactResellerSales .

SELECT t.name AS TableName,
       i.name AS IndexName,
       p.partition_number,
       p.partition_id,
       i.data_space_id,
       f.function_id,
       f.type_desc,
       r.boundary_id,
       r.value AS BoundaryValue
FROM sys.tables AS t
     INNER JOIN sys.indexes AS i
         ON t.object_id = i.object_id
     INNER JOIN sys.partitions AS p
         ON i.object_id = p.object_id
        AND i.index_id = p.index_id
     INNER JOIN sys.partition_schemes AS s
         ON i.data_space_id = s.data_space_id
     INNER JOIN sys.partition_functions AS f
         ON s.function_id = f.function_id
     LEFT OUTER JOIN sys.partition_range_values AS r
         ON f.function_id = r.function_id
        AND r.boundary_id = p.partition_number
WHERE t.name = 'FactResellerSales'
      AND i.type <= 1
ORDER BY p.partition_number;

C. Determinare la colonna di partizione per una tabella partizionata

La query seguente restituisce il nome della colonna di partizionamento per la FactResellerSales tabella.

SELECT t.object_id AS Object_ID,
       t.name AS TableName,
       ic.column_id AS PartitioningColumnID,
       c.name AS PartitioningColumnName
FROM sys.tables AS t
     INNER JOIN sys.indexes AS i
         ON t.object_id = i.object_id
     INNER JOIN sys.columns AS c
         ON t.object_id = c.object_id
     INNER JOIN sys.partition_schemes AS ps
         ON ps.data_space_id = i.data_space_id
     INNER JOIN sys.index_columns AS ic
         ON ic.object_id = i.object_id
        AND ic.index_id = i.index_id
        AND ic.partition_ordinal > 0
WHERE t.name = 'FactResellerSales'
      AND i.type <= 1
      AND c.column_id = ic.column_id;

D. Unire due partizioni

Nell'esempio seguente si uniscono due partizioni in una tabella.

La tabella Customer presenta la definizione seguente:

CREATE TABLE Customer
(
    id INT NOT NULL,
    lastName VARCHAR (20),
    orderCount INT,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderCount RANGE LEFT
        FOR VALUES (1, 5, 10, 25, 50, 100)
    )
);

Il comando seguente unisce i limiti delle partizioni 10 e 25.

ALTER TABLE Customer MERGE RANGE (10);

La nuova istruzione DDL per la tabella è la seguente:

CREATE TABLE Customer
(
    id INT NOT NULL,
    lastName VARCHAR (20),
    orderCount INT,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderCount RANGE LEFT
        FOR VALUES (1, 5, 25, 50, 100)
    )
);

E. Dividere una partizione

Nell'esempio seguente si suddivide una partizione in una tabella.

La tabella Customer presenta l'istruzione DDL seguente:

DROP TABLE Customer;

CREATE TABLE Customer
(
    id INT NOT NULL,
    lastName VARCHAR (20),
    orderCount INT,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderCount RANGE LEFT
        FOR VALUES (1, 5, 10, 25, 50, 100)
    )
);

Il comando seguente consente di creare una nuova partizione associata al valore 75, compresa tra 50 e 100.

ALTER TABLE Customer SPLIT RANGE (75);

La nuova istruzione DDL per la tabella è la seguente:

CREATE TABLE Customer (
   id INT NOT NULL,
   lastName VARCHAR(20),
   orderCount INT,
   orderDate DATE)
   WITH DISTRIBUTION = HASH(id),
   PARTITION ( orderCount (RANGE LEFT
      FOR VALUES (1, 5, 10, 25, 50, 75, 100))) ;

F. Usare SWITCH per spostare una partizione in una tabella di cronologia

Nell'esempio seguente si spostano i dati in una partizione della tabella Orders in una partizione della tabella OrdersHistory.

La tabella Orders presenta l'istruzione DDL seguente:

CREATE TABLE Orders
(
    id INT,
    city VARCHAR (25),
    lastUpdateDate DATE,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderDate RANGE RIGHT
        FOR VALUES ('2004-01-01', '2005-01-01', '2006-01-01', '2007-01-01')
    )
);

In questo esempio la tabella Orders contiene le partizioni seguenti. Ogni partizione contiene dati.

• Partizione 1 (con dati): OrderDate < '2004-01-01'
• Partizione 2 (con dati): '2004-01-01' <= OrderDate < '2005-01-01'
• Partizione 3 (con dati): '2005-01-01' <= OrderDate< '2006-01-01'
• Partizione 4 (con dati): '2006-01-01'<= OrderDate < '2007-01-01'
• Partizione 5 (con dati): '2007-01-01' <= OrderDate

La tabella OrdersHistory contiene l'istruzione DDL seguente, con colonne identiche e gli stessi nomi di colonna della tabella Orders. Sono entrambe tabelle hash distribuite nella colonna id.

CREATE TABLE OrdersHistory
(
    id INT,
    city VARCHAR (25),
    lastUpdateDate DATE,
    orderDate DATE
)
WITH (
    DISTRIBUTION = HASH(id),
    PARTITION(orderDate RANGE RIGHT
        FOR VALUES ('2004-01-01')
    )
);

Mentre le colonne e i nomi di colonna devono essere gli stessi, non è necessario che i limiti delle partizioni siano uguali. In questo esempio la tabella OrdersHistory contiene le due partizioni seguenti. Entrambe sono vuote:

• Partizione 1 (nessun dato): OrderDate < '2004-01-01'
• Partizione 2 (vuota): '2004-01-01' <= OrderDate

Per le due tabelle precedenti, il comando seguente sposta tutte le righe con OrderDate < '2004-01-01' dalla tabella Orders alla tabella OrdersHistory.

ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersHistory PARTITION 1;

Di conseguenza, la prima partizione in Orders è vuota e la prima partizione in OrdersHistory contiene dati. Le tabelle vengono visualizzate come indicato di seguito:

Tabella Orders

• Partizione 1 (vuota): OrderDate < '2004-01-01'
• Partizione 2 (con dati): '2004-01-01' <= OrderDate < '2005-01-01'
• Partizione 3 (con dati): '2005-01-01' <= OrderDate< '2006-01-01'
• Partizione 4 (con dati): '2006-01-01'<= OrderDate < '2007-01-01'
• Partizione 5 (con dati): '2007-01-01' <= OrderDate

Tabella OrdersHistory

• Partizione 1 (con dati): OrderDate < '2004-01-01'
• Partizione 2 (vuota): '2004-01-01' <= OrderDate

Per pulire la Orders tabella, è possibile rimuovere la partizione vuota unendo le partizioni 1 e 2 come indicato di seguito:

ALTER TABLE Orders MERGE RANGE ('2004-01-01');

Dopo l'unione, la tabella Orders conterrà le partizioni seguenti:

Tabella Orders

• Partizione 1 (con dati): OrderDate < '2005-01-01'
• Partizione 2 (con dati): '2005-01-01' <= OrderDate< '2006-01-01'
• Partizione 3 (con dati): '2006-01-01'<= OrderDate < '2007-01-01'
• Partizione 4 (con dati): '2007-01-01' <= OrderDate

Si supponga che sia passato un anno e che si voglia archiviare l'anno 2005. È possibile allocare una partizione vuota per l'anno 2005 nella tabella OrdersHistory suddividendo la partizione vuota come indicato di seguito:

ALTER TABLE OrdersHistory SPLIT RANGE ('2005-01-01');

Dopo la suddivisione, la tabella OrdersHistory conterrà le partizioni seguenti:

Tabella OrdersHistory

• Partizione 1 (con dati): OrderDate < '2004-01-01'
• Partizione 2 (vuota): '2004-01-01' < '2005-01-01'
• Partizione 3 (vuota): '2005-01-01' <= OrderDate

Contenuti correlati

• sys.tables
• sp_rename
• sp_help
• EVENTDATA (Transact-SQL)
• CREATE TABLE (Transact-SQL)
• DROP TABLE (Transact-SQL)
• ALTER TABLE column_constraint (Transact-SQL)
• ALTER TABLE column_definition (Transact-SQL)
• ALTER TABLE computed_column_definition (Transact-SQL)
• ALTER TABLE index_option (Transact-SQL)
• ALTER TABLE table_constraint (Transact-SQL)