ALTER TABLE (Transact-SQL)

Data aggiornamento: 15 settembre 2007

Modifica una definizione di tabella mediante la modifica, l'aggiunta o l'eliminazione di colonne e vincoli, la riassegnazione di partizioni, la disattivazione o l'attivazione di vincoli e trigger.

Convenzioni della sintassi Transact-SQL

Sintassi

ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name 
{ 
    ALTER COLUMN column_name 
    { 
        [ type_schema_name. ] type_name [ ( { precision [ , scale ] 
            | max | xml_schema_collection } ) ] 
        [ COLLATE collation_name ] 
        [ NULL | NOT NULL ] 
    | {ADD | DROP } { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION}
    } 
    | [ WITH { CHECK | NOCHECK } ] ADD 
    { 
        <column_definition>
      | <computed_column_definition>
      | <table_constraint> 
    } [ ,...n ]
    | DROP 
    { 
        [ CONSTRAINT ] constraint_name 
        [ WITH ( <drop_clustered_constraint_option> [ ,...n ] ) ]
        | COLUMN column_name 
    } [ ,...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 ]
}
[ ; ]

<drop_clustered_constraint_option> ::=  
    { 
        MAXDOP = max_degree_of_parallelism
      | ONLINE = {ON | OFF }
      | MOVE TO { partition_scheme_name ( column_name ) | filegroup
          | "default"}
    }

Argomenti

• 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 che si desidera 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. ALTER COLUMN non è consentita se il livello di compatibilità è inferiore o uguale a 65. Per ulteriori informazioni, vedere sp_dbcmptlevel (Transact-SQL).

  Non è consentita la modifica delle colonne seguenti:

  • Colonne con tipo di dati timestamp.
  • Colonna ROWGUIDCOL della tabella.
  • Colonne calcolate o utilizzate in una colonna calcolata.
  • Colonne utilizzate in un indice, a meno che la colonna non sia un tipo di dati varchar, nvarchar o il tipo di dati varbinary, il tipo di dati non sia cambiato e le nuove dimensioni non siano minori o superiori a quelle delle dimensioni precedenti e l'indice non è il risultato di un vincolo PRIMARY KEY.
  • Colonne utilizzate in statistiche generate mediante l'istruzione CREATE STATISTICS. È innanzitutto necessario rimuovere le statistiche utilizzando l'istruzione DROP STATISTICS. Le statistiche generate in modo automatico da Query Optimizer vengono eliminate automaticamente da ALTER COLUMN.
  • Colonne utilizzate in un vincolo PRIMARY KEY o [FOREIGN KEY] REFERENCES.
  • Colonne utilizzate in un vincolo CHECK o UNIQUE. È tuttavia possibile modificare la lunghezza di una colonna a lunghezza variabile utilizzata in un vincolo CHECK o UNIQUE.
  • 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:
    • text in varchar(max), nvarchar(max) o xml
    • ntext in varchar(max), nvarchar(max) o xml
    • image in varbinary(max)
      Alcune modifiche del tipo di dati possono comportare la modifica dei dati. La sostituzione, ad esempio, del tipo di dati nchar o nvarchar di una colonna in char o varchar può causare la conversione di caratteri estesi. Per ulteriori informazioni, vedere CAST e CONVERT (Transact-SQL). La riduzione della precisione o della scala di una colonna può causare il troncamento dei dati.
      Non è possibile modificare il tipo di dati di una colonna di una tabella partizionata.

• column_name
  Nome della colonna che si desidera modificare, aggiungere o eliminare. column_name può essere composto da un massimo di 128 caratteri. Nel caso di nuove colonne create con il tipo di dati timestamp, è possibile omettere column_name. Se non viene specificato alcun column_name per una colonna di tipo timestamp, viene utilizzato il nome 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 le colonne esistenti di tabelle partizionate. type_name può appartenere a uno dei tipi seguenti:

  • Tipo di dati di sistema di SQL Server 2005.
  • Tipo di dati alias basato su un tipo di dati di sistema di SQL Server. Per consentirne l'utilizzo in una definizione di tabella, i tipi di dati alias vengono creati con l'istruzione CREATE TYPE.
  • Tipo di dati definito dall'utente di .NET Framework e schema al quale il tipo di dati appartiene. Per consentirne l'utilizzo in una definizione di tabella, i tipi di dati definiti dall'utente di .NET Framework vengono creati con l'istruzione CREATE TYPE.

  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 di tipo timestamp.
  • I valori predefiniti di ANSI_NULL sono sempre attivi per ALTER COLUMN. Se non diversamente specificato, la colonna supporta valori Null.
  • Il riempimento con ANSI_PADDING è sempre attivo 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 di SET ARITHABORT viene ignorata. Il funzionamento di ALTER TABLE presume l'impostazione di ARITHABORT su ON.

  [!NOTA] Se la clausola COLLATE è omessa, la modifica del tipo di dati di una colonna causerà la modifica delle regole di confronto predefinite del database.

