ALTER DATABASE (Transact-SQL)

Modifies certain configuration options of a database.

This article provides the syntax, arguments, remarks, permissions, and examples for whichever SQL product you choose.

For more information about the syntax conventions, see Transact-SQL syntax conventions.

Select a product

In the following row, select the product name you're interested in, and only that product's information is displayed.

* SQL Server *  

SQL Database

SQL Managed Instance

Azure Synapse
Analytics

Analytics Platform
System (PDW)

Microsoft Fabric

 

Overview: SQL Server

In SQL Server, this statement modifies a database, or the files and filegroups associated with the database. ALTER DATABASE adds or removes files and filegroups from a database, changes the attributes of a database or its files and filegroups, changes the database collation, and sets database options. Database snapshots can't be modified. To modify database options associated with replication, use sp_replicationdboption.

Because of its length, the ALTER DATABASE syntax is separated into the multiple articles.

Article	Description
ALTER DATABASE	The current article provides the syntax and related information for changing the name and the collation of a database.
ALTER DATABASE File and Filegroup Options	Provides the syntax and related information for adding and removing files and filegroups from a database, and for changing the attributes of the files and filegroups.
ALTER DATABASE SET options	Provides the syntax and related information for changing the attributes of a database by using the SET options of ALTER DATABASE.
ALTER DATABASE Database Mirroring	Provides the syntax and related information for the SET options of ALTER DATABASE that are related to database mirroring.
ALTER DATABASE SET HADR	Provides the syntax and related information for the Always On availability groups options of ALTER DATABASE for configuring a secondary database on a secondary replica of an Always On availability group.
ALTER DATABASE compatibility level	Provides the syntax and related information for the SET options of ALTER DATABASE that are related to database compatibility levels.
ALTER DATABASE SCOPED CONFIGURATION	Provides the syntax related to database scoped configurations used for individual database level settings such as query optimization and query execution related behaviors.

Syntax

-- SQL Server Syntax
ALTER DATABASE { database_name | CURRENT }
{
    MODIFY NAME = new_database_name
  | COLLATE collation_name
  | <file_and_filegroup_options>
  | SET <option_spec> [ ,...n ] [ WITH <termination> ]
}
[;]

<file_and_filegroup_options>::=
  <add_or_modify_files>::=
  <filespec>::=
  <add_or_modify_filegroups>::=
  <filegroup_updatability_option>::=

<option_spec>::=
{
  | <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>
  | <query_store_options>
  | <recovery_option>
  | <service_broker_option>
  | <snapshot_option>
  | <sql_option>
  | <termination>
  | <temporal_history_retention>
  | <data_retention_policy>
  | <compatibility_level>
      { 160 | 150 | 140 | 130 | 120 | 110 | 100 | 90 }
}

Arguments

database_name

Is the name of the database to be modified.

Note

This option isn't available in a Contained Database.

CURRENT
Applies to: SQL Server 2012 (11.x) and later.

Designates that the current database in use should be altered.

MODIFY NAME = new_database_name

Renames the database with the name specified as new_database_name.

COLLATE collation_name

Specifies the collation for the database. collation_name can be either a Windows collation name or a SQL collation name. If not specified, the database is assigned the collation of the instance of SQL Server.

Note

Collation can't be changed after database has been created on Azure SQL Database.

When creating databases with other than the default collation, the data in the database always respects the specified collation. For SQL Server, when creating a contained database, the internal catalog information is maintained using the SQL Server default collation, Latin1_General_100_CI_AS_WS_KS_SC.

For more information about the Windows and SQL collation names, see COLLATE.

<delayed_durability_option> ::=

Applies to: SQL Server 2014 (12.x) and later.

For more information, see ALTER DATABASE SET options and Control Transaction Durability.

<file_and_filegroup_options>::=

For more information, see ALTER DATABASE File and Filegroup Options.

Remarks

To remove a database, use DROP DATABASE.

