ALTER DATABASE (Transact-SQL)

 

QUESTO ARGOMENTO SI APPLICA A:sìSQL Server (a partire dalla versione 2008)noDatabase SQL di AzurenoAzure SQL Data WarehousenoParallel Data Warehouse

Consente di modificare un database oppure i file e i filegroup associati al database. Consente di aggiungere o rimuovere file e filegroup in un database, modificare gli attributi di un database oppure dei relativi file e filegroup, modificare le regole di confronto e impostare le opzioni del database. Non è possibile modificare snapshot di database. Per la modifica delle opzioni di database associate alla replica, usare sp_replicationdboption.

Si applica a: SQL Server (da SQL Server 2008 fino alla versione corrente), Database SQL di Azure.

A causa della lunghezza, la sintassi di ALTER DATABASE è separata negli argomenti seguenti:

ALTER DATABASE
L'argomento corrente fornisce la sintassi per la modifica del nome e le regole di confronto di un database.

Opzioni file e filegroup ALTER DATABASE
Fornisce la sintassi per l'aggiunta e la rimozione di file e filegroup da un database e per la modifica degli attributi di file e filegroup.

Opzioni ALTER DATABASE SET
Fornisce la sintassi per la modifica degli attributi di un database utilizzando le opzioni SET di ALTER DATABASE.

Mirroring del database ALTER DATABASE
Include la sintassi per le opzioni SET di ALTER DATABASE relative al mirroring del database.

ALTER DATABASE SET HADR
Fornisce la sintassi per le opzioni Gruppi di disponibilità AlwaysOn di ALTER DATABASE per la configurazione di un database secondario in una replica di disponibilità secondaria di un gruppo AlwaysOn.

Livello di compatibilità di ALTER DATABASE
Fornisce la sintassi per le opzioni SET di ALTER DATABASE relative ai livelli di compatibilità del database.

Topic link icon Convenzioni della sintassi Transact-SQL

  
      -- SQL Server Syntax  
ALTER DATABASE { database_name  | CURRENT }  
{  
    MODIFY NAME = new_database_name   
  | COLLATE collation_name  
  | <file_and_filegroup_options>  
  | <set_database_options>  
}  
[;]  
  
<file_and_filegroup_options >::=  
  <add_or_modify_files>::=  
  <filespec>::=   
  <add_or_modify_filegroups>::=  
  <filegroup_updatability_option>::=  
  
<set_database_options>::=  
  <optionspec>::=   
  <auto_option> ::=   
  <change_tracking_option> ::=  
  <cursor_option> ::=   
  <database_mirroring_option> ::=   
  <date_correlation_optimization_option> ::=  
  <db_encryption_option> ::=  
  <db_state_option> ::=  
  <db_update_option> ::=  
  <db_user_access_option> ::=  
  <delayed_durability_option> ::=  <external_access_option> ::=  
  <FILESTREAM_options> ::=  
  <HADR_options> ::=    
  <parameterization_option> ::=  
  <recovery_option> ::=   
  <service_broker_option> ::=  
  <snapshot_option> ::=  
  <sql_option> ::=   
  <termination> ::=  

  
      -- Azure SQL Database Syntax  
ALTER DATABASE database_name   
{  
    MODIFY NAME =new_database_name  
  | MODIFY ( <edition_options> [, ... n] )   
  | COLLATE collation_name  
  | SET { <set_database_options> }   
}  
  
<edition_options> ::=   
{  
      MAXSIZE = { 100 MB | 500 MB |1 | 5 | 10 | 20 | 30 … 150 … 500 } GB    
    | EDITION = { 'web' | 'business' | 'basic' | 'standard' | 'Premium' }   
    | SERVICE_OBJECTIVE = { 'shared' | 'basic' | 'S0' | 'S1' | 'S2' | 'P1' | 'P2' | 'P3' }   
}  
  
<set_database_options> ::=   
    <db_update_option>  
<db_update_option> ::=   
    { READ_ONLY | READ_WRITE }  
 [;]  
  

database_name
Nome del database da modificare.

System_CAPS_ICON_note.jpg Nota


Questa opzione non è disponibile in un database indipendente.

CURRENT

Si applica a: SQL Server 2012 - SQL Server 2016.