• precision
  Precisione del tipo di dati specificato. Per ulteriori informazioni sui valori di precisione validi, vedere Precisione, scala e lunghezza (Transact-SQL).

• scale
  Scala per il tipo di dati specificato. Per ulteriori informazioni sui valori di scala validi, vedere Precisione, scala e lunghezza (Transact-SQL).

• 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
  Viene applicato solo al tipo di dati xml per l'associazione di uno schema XML con il tipo. Prima di tipizzare una colonna xml in un insieme di schemi, è necessario creare l'insieme nel database utilizzando 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 utilizzare nomi di regole di confronto di Windows o SQL. Per un elenco e ulteriori informazioni, vedere Windows_collation_name (Transact-SQL) e SQL_collation_name (Transact-SQL).

  La clausola COLLATE consente di modificare le regole di confronto solo delle colonne di tipo char, varchar, nchar e nvarchar. Per modificare le regole di confronto di una colonna con un tipo di dati alias definito dall'utente, è necessario eseguire istruzioni ALTER TABLE separate in modo da modificare il tipo di dati della colonna in un tipo di dati di sistema di SQL Server e le relative regole di confronto. Si dovrà quindi ripristinare un tipo di dati alias per la colonna.

  Non è possibile specificare una modifica delle regole di confronto per ALTER COLUMN se si verifica una delle condizioni seguenti:

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

  Per ulteriori informazioni, vedere COLLATE (Transact-SQL).

• NULL | NOT NULL
  Specifica se la colonna consente valori Null. L'istruzione ALTER TABLE consente di aggiungere colonne che non consentono valori Null solo se alle colonne è associato un valore predefinito oppure se la tabella è vuota. È possibile specificare NOT NULL per le colonne calcolate solo se è specificato PERSISTED. 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 a una nuova colonna che consente valori Null viene aggiunta una definizione DEFAULT, è possibile utilizzare WITH VALUES per l'archiviazione del valore predefinito nella nuova colonna per ogni riga della tabella.

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

  È possibile specificare NULL in ALTER COLUMN per forzare l'utilizzo di valori Null nelle colonne NOT NULL, ad eccezione delle colonne nei vincoli PRIMARY KEY. È possibile specificare NOT NULL in ALTER COLUMN solo se la colonna non contiene valori Null. Per utilizzare ALTER COLUMN NOT NULL, è necessario aggiornare i valori Null con un valore specifico, 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 mediante un'istruzione CREATE TABLE o ALTER TABLE, le impostazioni del database e della sessione influiscono sull'impostazione che consente l'utilizzo dei valori Null del tipo di dati utilizzato in una definizione di colonna. In questo caso, tale impostazione può essere sostituita. È consigliabile definire sempre in modo esplicito come NULL o NOT NULL le colonne non calcolate oppure, se si utilizza un tipo di dati definito dall'utente, è consigliabile consentire l'utilizzo dell'impostazione predefinita che consente l'utilizzo dei valori Null per tale tipo di dati. Per ulteriori informazioni, vedere CREATE TABLE (Transact-SQL).

  [!NOTA] Se si specifica NULL o NOT NULL con ALTER COLUMN, è necessario inoltre specificare new_data_type [(precision [, scale ])]. Se il tipo di dati, la precisione e la scala non vengono modificati, specificare i valori correnti della colonna.

• [ {ADD | DROP} ROWGUIDCOL ]
  Specifica l'aggiunta o l'eliminazione della proprietà ROWGUIDCOL dalla colonna specificata. ROWGUIDCOL indica che la colonna è di tipo rowguid. È possibile designare come colonna ROWGUIDCOL una sola colonna uniqueidentifier per tabella e assegnare la proprietà ROWGUIDCOL a una sola colonna uniqueidentifier. Non è possibile assegnare ROWGUIDCOL a una colonna con un tipo di dati definito dall'utente.

  ROWGUIDCOL non impone l'unicità dei valori archiviati nella colonna e non genera automaticamente valori per le nuove righe inserite nella tabella. Per generare valori univoci per ogni colonna, è necessario utilizzare la funzione NEWID con istruzioni INSERT o specificare la funzione NEWID come valore predefinito della colonna.