To decrease the size of a database, use DBCC SHRINKDATABASE.

The ALTER DATABASE statement must run in auto-commit mode (the default transaction management mode) and isn't allowed in an explicit or implicit transaction.

The state of a database file (for example, online or offline), is maintained independently from the state of the database. For more information, see File States. The state of the files within a filegroup determines the availability of the whole filegroup. For a filegroup to be available, all files within the filegroup must be online. If a filegroup is offline, any attempt to access the filegroup by a SQL statement fails with an error. When you build query plans for SELECT statements, the query optimizer avoids nonclustered indexes and indexed views that reside in offline filegroups. This enables these statements to succeed. However, if the offline filegroup contains the heap or clustered index of the target table, the SELECT statements fail. Additionally, any INSERT, UPDATE, or DELETE statement that modifies a table with any index in an offline filegroup fails.

When a database is in the RESTORING state, most ALTER DATABASE statements fail. The exception is setting database mirroring options. A database might be in the RESTORING state during an active restore operation or when a restore operation of a database or log file fails because of a corrupted backup file.

The plan cache for the instance of SQL Server is cleared by setting one of the following options.

• COLLATE
• MODIFY FILEGROUP DEFAULT
• MODIFY FILEGROUP READ_ONLY
• MODIFY FILEGROUP READ_WRITE
• MODIFY_NAME
• OFFLINE
• ONLINE
• PAGE_VERIFY
• READ_ONLY
• READ_WRITE

Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations. This message is logged every five minutes as long as the cache is flushed within that time interval.

The plan cache is also flushed in the following scenarios:

• A database has the AUTO_CLOSE database option set to ON. When no user connection references or uses the database, the background task tries to close and shut down the database automatically.
• You run several queries against a database that has default options. Then, the database is dropped.
• A database snapshot for a source database is dropped.
• You successfully rebuild the transaction log for a database.
• You restore a database backup.
• You detach a database.

Change the database collation

Before you apply a different collation to a database, make sure that the following conditions are in place:

• You are the only one currently using the database.
• No schema-bound object depends on the collation of the database.

If the following objects, which depend on the database collation, exist in the database, the ALTER DATABASE database_name COLLATE statement fails. SQL Server returns an error message for each object blocking the ALTER action:

• User-defined functions and views created with SCHEMABINDING
• Computed columns
• CHECK constraints
• Table-valued functions that return tables with character columns with collations inherited from the default database collation

Dependency information for non-schema-bound entities is automatically updated when the database collation is changed.

Changing the database collation does not create duplicates among any system names for the database objects. If duplicate names result from the changed collation, the following namespaces can cause the failure of a database collation change:

• Object names such as a procedure, table, trigger, or view
• Schema names
• Principals such as a group, role, or user
• Scalar-type names such as system and user-defined types
• Full-text catalog names
• Column or parameter names within an object
• Index names within a table

Duplicate names resulting from the new collation cause the change action to fail, and SQL Server returns an error message specifying the namespace where the duplicate was found.

View database information

You can use catalog views, system functions, and system stored procedures to return information about databases, files, and filegroups.

Permissions

Requires ALTER permission on the database.

Examples

A. Change the name of a database

The following example changes the name of the AdventureWorks2022 database to Northwind.

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

B. Change the collation of a database

The following example creates a database named testdb with the SQL_Latin1_General_CP1_CI_AS collation, and then changes the collation of the testdb database to COLLATE French_CI_AI.

Applies to: SQL Server 2008 (10.0.x) and later.

USE master;
GO

CREATE DATABASE testdb
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

ALTER DATABASE testDB
COLLATE French_CI_AI ;
GO

Related content

• CREATE DATABASE
• DATABASEPROPERTYEX
• DROP DATABASE
• SET TRANSACTION ISOLATION LEVEL
• EVENTDATA
• sp_configure
• sp_spaceused
• sys.databases
• sys.database_files
• sys.data_spaces
• sys.filegroups
• sys.master_files
• System Databases