ALTER DATABASE (Transact-SQL)

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.

Convenzioni della sintassi Transact-SQL

Sintassi

-- 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] ) 
  | 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 }
 [;]

Argomenti

• database_name
  Nome del database da modificare.

  Nota

  Questa opzione non è disponibile in un database indipendente.

• CURRENT

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

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

  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.

  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

  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.

  MAXSIZE	Web	Business	Basic	Standard	Premium
  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

  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.

  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.

  Nota

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

<delayed_durability_option> ::=

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

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

Osservazioni

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.

OFFLINE	READ_WRITE
ONLINE	MODIFY FILEGROUP DEFAULT
MODIFY_NAME	MODIFY FILEGROUP READ_WRITE
COLLATE	MODIFY FILEGROUP READ_ONLY
READ_ONLY	PAGE_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.

Modifica delle regole di confronto del 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 DATABASEdatabase_nameCOLLATE 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.

Visualizzazione delle informazioni sui database

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

Autorizzazioni

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.

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

Esempi

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

USE master;
GO

CREATE DATABASE testdb
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

ALTER DATABASE testDB
COLLATE French_CI_AI ;
GO

Vedere anche

Riferimento

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)

Concetti

Database di sistema.