• [ {ADD | DROP} PERSISTED ]
  Specifica l'aggiunta o l'eliminazione della proprietà PERSISTED dalla colonna specificata. La colonna interessata deve essere una colonna calcolata definita con un'espressione deterministica. Per le colonne specificate come PERSISTED, Motore di database di SQL Server 2005 archivia fisicamente i valori calcolati nella tabella e aggiorna i valori durante l'aggiornamento delle altre colonne da cui le colonne calcolate dipendono. Se si contrassegna una colonna calcolata come PERSISTED, è possibile creare indici in colonne calcolate definite in base a espressioni deterministiche ma imprecise. Per ulteriori informazioni, vedere Creazione di indici per le colonne calcolate.

  Tutte le colonne calcolate utilizzate come colonne di partizionamento di tabelle partizionate devono essere contrassegnate come PERSISTED in modo esplicito.

• NOT FOR REPLICATION
  Specifica che i valori non vengono incrementali nelle colonne Identity quando gli agenti di replica eseguono operazioni di inserimento. Questa clausola può essere specificata solo se column_name corrisponde a una colonna Identity. Per ulteriori informazioni, vedere Controllo di vincoli, identità e trigger con l'opzione NOT FOR REPLICATION.

• WITH CHECK | WITH NOCHECK
  Specifica se i dati nella tabella vengono convalidati in base a un vincolo FOREIGN KEY o CHECK nuovo o riattivato. Se viene omesso, viene utilizzata la clausola WITH CHECK per nuovi vincoli e WITH NOCHECK per vincoli riattivati.

  Se non si desidera verificare nuovi vincoli CHECK o FOREIGN KEY in base ai dati esistenti, utilizzare WITH NOCHECK. È tuttavia consigliabile effettuare questa scelta solo in casi rari. Il nuovo vincolo viene valutato in tutti gli aggiornamenti successivi dei dati. Le eventuali violazioni del vincolo soppresse da WITH NOCHECK quando si aggiunge il vincolo possono causare il mancato completamento dei successivi aggiornamenti di righe contenenti dati che violano il vincolo.

  Query Optimizer non considera i vincoli definiti con WITH NOCHECK, i quali vengono ignorati finché non vengono riattivati mediante ALTER TABLE table CHECK CONSTRAINT ALL.

• ADD
  Specifica l'aggiunta di una o più definizioni di colonna, definizioni di colonna calcolata o vincoli di tabella.

• DROP { [ CONSTRAINT ] constraint_name | COLUMN column_name }
  Imposta la rimozione di constraint_name o column_name dalla tabella. È possibile elencare più colonne e vincoli. DROP COLUMN non è consentita se il livello di compatibilità è minore o uguale a 65. Per ulteriori informazioni, vedere sp_dbcmptlevel (Transact-SQL).

  Il nome del vincolo definito dall'utente o di sistema può essere determinato mediante l'esecuzione di una query sulle viste del catalogo sys.check_constraint, sys.default_constraints, sys.key_constraints e sys.foreign_keys.

  Se nella tabella è presente un indice XML, non è possibile eliminare un vincolo PRIMARY KEY.

  Non è possibile eliminare una colonna se:

  • Viene utilizzata in un indice.
  • Viene utilizzata in un vincolo CHECK, FOREIGN KEY, UNIQUE o PRIMARY KEY.
  • È associata a un valore predefinito creato con la parola chiave DEFAULT o a un oggetto predefinito.
  • È associata a una regola.

  [!NOTA] L'eliminazione di una colonna non consente di recuperare lo spazio su disco corrispondente. Può essere necessario recuperare lo spazio su disco di una colonna rimossa quando le dimensioni delle righe della tabella sono prossime al limite o lo hanno superato. Per recuperare spazio, creare in indice cluster nella tabella o ricostruire un indice cluster esistente utilizzando ALTER INDEX.

• WITH <drop_clustered_constraint_option>
  Specifica l'impostazione di una o più opzioni di eliminazione dei vincoli cluster.

• MAXDOP = max_degree_of_parallelism
  Consente di ignorare l'opzione di configurazione Max Degree of Parallelism solo per la durata dell'operazione. Per ulteriori informazioni, vedere Opzione max degree of parallelism.

  L'opzione MAXDOP consente di limitare il numero di processori utilizzati per l'esecuzione di piani paralleli. Il valore massimo è 64 processori.

  I possibili valori di max_degree_of_parallelism sono i 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 (predefinito)
    Utilizza il numero effettivo di processori o un numero inferiore in base al carico di lavoro corrente del sistema.

  Per ulteriori informazioni, vedere Configurazione di operazioni a indici paralleli.

  [!NOTA] Le operazioni parallele sugli indici sono supportate solo in SQL Server 2005 Enterprise Edition.

• ONLINE = { ON | OFF }
  Specifica se le tabelle sottostanti e gli indici associati sono disponibili per le query e per modifiche dei dati durante l'operazione sull'indice. Il valore predefinito è OFF.

  • ON
    I blocchi di lunga durata a livello di tabella non vengono mantenuti per la durata di un'operazione sugli indici. Durante la fase principale dell'operazione viene mantenuto solo un blocco preventivo condiviso (IS, Intent Shared) sulla tabella di origine, in modo da consentire 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 periodo molto breve. Al termine dell'operazione di creazione di un indice non cluster, per un breve periodo viene acquisito un blocco condiviso (S) sull'origine. Al termine dell'operazione di creazione o di eliminazione di un indice cluster in linea o di ricostruzione di un indice cluster o non cluster, viene acquisito un blocco di modifica dello schema (SCH-M). Durante la creazione di un indice per una tabella temporanea locale non è possibile impostare ONLINE su ON.
  • OFF
    I blocchi a livello di tabella vengono applicati per la durata dell'operazione sugli indici. Un'operazione sugli indici non in linea che crea, ricostruisce o elimina un indice cluster oppure ricostruisce o elimina un indice non cluster acquisisce un blocco di modifica dello schema (SCH-M) sulla tabella. Tale blocco impedisce agli utenti di accedere alla tabella sottostante per la durata dell'operazione. Un'operazione sugli indici non in linea che crea un indice non cluster acquisisce un blocco condiviso (S) sulla tabella. Tale blocco impedisce l'aggiornamento della tabella sottostante ma consente operazioni di lettura, ad esempio l'esecuzione di istruzioni SELECT.

  Per ulteriori informazioni, vedere Funzionamento delle operazioni sugli indici in linea. Per ulteriori informazioni sui blocchi, vedere Modalità blocco.

  [!NOTA] Le operazioni sugli indici in linea sono disponibili solo in SQL Server 2005 Enterprise Edition.

• MOVE TO ( partition_scheme_name ( column_name [ 1**,** ... n] ) | filegroup | "default"}
  Specifica la posizione in cui spostare le righe di dati attualmente al livello foglia dell'indice cluster. La tabella viene spostata nella nuova posizione.

  [!NOTA] In questo contesto, default non è una parola chiave, ma un identificatore per il filegroup predefinito e deve essere delimitato, come in MOVE TO "default" o MOVE TO [default]. Se si specifica "default", l'opzione QUOTED_IDENTIFIER deve essere impostata su ON per la sessione corrente. Questa è l'impostazione predefinita. Per ulteriori informazioni, vedere SET QUOTED_IDENTIFIER (Transact-SQL).

• { CHECK | NOCHECK } CONSTRAINT
  Specifica l'attivazione o la disattivazione di constraint_name. È possibile utilizzare questa opzione solo con vincoli FOREIGN KEY e CHECK. Quando si specifica NOCHECK, il vincolo viene disattivato e gli inserimenti o gli aggiornamenti successivi della colonna non vengono convalidati in base alle condizioni del vincolo. I vincoli DEFAULT, PRIMARY KEY e UNIQUE non possono essere disattivati.

• ALL
  Specifica che tutti i vincoli sono disattivati con l'opzione NOCHECK o attivati con l'opzione CHECK.

• { ENABLE | DISABLE } TRIGGER
  Specifica l'attivazione o la disattivazione di trigger_name. Un trigger disattivato è comunque disponibile nella tabella. Quando si esegue un'istruzione INSERT, UPDATE o DELETE sulla tabella, tuttavia, le azioni nel trigger vengono eseguite solo dopo la riattivazione del trigger stesso.