Specifica che il database corrente in uso deve essere modificato.

MODIFY NAME =new_database_name
Consente di rinominare il database con il nome specificato come new_database_name.

COLLATE collation_name

Si applica a: SQL Server 2008 - SQL Server 2016, Database SQL V12.

Specifica le regole di confronto per il database. collation_name può essere un nome di regole di confronto di Windows o SQL. Se omesso, al database vengono assegnate le regole di confronto dell'istanza di SQL Server.

Quando si creano database con regole di confronto diverse da quelle predefinite, i dati nel database rispettano sempre le regole di confronto specificate. Per SQL Server, quando si crea un database indipendente, le informazioni del catalogo interno vengono mantenute usando le regole di confronto predefinite di SQL Server, ovvero Latin1_General_100_CI_AS_WS_KS_SC. Per il Database SQL, le informazioni del catalogo interno vengono mantenute usando SQL_Latin1_General_CP1_CI_AS.

Per altre informazioni sui nomi di regole di confronto Windows e SQL, vedere COLLATE (Transact-SQL).

MODIFY (MAXSIZE = [100 MB | 500 MB | 1 | 5 | 10 | 20 | 30 … 150…500] GB)

Si applica a: Database SQL di Azure. Le edizioni Web e Business non sono supportate dall'Database SQL V12.

Specifica le dimensioni massime del database. Le dimensioni massime devono essere conformi al set valido di valori per la proprietà EDITION del database. La modifica delle dimensioni massime del database può causare la modifica del valore di EDITION del database. Nella tabella seguente sono elencati i valori MAXSIZE supportati e i valori predefiniti (P) per i livelli del servizio di Database SQL.

MAXSIZEWebBusinessBasicStandardPremium
100 MB
500 MB
1 GB√ (P)
2 GB√ (P)
5 GB
10 GB√ (P)
20 GB
30 GB
40 GB
50 GB
100 GB
150 GB
200 GB
250 GB√ (P)
300 GB
400 GB
500 GB√ (P)

Le seguenti regole vengono applicate agli argomenti MAXSIZE ed EDITION:

  • Il valore MAXSIZE, se specificato, deve essere un valore valido presente nella precedente tabella.

  • Se MAXSIZE è impostato su un valore inferiore a 5 GB ed EDITION viene omesso, l'edizione del database verrà automaticamente impostata su Web.

  • Se MAXSIZE è impostato su un valore maggiore di 5 GB ed EDITION viene omesso, l'edizione del database verrà automaticamente impostata su Business.

  • Se il valore di EDITION è specificato e il valore di MAXSIZE viene omesso, viene utilizzato il valore predefinito dell'edizione. Ad esempio, se EDITION è impostato su Standard e MAXSIZE non è specificato, il valore di MAXSIZE viene automaticamente impostato su 500 MB.

  • Se né MAXSIZE né EDITION vengono specificati, EDITION viene impostato su Web e MAXSIZE viene impostato su 1 GB.

MODIFY (EDITION = [ 'web' | 'business' | 'basic' | 'standard' | 'premium' ] )

Si applica a: Database SQL di Azure. Le edizioni Web e Business non sono supportate dall'Database SQL V12.

Modifica l'edizione del database. I livelli del servizio Database SQL possono essere impostati o modificati tramite il parametro EDITION. La modifica di EDITION ha esito negativo se la proprietà MAXSIZE per il database è impostata su un valore non compreso nell'intervallo valido supportato da questa edizione.

System_CAPS_ICON_important.jpg Importante


I livelli del servizio Business e Web verranno ritirati a settembre 2015. Per altre informazioni, vedere Domande frequenti su Web e Business.

SERVICE_OBJECTIVE

Si applica a: Database SQL di Azure

Specifica il livello di prestazioni. Per descrizioni degli obiettivi di servizio e altre informazioni su dimensioni, edizioni e combinazioni di obiettivi di servizio, vedere Livelli di servizio e livelli di prestazioni del database SQL di Azure. Se SERVICE_OBJECTIVE non è supportato da EDITION, sarà visualizzato un errore. Per cambiare il valore di SERVICE_OBJECTIVE da un livello a un altro (ad esempio da S1 a P1), è necessario modificare anche il valore EDITION.

<db_update_option> ::=

Si applica a: Database SQL di Azure

Indica se sono consentiti aggiornamenti nel database.

{ READ_ONLY | READ_WRITE }

READ_ONLY
Gli utenti possono leggere i dati dal database, ma non modificarli.

READ_WRITE
Il database è disponibile per operazioni di lettura e scrittura.

System_CAPS_ICON_note.jpg Nota


Nei database federati di Database SQL, SET { READ_ONLY | READ_WRITE } è disabilitato.

<delayed_durability_option> ::=

Si applica a: SQL Server 2014 - SQL Server 2016.

Per altre informazioni, vedere Opzioni ALTER DATABASE SET (Transact-SQL) e Controllo della durabilità delle transazioni.

<file_and_filegroup_options >::=

Per altre informazioni, vedere Opzioni per file e filegroup ALTER DATABASE (Transact-SQL).

<set_database_options >::=

Per altre informazioni, vedere Opzioni ALTER DATABASE SET (Transact-SQL), Mirroring del database di ALTER DATABASE (Transact-SQL), ALTER DATABASE SET HADR (Transact-SQL) e Livello di compatibilità ALTER DATABASE (Transact-SQL).

Per rimuovere un database, usare DROP DATABASE.

Per ridurre le dimensioni di un database, usare DBCC SHRINKDATABASE.

L'istruzione ALTER DATABASE deve essere eseguita in modalità autocommit (modalità predefinita di gestione delle transazioni) e non è consentita in una transazione esplicita o implicita.

Lo stato di un file di database, ad esempio online o offline, viene mantenuto indipendentemente dallo stato del database. Per altre informazioni, vedere Stati dei file. Lo stato dei file all'interno di un filegroup determina la disponibilità dell'intero filegroup. Un filegroup è disponibile se tutti i file in esso inclusi sono online. Se un filegroup è offline, qualsiasi tentativo di accesso al filegroup tramite un'istruzione SQL avrà esito negativo e verrà generato un errore. Per la compilazione di piani delle query per istruzioni SELECT, Query Optimizer evita gli indici non cluster e le viste indicizzate presenti in filegroup offline. Ciò consente la corretta esecuzione di tali istruzioni. Se tuttavia il filegroup offline contiene l'indice cluster o heap della tabella di destinazione, l'istruzione SELECT avrà esito negativo, così come tutte le istruzioni INSERT, UPDATE o DELETE che implicano la modifica di una tabella tramite un indice incluso in un filegroup offline.

Quando un database è nello stato RESTORING, la maggior parte delle istruzioni ALTER DATABASE avrà esito negativo. Un'alternativa consiste nell'impostare le opzioni di mirroring del database. Lo stato RESTORING può essere impostato durante un'operazione di ripristino attiva o quando un'operazione di ripristino di un database o di un file di log ha esito negativo a causa di un file di backup danneggiato.

La cache dei piani per l'istanza di SQL Server viene cancellata quando si imposta una delle opzioni seguenti.

OFFLINEREAD_WRITE
ONLINEMODIFY FILEGROUP DEFAULT
MODIFY_NAMEMODIFY FILEGROUP READ_WRITE
COLLATEMODIFY FILEGROUP READ_ONLY
READ_ONLYPAGE_VERIFY

La cancellazione della cache dei piani comporta la ricompilazione di tutti i piani di esecuzione successivi e può causare un peggioramento improvviso e temporaneo delle prestazioni di esecuzione delle query. Per ogni archivio cache cancellato nella cache dei piani, il log degli errori di SQL Server contiene il seguente messaggio informativo: "SQL Server ha rilevato %d occorrenza/e di scaricamento dell'archivio cache '%s' (parte della cache dei piani) a causa di operazioni di manutenzione o riconfigurazione del database". Questo messaggio viene registrato ogni cinque minuti per tutta la durata dello scaricamento della cache.