• ALL
  Specifica l'attivazione o la disattivazione di tutti i trigger della tabella.

• trigger_name
  Specifica il nome del trigger da attivare o disattivare.

• SWITCH [ PARTITION source_partition_number_expression ] TO target_table [ PARTITION target_ partition_number_expression ]
  Trasferisce un blocco di dati in uno di 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. Se 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.

  Se si riassegnano i dati di una partizione per formare un'unica tabella, la tabella di destinazione deve essere già stata creata 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. Per ulteriori informazioni, vedere Trasferimento efficiente dei dati mediante lo spostamento di partizioni. table e target_table non possono essere uguali. 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 e funzioni definite dall'utente. Non possono fare riferimento a espressioni Transact-SQL.

  [!NOTA] Non è possibile utilizzare l'istruzione SWITCH nelle tabelle replicate.

Osservazioni

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

Se la cache delle procedure include piani di esecuzione che fanno riferimento alla tabella, l'istruzione ALTER TABLE li contrassegna per la ricompilazione durante l'esecuzione successiva.

Modifica delle dimensioni di una colonna

È possibile modificare la lunghezza, la precisione o il ridimensionamento di una colonna specificando una nuova dimensione per il tipo di dati di colonna nella clausola ALTER COLUMN. Se i dati sono presenti nella colonna, la dimensione nuova non può essere più piccola della lunghezza massima dei dati. Inoltre, la colonna non può essere definita in un indice, a meno che la colonna sia un varchar, nvarchar o un tipo di dati varbinary e l'indice non è il risultato di un vincolo PRIMARY KEY. Vedere l'esempio P.

Blocchi e ALTER TABLE

Le modifiche specificate in ALTER TABLE vengono implementate immediatamente. Se le modifiche richiedono l'alterazione delle righe nella tabella, le righe vengono aggiornate tramite ALTER TABLE. ALTER TABLE acquisisce un blocco di modifica dello schema (SCH-M) sulla tabella per verificare che durante la modifica nessun'altra connessione faccia riferimento ai dati o ai metadati della tabella, ad eccezione delle operazioni sugli indici al termine delle quali è richiesto un blocco SCH-M molto breve. In un'operazione ALTER TABLE…SWITCH il blocco viene acquisito sia sulle tabelle di origine che su quelle di destinazione. Le modifiche apportate alla tabella vengono registrate e possono essere recuperate completamente. Le modifiche che influiscono su tutte le righe di tabelle di grandi dimensioni, ad esempio l'eliminazione di una colonna o l'aggiunta di una colonna NOT NULL con valore predefinito, possono richiedere molto tempo e generare un elevato numero di record del log. Tali istruzioni ALTER TABLE devono essere eseguite con la stessa attenzione dedicata alle istruzioni INSERT, UPDATE e DELETE quando queste influiscono su molte righe.

Esecuzione di piani paralleli

In SQL Server 2005 Enterprise Edition il numero di processori utilizzati per eseguire un'unica istruzione ALTER TABLE ADD (basata su indici) CONSTRAINT o DROP (indice cluster) CONSTRAINT viene determinato dall'opzione di configurazione Max Degree of Parallelism e dal carico di lavoro corrente. Se 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 utilizzati per eseguire l'istruzione mediante l'opzione MAXDOP.

Tabelle partizionate

Oltre all'esecuzione di operazioni SWITCH che interessano tabelle partizionate, è possibile utilizzare ALTER TABLE per modificare lo stato di colonne, vincoli e trigger di tali tabelle così come per le tabelle non partizionate. Non è tuttavia possibile utilizzare questa istruzione per modificare il modo di partizione della tabella stessa. Per ripartizionare una tabella partizionata, utilizzare ALTER PARTITION SCHEME e ALTER PARTITION FUNCTION. Non è inoltre possibile modificare il tipo di dati di una colonna di una tabella partizionata.

Restrizioni per le tabelle con viste associate a schema

Le restrizioni che si applicano a istruzioni ALTER TABLE eseguite su tabelle con viste associate a schema sono le stesse che vengono applicate alla 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 schema. Se l'istruzione ALTER TABLE richiede la modifica di una colonna utilizzata in una vista associata allo schema, ALTER TABLE ha esito negativo e Motore di database genera un messaggio di errore. Per ulteriori informazioni sull'associazione di schema e sulle viste indicizzate, vedere CREATE VIEW (Transact-SQL).

La creazione di una vista associata a 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 mediante CREATE INDEX devono essere eliminati mediante DROP INDEX. È possibile utilizzare l'istruzione ALTER INDEX per ricostruire un indice che costituisce una parte di una definizione di vincolo. Non è necessario eliminare e quindi aggiungere nuovamente il vincolo 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 nel livello foglia dell'indice cluster vengono archiviate in una tabella non cluster. In SQL Server 2005 è possibile eliminare l'indice cluster e spostare la tabella risultante in un altro filegroup o in un altro schema di partizione con un'unica transazione specificando l'opzione MOVE TO. Per l'opzione MOVE TO vengono applicate le seguenti restrizioni:

• MOVE TO non può essere utilizzata per viste indicizzate o indici non cluster.
• Lo schema di partizione o il filegroup deve essere già esistente.
• Se non si specifica MOVE TO, la tabella viene inserita nello stesso schema di partizione o nello stesso filegroup definito per l'indice cluster.

Quando si elimina un indice cluster, è possibile specificare l'opzione ONLINE = ON per evitare che la transazione DROP INDEX blocchi l'esecuzione di query e modifiche sui dati sottostanti e gli indici non cluster associati.

Per l'opzione ONLINE = ON vengono applicate le seguenti restrizioni:

• ONLINE = ON non è valida per gli indici cluster che sono inoltre disabilitati. Per l'eliminazione degli indici disabilitati è necessario utilizzare ONLINE = OFF.
• È possibile eliminare un solo indice alla volta.
• ONLINE = ON non è valida per viste indicizzate, indici non cluster o indici su tabelle temporanee locali.

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.

[!NOTA] Le opzioni elencate in <drop_clustered_constraint_option> si applicano a indici cluster su tabelle e non possono essere applicate a indici cluster su viste o a indici non cluster.

Replica delle modifiche dello schema

Per impostazione predefinita, quando si esegue ALTER TABLE su una tabella pubblicata in un server di pubblicazione SQL Server tale modifica viene propagata a tutti i Sottoscrittori SQL Server. Questa funzionalità presenta alcune restrizioni e può essere disattivata. Per ulteriori informazioni, vedere Modifiche allo schema nei database di pubblicazione.

Autorizzazioni

È necessaria l'autorizzazione ALTER per la tabella.

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

Se nell'istruzione ALTER TABLE si definiscono colonne di tipo Common Language Runtime (CLR) definito dall'utente o di tipo alias, è necessaria l'autorizzazione REFERENCES per il tipo desiderato.

Esempi

A. Aggiunta di una nuova colonna

Nell'esempio seguente viene aggiunta una colonna che consente valori Null e alla quale non sono associati valori mediante una definizione DEFAULT. In ogni riga della nuova colonna sarà indicato NULL.

CREATE TABLE dbo.doc_exa (column_a INT) ;
GO
ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ;
GO
EXEC sp_help doc_exa ;
GO
DROP TABLE dbo.doc_exa ;
GO

B. Eliminazione di una colonna

Nell'esempio seguente viene modificata una tabella mediante la rimozione di una colonna.

CREATE TABLE dbo.doc_exb (column_a INT, column_b VARCHAR(20) NULL) ;
GO
ALTER TABLE dbo.doc_exb DROP COLUMN column_b ;
GO
EXEC sp_help doc_exb ;
GO
DROP TABLE dbo.doc_exb ;
GO

C. Modifica del 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

D. Aggiunta di 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
EXEC sp_help doc_exc ;
GO
DROP TABLE dbo.doc_exc ;
GO

E. Aggiunta di 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
EXEC sp_help doc_exd ;
GO
DROP TABLE dbo.doc_exd ;
GO

F. Aggiunta di 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 mentre i valori nell'altra colonna rimangono 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

G. Aggiunta di più 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 

-- Add a PRIMARY KEY identity column.
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 
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
EXEC sp_help doc_exe ;
GO
DROP TABLE dbo.doc_exe ;
GO

H. Aggiunta di una colonna che supporta valori Null con valori predefiniti

Nell'esempio seguente viene aggiunta una colonna che supporta valori Null con una definizione DEFAULT e viene specificato WITH VALUES per l'assegnazione di valori a ogni riga della tabella. Se non si utilizza WITH VALUES, a ogni riga della nuova colonna viene associato il valore NULL.

USE AdventureWorks ; 
GO
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 NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
DROP TABLE dbo.doc_exf ;
GO

I. Disattivazione e riattivazione di un vincolo

Nell'esempio seguente viene disattivato un vincolo che limita i dati relativi agli stipendi accettabili. Viene inoltre utilizzato NOCHECK CONSTRAINT con ALTER TABLE per disattivare il vincolo e consentire un inserimento che altrimenti violerebbe il vincolo. CHECK CONSTRAINT riattiva 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 will fail.
ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap;
INSERT INTO dbo.cnst_example VALUES (4,'Eric James',110000) ;

J. Eliminazione di un vincolo

Nell'esempio seguente viene rimosso un vincolo UNIQUE da una tabella.

CREATE TABLE dbo.doc_exc ( column_a INT
CONSTRAINT my_constraint UNIQUE) ;
GO
ALTER TABLE dbo.doc_exc DROP CONSTRAINT my_constraint ;
GO
DROP TABLE dbo.doc_exc ;
GO

K. Trasferimento di partizioni tra tabelle

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

L. Disattivazione e riattivazione di un trigger

Nell'esempio seguente viene utilizzata l'opzione DISABLE TRIGGER di ALTER TABLE per disattivare il trigger e consentire un inserimento che altrimenti violerebbe il trigger. Per riattivare il trigger viene quindi utilizzato ENABLE 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 TRANSACTION
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

M. Creazione di un vincolo PRIMARY KEY con opzioni per gli indici

Nell'esempio seguente viene creato il vincolo PRIMARY KEY PK_TransactionHistoryArchive_TransactionID e vengono impostate le opzioni FILLFACTOR, ONLINE e PAD_INDEX. All'indice cluster risultante sarà assegnato lo stesso nome del vincolo.

USE AdventureWorks;
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

N. Eliminazione di un vincolo PRIMARY KEY nella modalità ONLINE

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

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

O. Aggiunta e rimozione di un vincolo FOREIGN KEY

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

USE AdventureWorks ;
GO
CREATE TABLE Person.ContactBackup
(ContactID int) ;
GO
ALTER TABLE Person.ContactBackup
ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID)
    REFERENCES Person.Contact (ContactID) ;
ALTER TABLE Person.ContactBackup
DROP CONSTRAINT FK_ContactBacup_Contact ;
GO
DROP TABLE Person.ContactBackup ;

P. Modifica delle dimensioni di una colonna

Il seguente esempio incrementa le dimensioni di una colonna varchar e la precisione e il ridimensionamento di una colonna decimal. Dal momento che le colonne contengono dati, le dimensioni della colonna possono essere solo incrementate. Notare inoltre che col_a è definito in un indice univoco. Le dimensioni di col_a possono ancora essere incrementate perché il tipo di dati è un varchar e l'indice non è il risultato di un vincolo PRIMARY KEY.

IF OBJECT_ID ( 'dbo.doc_exy', 'U' ) IS NOT NULL 
    DROP TABLE dbo.doc_exy;
GO
-- 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');

Vedere anche

Riferimento

sp_rename (Transact-SQL)
CREATE TABLE (Transact-SQL)
DROP TABLE (Transact-SQL)
sp_help (Transact-SQL)
ALTER PARTITION SCHEME (Transact-SQL)
ALTER PARTITION FUNCTION (Transact-SQL)
EVENTDATA (Transact-SQL)

Altre risorse

Creazione e modifica di tabelle
Modifiche allo schema nei database di pubblicazione

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche

Versione	Cronologia
15 settembre 2007	Contenuto modificato: Aggiunta della sezione, 'Modifica delle dimensioni di una colonna' ed Esempio P.
12 dicembre 2006	Contenuto modificato: Chiarimento relativo alla posizione e al significato della clausola NOT FOR REPLICATION nelle sezioni della sintassi e degli argomenti. Chiarimento del fatto che la tabella di destinazione di una clausola SWITCH può essere espressa come identificatore in più parti.
14 aprile 2006	Nuovo contenuto: Aggiunta della spiegazione che non è possibile utilizzare l'istruzione SWITCH nelle tabelle replicate.
5 dicembre 2005	Nuovo contenuto: Aggiunta della clausola DROP NOT FOR REPLICATION al diagramma della sintassi e all'elenco delle definizioni degli argomenti. Contenuto modificato: Inserimento della clausola COLLATE nella posizione corretta nel diagramma della sintassi. Correzione degli esempi M e N.