La cache delle procedure viene inoltre scaricata negli scenari seguenti:

  • L'opzione AUTO_CLOSE di un database è impostata su ON. Se il database non viene utilizzato da alcuna connessione utente, neanche come riferimento, tramite l'attività in background viene effettuato il tentativo di chiusura e di arresto automatici del database.

  • Vengono eseguite diverse query su un database contenente opzioni predefinite. Successivamente, il database viene eliminato.

  • Viene eliminato uno snapshot del database per un database di origine.

  • Viene ricompilato correttamente il log delle transazioni per un database.

  • Viene ripristinato un backup del database.

  • Viene scollegato un database.

Prima di applicare regole di confronto diverse a un database, verificare che siano soddisfatte le condizioni seguenti:

  1. Nessun altro utente sta utilizzando il database.

  2. Nessun oggetto associato a schema dipende dalle regole di confronto del database.

    Se il database contiene gli oggetti seguenti che dipendono dalle regole di confronto del database, l'istruzione ALTER DATABASE *database\_name* COLLATE avrà esito negativo. SQL Server restituirà un messaggio di errore per ogni oggetto che blocca l'azione ALTER:

    • Funzioni definite dall'utente e viste create con SCHEMABINDING.

    • Colonne calcolate.

    • Vincoli CHECK.

    • Funzioni con valori di tabella che restituiscono tabelle contenenti colonne di tipo carattere con regole di confronto ereditate dalle regole di confronto predefinite del database.

    Le informazioni sulle dipendenze per le entità non associate a schemi vengono aggiornate automaticamente quando vengono modificate le regole di confronto del database.

La modifica delle regole di confronto del database non comporta la creazione di duplicati per i nomi di sistema degli oggetti di database. Se la modifica delle regole di confronto genera nomi duplicati, gli spazi dei nomi seguenti potrebbero impedire tale modifica:

  • Nomi di oggetti, quali stored procedure, tabelle, trigger e viste.

  • Nomi di schemi.

  • Entità, come gruppi, ruoli o utenti.

  • Nomi di tipi di dati scalari, come i tipi di dati di sistema e definiti dall'utente.

  • Nomi di cataloghi full-text.

  • Nomi di colonne o parametri in un oggetto.

  • Nomi di indici in una tabella.

Se vengono generati nomi duplicati in seguito all'applicazione delle nuove regole di confronto, l'azione di modifica avrà esito negativo e in SQL Server verrà visualizzato un messaggio di errore che indica lo spazio dei nomi in cui è stato identificato il duplicato.

Per restituire informazioni su database, file e filegroup, è possibile usare viste del catalogo, funzioni di sistema e stored procedure di sistema.

SQL Server

È richiesta l'autorizzazione ALTER per il database.

Database SQL di Azure

Solo l'account di accesso dell'entità a livello di server (creato dal processo di provisioning) o i membri del ruolo del database dbmanager possono modificare un database.

System_CAPS_ICON_important.jpg Importante


Il proprietario del database può modificare il database solo se è un membro del ruolo dbmanager.

A.Modifica del nome di un database

Nell'esempio seguente il nome del database AdventureWorks2012 viene modificato in Northwind.

USE master;  
GO  
ALTER DATABASE AdventureWorks2012  
Modify Name = Northwind ;  
GO  

B.Modifica delle regole di confronto del database

Nell'esempio seguente viene creato un database denominato testdb con le regole di confronto SQL_Latin1_General_CP1_CI_AS, quindi vengono modificate le regole di confronto del database testdb in COLLATE French_CI_AI.

Si applica a: SQL Server 2008 - SQL Server 2016.
USE master;  
GO  
  
CREATE DATABASE testdb  
COLLATE SQL_Latin1_General_CP1_CI_AS ;  
GO  
  
ALTER DATABASE testDB  
COLLATE French_CI_AI ;  
GO  

CREATE DATABASE (Transact-SQL di SQL Server)
DATABASEPROPERTYEX (Transact-SQL)
DROP DATABASE (Transact-SQL)
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
EVENTDATA (Transact-SQL)
sp_configure (Transact-SQL)
sp_spaceused (Transact-SQL)
sys.databases (Transact-SQL)
sys.database_files (Transact-SQL)
sys.database_mirroring_witnesses (Transact-SQL)
sys.data_spaces (Transact-SQL)
sys.filegroups (Transact-SQL)
sys.master_files (Transact-SQL)
Database di sistema.

Aggiunte alla community

Mostra: