Transact-SQL Data Definition

Language (DDL) Reference

SQL Server 2012 Books Online

Summary: Data Definition Language (DDL) is a vocabulary used to define data

structures in SQL Server 2012. Use these statements to create, alter, or drop data
structures in an instance of SQL Server.

Category: Reference
Applies to: SQL Server 2012
Source: SQL Server Books Online (link to source content)
E-book publication date: June 2012
Copyright © 2012 by Microsoft Corporation
All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by
any means without the written permission of the publisher.

Microsoft and the trademarks listed at

http://www.microsoft.com/about/legal/en/us/IntellectualProperty/Trademarks/EN-US.aspx are trademarks of
the Microsoft group of companies. All other marks are property of their respective owners.

The example companies, organizations, products, domain names, email addresses, logos, people, places, and
events depicted herein are fictitious. No association with any real company, organization, product, domain
name, email address, logo, person, place, or event is intended or should be inferred.

This book expresses the author’s views and opinions. The information contained in this book is provided
without any express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its
resellers, or distributors will be held liable for any damages caused or alleged to be caused either directly or
indirectly by this book.
Contents
Data Definition Language (DDL) Statements (Transact-SQL) ......................................................... 7

ALTER Statements............................................................................................................................................ 7
ALTER APPLICATION ROLE....................................................................................................................... 8
ALTER ASSEMBLY ....................................................................................................................................... 11
ALTER ASYMMETRIC KEY ........................................................................................................................ 15
ALTER AUTHORIZATION ......................................................................................................................... 18
ALTER AVAILABILITY GROUP................................................................................................................. 22
ALTER BROKER PRIORITY........................................................................................................................ 37
ALTER CERTIFICATE ................................................................................................................................... 40
ALTER CREDENTIAL................................................................................................................................... 43
ALTER CRYPTOGRAPHIC PROVIDER ................................................................................................... 44
ALTER DATABASE ...................................................................................................................................... 46
ALTER DATABASE File and Filegroup Options ............................................................................ 51
ALTER DATABASE SET Options ......................................................................................................... 64
ALTER DATABASE Database Mirroring .......................................................................................... 96
ALTER DATABASE SET HADR .......................................................................................................... 102
ALTER DATABASE Compatibility Level ........................................................................................ 105
ALTER DATABASE AUDIT SPECIFICATION ..................................................................................... 116
ALTER DATABASE ENCRYPTION KEY............................................................................................... 119
ALTER ENDPOINT ................................................................................................................................... 120
ALTER EVENT SESSION ......................................................................................................................... 123
ALTER FULLTEXT CATALOG................................................................................................................. 133
ALTER FULLTEXT INDEX........................................................................................................................ 135
ALTER FULLTEXT STOPLIST ................................................................................................................. 144
ALTER FUNCTION ................................................................................................................................... 146
ALTER INDEX ............................................................................................................................................ 157
ALTER LOGIN ............................................................................................................................................ 174
ALTER MASTER KEY................................................................................................................................ 178
ALTER MESSAGE TYPE........................................................................................................................... 180
ALTER PARTITION FUNCTION............................................................................................................ 182
ALTER PARTITION SCHEME ................................................................................................................ 185
ALTER PROCEDURE ................................................................................................................................ 187
ALTER QUEUE ........................................................................................................................................... 193
ALTER REMOTE SERVICE BINDING .................................................................................................. 197
ALTER RESOURCE GOVERNOR .......................................................................................................... 198
ALTER RESOURCE POOL ...................................................................................................................... 203
ALTER ROLE............................................................................................................................................... 206
ALTER ROUTE ........................................................................................................................................... 207
ALTER SCHEMA ....................................................................................................................................... 211
ALTER SEARCH PROPERTY LIST......................................................................................................... 214
ALTER SEQUENCE ................................................................................................................................... 219
 ALTER SERVER AUDIT............................................................................................................................ 223
ALTER SERVER AUDIT SPECIFICATION ........................................................................................... 229
ALTER SERVER CONFIGURATION ..................................................................................................... 231
ALTER SERVER ROLE .............................................................................................................................. 236
ALTER SERVICE......................................................................................................................................... 239
ALTER SERVICE MASTER KEY.............................................................................................................. 241
ALTER SYMMETRIC KEY ........................................................................................................................ 244
ALTER TABLE ............................................................................................................................................. 246
column_definition ............................................................................................................................... 273
column_constraint .............................................................................................................................. 277
computed_column_definition ........................................................................................................ 283
table_constraint ................................................................................................................................... 287
index_option ......................................................................................................................................... 292
ALTER TRIGGER........................................................................................................................................ 297
ALTER USER............................................................................................................................................... 303
ALTER VIEW............................................................................................................................................... 307
ALTER WORKLOAD GROUP ................................................................................................................ 310
ALTER XML SCHEMA COLLECTION.................................................................................................. 315

CREATE Statements.................................................................................................................................... 322

CREATE AGGREGATE ............................................................................................................................. 323
CREATE APPLICATION ROLE............................................................................................................... 325
CREATE ASSEMBLY ................................................................................................................................. 327
CREATE ASYMMETRIC KEY .................................................................................................................. 331
CREATE AVAILABILITY GROUP........................................................................................................... 335
CREATE BROKER PRIORITY.................................................................................................................. 354
CREATE CERTIFICATE ............................................................................................................................. 361
CREATE COLUMNSTORE INDEX........................................................................................................ 366
CREATE CONTRACT ............................................................................................................................... 371
CREATE CREDENTIAL............................................................................................................................. 374
CREATE CRYPTOGRAPHIC PROVIDER ............................................................................................. 376
CREATE DATABASE ................................................................................................................................ 378
CREATE DATABASE AUDIT SPECIFICATION .................................................................................. 400
CREATE DATABASE ENCRYPTION KEY............................................................................................ 403
CREATE DEFAULT .................................................................................................................................... 405
CREATE ENDPOINT ................................................................................................................................ 407
CREATE EVENT NOTIFICATION ......................................................................................................... 414
CREATE EVENT SESSION ...................................................................................................................... 418
CREATE FULLTEXT CATALOG.............................................................................................................. 425
CREATE FULLTEXT INDEX..................................................................................................................... 427
CREATE FULLTEXT STOPLIST .............................................................................................................. 434
CREATE FUNCTION ................................................................................................................................ 436
CREATE INDEX ......................................................................................................................................... 457
CREATE LOGIN ......................................................................................................................................... 482
CREATE MASTER KEY............................................................................................................................. 488
CREATE MESSAGE TYPE........................................................................................................................ 489
 CREATE PARTITION FUNCTION......................................................................................................... 492
CREATE PARTITION SCHEME ............................................................................................................. 497
CREATE PROCEDURE ............................................................................................................................. 501
CREATE QUEUE ........................................................................................................................................ 524
CREATE REMOTE SERVICE BINDING ............................................................................................... 531
CREATE RESOURCE POOL ................................................................................................................... 533
CREATE ROLE............................................................................................................................................ 536
CREATE ROUTE ........................................................................................................................................ 538
CREATE RULE ............................................................................................................................................ 543
CREATE SCHEMA .................................................................................................................................... 546
CREATE SEARCH PROPERTY LIST...................................................................................................... 550
CREATE SEQUENCE ................................................................................................................................ 553
CREATE SERVER AUDIT......................................................................................................................... 559
CREATE SERVER AUDIT SPECIFICATION ........................................................................................ 565
CREATE SERVER ROLE ........................................................................................................................... 567
CREATE SERVICE...................................................................................................................................... 568
CREATE SPATIAL INDEX ....................................................................................................................... 571
CREATE STATISTICS................................................................................................................................ 585
CREATE SYMMETRIC KEY ..................................................................................................................... 589
CREATE SYNONYM ................................................................................................................................ 594
CREATE TABLE .......................................................................................................................................... 598
IDENTITY (Property) ........................................................................................................................... 627
CREATE TRIGGER..................................................................................................................................... 630
CREATE TYPE............................................................................................................................................. 645
CREATE USER............................................................................................................................................ 651
CREATE VIEW............................................................................................................................................ 659
CREATE WORKLOAD GROUP ............................................................................................................. 672
CREATE XML INDEX ............................................................................................................................... 675
CREATE XML SCHEMA COLLECTION............................................................................................... 682

DISABLE TRIGGER ....................................................................................................................................... 689

DROP Statements ....................................................................................................................................... 692

DROP AGGREGATE ................................................................................................................................. 693
DROP APPLICATION ROLE .................................................................................................................. 693
DROP ASSEMBLY .................................................................................................................................... 695
DROP ASYMMETRIC KEY ..................................................................................................................... 696
DROP AVAILABILITY GROUP .............................................................................................................. 697
DROP BROKER PRIORITY ..................................................................................................................... 698
DROP CERTIFICATE ................................................................................................................................ 699
DROP CONTRACT ................................................................................................................................... 699
DROP CREDENTIAL ................................................................................................................................ 700
DROP CRYPTOGRAPHIC PROVIDER ................................................................................................ 701
DROP DATABASE .................................................................................................................................... 702
DROP DATABASE AUDIT SPECIFICATION ..................................................................................... 704
DROP DATABASE ENCRYPTION KEY ............................................................................................... 706
DROP DEFAULT ....................................................................................................................................... 707
 DROP ENDPOINT .................................................................................................................................... 708
DROP EVENT NOTIFICATION ............................................................................................................. 709
DROP EVENT SESSION.......................................................................................................................... 711
DROP FULLTEXT CATALOG ................................................................................................................. 711
DROP FULLTEXT INDEX ........................................................................................................................ 712
DROP FULLTEXT STOPLIST .................................................................................................................. 713
DROP FUNCTION .................................................................................................................................... 714
DROP INDEX ............................................................................................................................................. 715
DROP LOGIN............................................................................................................................................. 725
DROP MASTER KEY ................................................................................................................................ 725
DROP MESSAGE TYPE ........................................................................................................................... 726
DROP PARTITION FUNCTION ............................................................................................................ 727
DROP PARTITION SCHEME ................................................................................................................. 728
DROP PROCEDURE................................................................................................................................. 729
DROP QUEUE............................................................................................................................................ 730
DROP REMOTE SERVICE BINDING ................................................................................................... 732
DROP RESOURCE POOL ....................................................................................................................... 732
DROP ROLE ............................................................................................................................................... 733
DROP ROUTE ............................................................................................................................................ 735
DROP RULE................................................................................................................................................ 735
DROP SCHEMA ........................................................................................................................................ 737
DROP SEARCH PROPERTY LIST ......................................................................................................... 738
DROP SEQUENCE.................................................................................................................................... 740
DROP SERVER AUDIT ............................................................................................................................ 741
DROP SERVER AUDIT SPECIFICATION ............................................................................................ 743
DROP SERVER ROLE............................................................................................................................... 744
DROP SERVICE ......................................................................................................................................... 746
DROP SIGNATURE .................................................................................................................................. 746
DROP STATISTICS ................................................................................................................................... 748
DROP SYMMETRIC KEY ........................................................................................................................ 749
DROP SYNONYM .................................................................................................................................... 750
DROP TABLE ............................................................................................................................................. 751
DROP TRIGGER ........................................................................................................................................ 754
DROP TYPE ................................................................................................................................................ 756
DROP USER ............................................................................................................................................... 757
DROP VIEW ............................................................................................................................................... 758
DROP WORKLOAD GROUP................................................................................................................. 760
DROP XML SCHEMA COLLECTION .................................................................................................. 761

ENABLE TRIGGER ........................................................................................................................................ 763

UPDATE STATISTICS .................................................................................................................................. 765

TRUNCATE TABLE ....................................................................................................................................... 769

Data Definition Language (DDL) Statements
(Transact-SQL)
Data Definition Language (DDL) is a vocabulary used to define data structures in SQL Server
2012. Use these statements to create, alter, or drop data structures in an instance of SQL Server.

In this Section
ALTER Statements (Transact-SQL)
CREATE Statements (Transact-SQL)
DISABLE TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL)
DROP Statements (Transact-SQL)
ENABLE TRIGGER (Transact-SQL)
TRUNCATE TABLE (Transact-SQL)
UPDATE STATISTICS (Transact-SQL)

ALTER Statements
SQL Server Transact-SQL contains the following ALTER statements. Use ALTER statements to
modify the definition of existing entities. For example, use ALTER TABLE to add a new column to
a table, or use ALTER DATABASE to set database options.

In this Section

ALTER APPLICATION ROLE ALTER EVENT SESSION ALTER ROLE

ALTER ASSEMBLY ALTER FULLTEXT CATALOG ALTER ROUTE
ALTER ASYMMETRIC KEY ALTER FULLTEXT INDEX ALTER SCHEMA
ALTER AUTHORIZATION ALTER FULLTEXT STOPLIST ALTER SEARCH PROPERTY LIST
(Transact-SQL)
ALTER BROKER PRIORITY ALTER FUNCTION ALTER SEQUENCE (Transact-
SQL)
ALTER CERTIFICATE ALTER INDEX ALTER SERVER AUDIT

7
ALTER CREDENTIAL ALTER LOGIN ALTER SERVER AUDIT
SPECIFICATION
ALTER CRYPTOGRAPHIC ALTER MASTER KEY ALTER SERVICE
PROVIDER
ALTER DATABASE ALTER MESSAGE TYPE ALTER SERVICE MASTER KEY
ALTER DATABASE AUDIT ALTER PARTITION FUNCTION ALTER SYMMETRIC KEY
SPECIFICATION
ALTER DATABASE Compatibility ALTER PARTITION SCHEME ALTER TABLE
Level
ALTER DATABASE Database ALTER PROCEDURE ALTER TRIGGER
Mirroring
ALTER DATABASE ENCRYPTION ALTER QUEUE ALTER USER
KEY
ALTER DATABASE File and ALTER REMOTE SERVICE ALTER VIEW
Filegroup Options BINDING
ALTER DATABASE SET Options ALTER RESOURCE ALTER WORKLOAD GROUP
GOVERNOR
ALTER ENDPOINT ALTER RESOURCE POOL ALTER XML SCHEMA
COLLECTION

See Also
CREATE Statements (Transact-SQL)
DROP Statements

ALTER APPLICATION ROLE

Changes the name, password, or default schema of an application role.
Transact-SQL Syntax Conventions
Syntax

ALTER APPLICATION ROLE application_role_name

WITH <set_item> [ ,...n ]

<set_item> ::=

8
 NAME = new_application_role_name
| PASSWORD = 'password'
| DEFAULT_SCHEMA = schema_name
Arguments
application_role_name
Is the name of the application role to be modified.

NAME = new_application_role_name
Specifies the new name of the application role. This name must not already be used to refer
to any principal in the database.

PASSWORD = 'password'
Specifies the password for the application role. password must meet the Windows password
policy requirements of the computer that is running the instance of SQL Server. You should
always use strong passwords.

DEFAULT_SCHEMA = schema_name
Specifies the first schema that will be searched by the server when it resolves the names of
objects. schema_name can be a schema that does not exist in the database.

Remarks
If the new application role name already exists in the database, the statement will fail. When the
name, password, or default schema of an application role is changed the ID associated with the
role is not changed.

Important
Password expiration policy is not applied to application role passwords. For this reason,
take extra care in selecting strong passwords. Applications that invoke application roles
must store their passwords.
Application roles are visible in the sys.database_principals catalog view.

Caution
In SQL Server 2005 the behavior of schemas changed from the behavior in earlier
versions of SQL Server. Code that assumes that schemas are equivalent to database users
may not return correct results. Old catalog views, including sysobjects, should not be
used in a database in which any of the following DDL statements has ever been used:
CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP
USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE,
DROP APPROLE, ALTER AUTHORIZATION. In a database in which any of these statements
has ever been used, you must use the new catalog views. The new catalog views take
into account the separation of principals and schemas that is introduced in SQL Server
2005. For more information about catalog views, see EVENTDATA (Transact-SQL)).

9
Permissions
Requires ALTER ANY APPLICATION ROLE permission on the database. To change the default
schema, the user also needs ALTER permission on the application role. An application role can
alter its own default schema, but not its name or password.
Examples
A. Changing the name of application role
The following example changes the name of the application role weekly_receipts to
receipts_ledger.
USE AdventureWorks2012;
CREATE APPLICATION ROLE weekly_receipts
WITH PASSWORD = '987Gbv8$76sPYY5m23' ,
DEFAULT_SCHEMA = Sales;
GO
ALTER APPLICATION ROLE weekly_receipts
WITH NAME = receipts_ledger;
GO

B. Changing the password of application role

The following example changes the password of the application role receipts_ledger.
ALTER APPLICATION ROLE receipts_ledger
WITH PASSWORD = '897yUUbv867y$200nk2i';
GO

C. Changing the name, password, and default schema

The following example changes the name, password, and default schema of the application role
receipts_ledger all at the same time.
ALTER APPLICATION ROLE receipts_ledger
WITH NAME = weekly_ledger,
PASSWORD = '897yUUbv77bsrEE00nk2i',
DEFAULT_SCHEMA = Production;
GO

See Also
Application Roles
CREATE APPLICATION ROLE (Transact-SQL)
DROP APPLICATION ROLE (Transact-SQL)
EVENTDATA (Transact-SQL)
10
ALTER ASSEMBLY
Alters an assembly by modifying the SQL Server catalog properties of an assembly. ALTER
ASSEMBLY refreshes it to the latest copy of the Microsoft .NET Framework modules that hold its
implementation and adds or removes files associated with it. Assemblies are created by using
CREATE ASSEMBLY.
Transact-SQL Syntax Conventions
Syntax

ALTER ASSEMBLY assembly_name

[ FROM <client_assembly_specifier> | <assembly_bits> ]
[ WITH <assembly_option> [ ,...n ] ]
[ DROP FILE { file_name [ ,...n ] | ALL } ]
[ ADD FILE FROM
{
client_file_specifier [ AS file_name ]
| file_bits AS file_name
} [,...n ]
][;]
<client_assembly_specifier> :: =
'\\computer_name\share-name\[path\]manifest_file_name'
| '[local_path\]manifest_file_name'

<assembly_bits> :: =
{ varbinary_literal | varbinary_expression }

<assembly_option> :: =
PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE }
| VISIBILITY = { ON | OFF }
| UNCHECKED DATA

Arguments
assembly_name
Is the name of the assembly you want to modify. assembly_name must already exist in the
database.

11
FROM <client_assembly_specifier> | <assembly_bits>
Updates an assembly to the latest copy of the .NET Framework modules that hold its
implementation. This option can only be used if there are no associated files with the
specified assembly.
<client_assembly_specifier> specifies the network or local location where the assembly being
refreshed is located. The network location includes the computer name, the share name and
a path within that share. manifest_file_name specifies the name of the file that contains the
manifest of the assembly.
<assembly_bits> is the binary value for the assembly.
Separate ALTER ASSEMBLY statements must be issued for any dependent assemblies that
also require updating.

Note
This option is not available in a contained database.

PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE }

Specifies the .NET Framework code access permission set property of the assembly. For more
information about this property, see EVENTDATA (Transact-SQL).

Note
The EXTERNAL_ACCESS and UNSAFE options are not available in a contained database.

VISIBILITY = { ON | OFF }
Indicates whether the assembly is visible for creating common language runtime (CLR)
functions, stored procedures, triggers, user-defined types, and user-defined aggregate
functions against it. If set to OFF, the assembly is intended to be called only by other
assemblies. If there are existing CLR database objects already created against the assembly,
the visibility of the assembly cannot be changed. Any assemblies referenced by
assembly_name are uploaded as not visible by default.

UNCHECKED DATA
By default, ALTER ASSEMBLY fails if it must verify the consistency of individual table rows.
This option allows postponing the checks until a later time by using DBCC CHECKTABLE. If
specified, SQL Server executes the ALTER ASSEMBLY statement even if there are tables in the
database that contain the following:
• Persisted computed columns that either directly or indirectly reference methods in the
assembly, through Transact-SQL functions or methods.
• CHECK constraints that directly or indirectly reference methods in the assembly.
• Columns of a CLR user-defined type that depend on the assembly, and the type
implements a UserDefined (non-Native) serialization format.
• Columns of a CLR user-defined type that reference views created by using WITH
SCHEMABINDING.
If any CHECK constraints are present, they are disabled and marked untrusted. Any tables

12
 containing columns depending on the assembly are marked as containing unchecked data
until those tables are explicitly checked.
Only members of the db_owner and db_ddlowner fixed database roles can specify this
option.
For more information, see Implementing Assemblies.
[ DROP FILE { file_name[ ,...n] | ALL } ]
Removes the file name associated with the assembly, or all files associated with the assembly,
from the database. If used with ADD FILE that follows, DROP FILE executes first. This lets you
to replace a file with the same file name.

Note
This option is not available in a contained database.

[ ADD FILE FROM { client_file_specifier [ AS file_name] | file_bitsAS file_name}

Uploads a file to be associated with the assembly, such as source code, debug files or other
related information, into the server and made visible in the sys.assembly_files catalog view.
client_file_specifier specifies the location from which to upload the file. file_bits can be used
instead to specify the list of binary values that make up the file. file_name specifies the name
under which the file should be stored in the instance of SQL Server. file_name must be
specified if file_bits is specified, and is optional if client_file_specifier is specified. If file_name
is not specified, the file_name part of client_file_specifier is used as file_name.

Note
This option is not available in a contained database.

Remarks
ALTER ASSEMBLY does not disrupt currently running sessions that are running code in the
assembly being modified. Current sessions complete execution by using the unaltered bits of
the assembly.
If the FROM clause is specified, ALTER ASSEMBLY updates the assembly with respect to the
latest copies of the modules provided. Because there might be CLR functions, stored
procedures, triggers, data types, and user-defined aggregate functions in the instance of SQL
Server that are already defined against the assembly, the ALTER ASSEMBLY statement rebinds
them to the latest implementation of the assembly. To accomplish this rebinding, the methods
that map to CLR functions, stored procedures, and triggers must still exist in the modified
assembly with the same signatures. The classes that implement CLR user-defined types and
user-defined aggregate functions must still satisfy the requirements for being a user-defined
type or aggregate.

Caution
If WITH UNCHECKED DATA is not specified, SQL Server tries to prevent ALTER ASSEMBLY
from executing if the new assembly version affects existing data in tables, indexes, or
other persistent sites. However, SQL Server does not guarantee that computed columns,
13
 indexes, indexed views or expressions will be consistent with the underlying routines and
types when the CLR assembly is updated. Use caution when you execute ALTER
ASSEMBLY to make sure that there is not a mismatch between the result of an
expression and a value based on that expression stored in the assembly.
ALTER ASSEMBLY changes the assembly version. The culture and public key token of the
assembly remain the same.
ALTER ASSEMBLY statement cannot be used to change the following:
• The signatures of CLR functions, aggregate functions, stored procedures, and triggers in an
instance of SQL Server that reference the assembly. ALTER ASSEMBLY fails when SQL Server
cannot rebind .NET Framework database objects in SQL Server with the new version of the
assembly.
• The signatures of methods in the assembly that are called from other assemblies.
• The list of assemblies that depend on the assembly, as referenced in the DependentList
property of the assembly.
• The indexability of a method, unless there are no indexes or persisted computed columns
depending on that method, either directly or indirectly.
• The FillRow method name attribute for CLR table-valued functions.
• The Accumulate and Terminate method signature for user-defined aggregates.
• System assemblies.
• Assembly ownership. Use ALTER AUTHORIZATION (Transact-SQL) instead.
Additionally, for assemblies that implement user-defined types, ALTER ASSEMBLY can be used
for making only the following changes:
• Modifying public methods of the user-defined type class, as long as signatures or attributes
are not changed.
• Adding new public methods.
• Modifying private methods in any way.
Fields contained within a native-serialized user-defined type, including data members or base
classes, cannot be changed by using ALTER ASSEMBLY. All other changes are unsupported.
If ADD FILE FROM is not specified, ALTER ASSEMBLY drops any files associated with the
assembly.
If ALTER ASSEMBLY is executed without the UNCHECKED data clause, checks are performed to
verify that the new assembly version does not affect existing data in tables. Depending on the
amount of data that needs to be checked, this may affect performance.
Permissions
Requires ALTER permission on the assembly. Additional requirements are as follows:
• To alter an assembly whose existing permission set is EXTERNAL_ACCESS, the SQL Server
login must have EXTERNAL ACCESS permission on the server.

14
• To alter an assembly whose existing permission set is UNSAFE requires membership in the
sysadmin fixed server role.
• To change the permission set of an assembly to EXTERNAL_ACCESS, the SQL Server login
must have EXTERNAL ACCESS ASSEMBLY permission on the server.
• To change the permission set of an assembly to UNSAFE requires membership in the
sysadmin fixed server role.
• Specifying WITH UNCHECKED DATA requires membership in the sysadmin fixed server role.
For more information about assembly permission sets, see Designing Assemblies.
Examples
A. Refreshing an assembly
The following example updates assembly ComplexNumber to the latest copy of the .NET
Framework modules that hold its implementation.

Note
Assembly ComplexNumber can be created by running the UserDefinedDataType sample
scripts. For more information, see User-Defined Type (UDT) Sample.
ALTER ASSEMBLY ComplexNumber
FROM 'C:\Program Files\Microsoft SQL
Server\90\Tools\Samples\1033\Engine\Programmability\CLR\UserDefinedDataType\C
S\ComplexNumber\obj\Debug\ComplexNumber.dll'

B. Adding a file to associate with an assembly

The following example uploads the source code file Class1.cs to be associated with assembly
MyClass. This example assumes assembly MyClass is already created in the database.
ALTER ASSEMBLY MyClass
ADD FILE FROM 'C:\MyClassProject\Class1.cs';

C. Changing the permissions of an assembly

The following example changes the permission set of assembly ComplexNumber from SAFE to
EXTERNAL ACCESS.
ALTER ASSEMBLY ComplexNumber WITH PERMISSION_SET = EXTERNAL_ACCESS

See Also
CREATE ASSEMBLY
DROP ASSEMBLY
EVENTDATA (Transact-SQL)

ALTER ASYMMETRIC KEY

Changes the properties of an asymmetric key.
15
 Transact-SQL Syntax Conventions
Syntax

ALTER ASYMMETRIC KEY Asym_Key_Name <alter_option>

<alter_option> ::=
<password_change_option>
|
REMOVE PRIVATE KEY
<password_change_option> ::=
WITH PRIVATE KEY ( <password_option> [ , <password_option> ] )
<password_option> ::=
ENCRYPTION BY PASSWORD = 'strongPassword'
|
DECRYPTION BY PASSWORD = 'oldPassword'
Arguments
Asym_Key_Name
Is the name by which the asymmetric key is known in the database.

REMOVE PRIVATE KEY

Removes the private key from the asymmetric key The public key is not removed.

WITH PRIVATE KEY

Changes the protection of the private key.

ENCRYPTION BY PASSWORD = 'stongPassword'

Specifies a new password for protecting the private key. password must meet the Windows
password policy requirements of the computer that is running the instance of SQL Server. If
this option is omitted, the private key will be encrypted by the database master key.

DECRYPTION BY PASSWORD = 'oldPassword'

Specifies the old password, with which the private key is currently protected. Is not required if
the private key is encrypted with the database master key.

Remarks
If there is no database master key the ENCRYPTION BY PASSWORD option is required, and the
operation will fail if no password is supplied. For information about how to create a database
master key, see OPEN MASTER KEY (Transact-SQL).
You can use ALTER ASYMMETRIC KEY to change the protection of the private key by specifying
PRIVATE KEY options as shown in the following table.
16
Change protection from ENCRYPTION BY PASSWORD DECRYPTION BY PASSWORD

Old password to new Required Required

password
Password to master key Omit Required
Master key to password Required Omit

The database master key must be opened before it can be used to protect a private key. For
more information, see OPEN MASTER KEY (Transact-SQL).
To change the ownership of an asymmetric key, use ALTER AUTHORIZATION.
Permissions
Requires CONTROL permission on the asymmetric key if the private key is being removed.
Examples
A. Changing the password of the private key
The following example changes the password used to protect the private key of asymmetric key
PacificSales09. The new password will be <enterStrongPasswordHere>.
ALTER ASYMMETRIC KEY PacificSales09
WITH PRIVATE KEY (
DECRYPTION BY PASSWORD = '<oldPassword>',
ENCRYPTION BY PASSWORD = '<enterStrongPasswordHere>');
GO

B. Removing the private key from an asymmetric key

The following example removes the private key from PacificSales19, leaving only the public
key.
ALTER ASYMMETRIC KEY PacificSales19 REMOVE PRIVATE KEY;
GO

C. Removing password protection from a private key

The following example removes the password protection from a private key and protects it with
the database master key.
OPEN MASTER KEY;
ALTER ASYMMETRIC KEY PacificSales09 WITH PRIVATE KEY (
DECRYPTION BY PASSWORD = '<enterStrongPasswordHere>' );
GO

17
See Also
CREATE ASYMMETRIC KEY (Transact-SQL)
DROP ASYMMETRIC KEY (Transact-SQL)
SQL Server and Database Encryption Keys (Database Engine)
Encryption Hierarchy
CREATE MASTER KEY (Transact-SQL)
OPEN MASTER KEY (Transact-SQL)
Understanding Extensible Key Management (EKM)

ALTER AUTHORIZATION
Changes the ownership of a securable.
Transact-SQL Syntax Conventions
Syntax

ALTER AUTHORIZATION
ON [ <class_type>:: ] entity_name
TO { SCHEMA OWNER | principal_name }
[;]

<class_type> ::=
{
OBJECT | ASSEMBLY | ASYMMETRIC KEY | CERTIFICATE
| CONTRACT | TYPE | DATABASE | ENDPOINT | FULLTEXT CATALOG
| FULLTEXT STOPLIST | MESSAGE TYPE | REMOTE SERVICE BINDING
| ROLE | ROUTE | SCHEMA | SEARCH PROPERTY LIST | SERVER ROLE
| SERVICE | SYMMETRIC KEY | XML SCHEMA COLLECTION
}
Arguments
<class_type>
Is the securable class of the entity for which the owner is being changed. OBJECT is the
default.

entity_name
Is the name of the entity.

18
principal_name
Is the name of the principal that will own the entity.

Remarks
ALTER AUTHORIZATION can be used to change the ownership of any entity that has an owner.
Ownership of database-contained entities can be transferred to any database-level principal.
Ownership of server-level entities can be transferred only to server-level principals.

Important
Beginning with SQL Server 2005, a user can own an OBJECT or TYPE that is contained by
a schema owned by another database user. This is a change of behavior from earlier
versions of SQL Server. For more information, see OBJECTPROPERTY (Transact-SQL) and
TYPEPROPERTY (Transact-SQL).
Ownership of the following schema-contained entities of type "object" can be transferred:
tables, views, functions, procedures, queues, and synonyms.
Ownership of the following entities cannot be transferred: linked servers, statistics, constraints,
rules, defaults, triggers, Service Broker queues, credentials, partition functions, partition
schemes, database master keys, service master key, and event notifications.
Ownership of members of the following securable classes cannot be transferred: server, login,
user, application role, and column.
The SCHEMA OWNER option is only valid when you are transferring ownership of a schema-
contained entity. SCHEMA OWNER will transfer ownership of the entity to the owner of the
schema in which it resides. Only entities of class OBJECT, TYPE, or XML SCHEMA COLLECTION
are schema-contained.
If the target entity is not a database and the entity is being transferred to a new owner, all
permissions on the target will be dropped.

Caution
In SQL Server 2005, the behavior of schemas changed from the behavior in earlier
versions of SQL Server. Code that assumes that schemas are equivalent to database users
may not return correct results. Old catalog views, including sysobjects, should not be
used in a database in which any of the following DDL statements has ever been used:
CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP
USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE,
DROP APPROLE, ALTER AUTHORIZATION. In a database in which any of these statements
has ever been used, you must use the new catalog views. The new catalog views take
into account the separation of principals and schemas that was introduced in SQL Server
2005. For more information about catalog views, see Catalog Views (Transact-SQL).
Also, note the following:

Important

19
 The only reliable way to find the owner of a object is to query the sys.objects catalog
view. The only reliable way to find the owner of a type is to use the TYPEPROPERTY
function.
Special Cases and Conditions
The following table lists special cases, exceptions, and conditions that apply to altering
authorization.

Class Condition

DATABASE Cannot change the owner of system

databases master, model, tempdb, the
resource database, or a database that is
used as a distribution database. The
principal must be a login. If the principal is
a Windows login without a corresponding
SQL Server login, the principal must have
CONTROL SERVER permission and TAKE
OWNERSHIP permission on the database. If
the principal is a SQL Server login, the
principal cannot be mapped to a certificate
or asymmetric key. Dependent aliases will
be mapped to the new database owner.
The DBO SID will be updated in both the
current database and in sys.databases.
OBJECT Cannot change ownership of triggers,
constraints, rules, defaults, statistics, system
objects, queues, indexed views, or tables
with indexed views.
SCHEMA When ownership is transferred, permissions
on schema-contained objects that do not
have explicit owners will be dropped.
Cannot change the owner of sys, dbo, or
information_schema.
TYPE Cannot change ownership of a TYPE that
belongs to sys or information_schema.
CONTRACT, MESSAGE TYPE, or SERVICE Cannot change ownership of system
entities.
SYMMETRIC KEY Cannot change ownership of global
temporary keys.

20
Class Condition

CERTIFICATE or ASYMMETRIC KEY Cannot transfer ownership of these entities

to a role or group.
ENDPOINT The principal must be a login.

Permissions
Requires TAKE OWNERSHIP permission on the entity. If the new owner is not the user that is
executing this statement, also requires either, 1) IMPERSONATE permission on the new owner if
it is a user or login; or 2) if the new owner is a role, membership in the role, or ALTER permission
on the role; or 3) if the new owner is an application role, ALTER permission on the application
role.
Examples
A. Transfer ownership of a table
The following example transfers ownership of table Sprockets to user MichikoOsada. The table
is located inside schema Parts.
ALTER AUTHORIZATION ON OBJECT::Parts.Sprockets TO MichikoOsada;
GO
The query could also look like the following:
ALTER AUTHORIZATION ON Parts.Sprockets TO MichikoOsada;
GO

B. Transfer ownership of a view to the schema owner

The following example transfers ownership the view ProductionView06 to the owner of the
schema that contains it. The view is located inside schema Production.
ALTER AUTHORIZATION ON OBJECT::Production.ProductionView06 TO SCHEMA OWNER;
GO

C. Transfer ownership of a schema to a user

The following example transfers ownership of the schema SeattleProduction11 to user
SandraAlayo.
ALTER AUTHORIZATION ON SCHEMA::SeattleProduction11 TO SandraAlayo;
GO

D. Transfer ownership of an endpoint to a SQL Server login

The following example transfers ownership of endpoint CantabSalesServer1 to JaePak.
Because the endpoint is a server-level securable, the endpoint can only be transferred to a
server-level principal.

21
ALTER AUTHORIZATION ON ENDPOINT::CantabSalesServer1 TO JaePak;
GO

See Also
OBJECTPROPERTY (Transact-SQL)
TYPEPROPERTY (Transact-SQL)
EVENTDATA (Transact-SQL)

ALTER AVAILABILITY GROUP

Alters an existing AlwaysOn availability group in SQL Server 2012. Most ALTER AVAILABILITY
GROUP arguments are supported only on the server instance that hosts the current primary
replica. However the JOIN, FAILOVER, and FORCE_FAILOVER_ALLOW_DATA_LOSS arguments,
which can be run only on an secondary replica.
Transact-SQL Syntax Conventions
Syntax

ALTER AVAILABILITY GROUP group_name

{
SET ( <set_option_spec> )
| ADD DATABASE database_name
| REMOVE DATABASE database_name
| ADD REPLICA ON <add_replica_spec>
| MODIFY REPLICA ON <modify_replica_spec>
| REMOVE REPLICA ON <server_instance>
| JOIN
| FAILOVER
| FORCE_FAILOVER_ALLOW_DATA_LOSS
| ADD LISTENER ‘dns_name’ ( <add_listener_option> )
| MODIFY LISTENER ‘dns_name’ ( <modify_listener_option> )
| RESTART LISTENER ‘dns_name’
| REMOVE LISTENER ‘dns_name’
}
[;]

<set_option_spec> ::=

22
 AUTOMATED_BACKUP_PREFERENCE = { PRIMARY | SECONDARY_ONLY| SECONDARY | NONE
}
| FAILURE_CONDITION_LEVEL = { 1 | 2 | 3 | 4 | 5 }
| HEALTH_CHECK_TIMEOUT = milliseconds

<server_instance> ::=
{ 'system_name[\instance_name]' | 'FCI_network_name[\instance_name]' }

<add_replica_spec>::=
<server_instance> WITH
(
ENDPOINT_URL = 'TCP://system-address:port',
AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT },
FAILOVER_MODE = { AUTOMATIC | MANUAL }
[ , <add_replica_option> [ ,...n ] ]
)

<add_replica_option>::=
BACKUP_PRIORITY = n
| SECONDARY_ROLE ( {
ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL }
| READ_ONLY_ROUTING_URL = 'TCP://system-address:port'
})
| PRIMARY_ROLE ( {
ALLOW_CONNECTIONS = { READ_WRITE | ALL }
| READ_ONLY_ROUTING_LIST = { ( ‘<server_instance>’ [ ,...n ] ) | NONE }
})
| SESSION_TIMEOUT = seconds

<modify_replica_spec>::=
<server_instance> WITH
(
ENDPOINT_URL = 'TCP://system-address:port'
| AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT }
| FAILOVER_MODE = { AUTOMATIC | MANUAL }
23
 | BACKUP_PRIORITY = n
| SECONDARY_ROLE ( {
ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL }
| READ_ONLY_ROUTING_URL = 'TCP://system-address:port'
})
| PRIMARY_ROLE ( {
ALLOW_CONNECTIONS = { READ_WRITE | ALL }
| READ_ONLY_ROUTING_LIST = { ( ‘<server_instance>’ [ ,...n ] ) | NONE }
})
| SESSION_TIMEOUT = seconds
)

<add_listener_option> ::=
{
WITH DHCP [ ON ( <network_subnet_option> ) ]
| WITH IP ( { ( <ip_address_option> ) } [ , ...n ] ) [ , PORT = listener_port ]
}

<network_subnet_option> ::=
‘four_part_ipv4_address’, ‘four_part_ipv4_mask’

<ip_address_option> ::=
{
‘four_part_ipv4_address’, ‘four_part_ipv4_mask’
| ‘ipv6_address’
}

<modify_listener_option>::=
{
ADD IP ( <ip_address_option> )
| PORT = listener_port
}

Arguments

24
group_name
Specifies the name of the new availability group. group_name must be a valid SQL Server
identifier, and it must be unique across all availability groups in the WSFC cluster.

AUTOMATED_BACKUP_PREFERENCE = { PRIMARY | SECONDARY_ONLY| SECONDARY | NONE

}
Specifies a preference about how a backup job should evaluate the primary replica when
choosing where to perform backups. You can script a given backup job to take the
automated backup preference into account. It is important to understand that the preference
is not enforced by SQL Server, so it has no impact on ad-hoc backups.
Supported only on the primary replica.
The values are as follows:
PRIMARY
Specifies that the backups should always occur on the primary replica. This option is useful
if you need backup features, such as creating differential backups, that are not supported
when backup is run on a secondary replica.

SECONDARY_ONLY
Specifies that backups should never be performed on the primary replica. If the primary
replica is the only replica online, the backup should not occur.

SECONDARY
Specifies that backups should occur on a secondary replica except when the primary
replica is the only replica online. In that case, the backup should occur on the primary
replica. This is the default behavior.

NONE
Specifies that you prefer that backup jobs ignore the role of the availability replicas when
choosing the replica to perform backups. Note backup jobs might evaluate other factors
such as backup priority of each availability replica in combination with its operational state
and connected state.

There is no enforcement of the AUTOMATED_BACKUP_PREFERENCE setting. The

interpretation of this preference depends on the logic, if any, that you script into back jobs
for the databases in a given availability group. For more information, see Backup on
Secondary Replicas (AlwaysOn Availability Groups).

Note
To view the automated backup preference of an existing availability group, select the
automated_backup_preference or automated_backup_preference_desc column of the
sys.availability_groups catalog view.
FAILURE_CONDITION_LEVEL = { 1 | 2 | 3 | 4 | 5 }
Specifies what failure conditions will trigger an automatic failover for this availability group.
FAILURE_CONDITION_LEVEL is set at the group level but is relevant only on availability

25
replicas that are configured for synchronous-commit availability mode (AVAILIBILITY_MODE
= SYNCHRONOUS_COMMIT). Furthermore, failure conditions can trigger an automatic
failover only if both the primary and secondary replicas are configured for automatic failover
mode (FAILOVER_MODE = AUTOMATIC) and the secondary replica is currently synchronized
with the primary replica.
Supported only on the primary replica.
The failure-condition levels (1–5) range from the least restrictive, level 1, to the most
restrictive, level 5. A given condition level encompasses all of the less restrictive levels. Thus,
the strictest condition level, 5, includes the four less restrictive condition levels (1-4), level 4
includes levels 1-3, and so forth. The following table describes the failure-condition that
corresponds to each level.

Level Failure Condition

1 Specifies that an automatic failover should

be initiated when any of the following
occurs:
• The SQL Server service is down.
• The lease of the availability group for
connecting to the WSFC cluster expires
because no ACK is received from the
server instance.
2 Specifies that an automatic failover should
be initiated when any of the following
occurs:
• The instance of SQL Server does not
connect to cluster, and the user-
specified HEALTH_CHECK_TIMEOUT
threshold of the availability group is
exceeded.
• The availability replica is in failed state.
3 Specifies that an automatic failover should
be initiated on critical SQL Server internal
errors, such as orphaned spinlocks, serious
write-access violations, or too much
dumping.
This is the default behavior.
4 Specifies that an automatic failover should
be initiated on moderate SQL Server
internal errors, such as a persistent out-of-
26
 memory condition in the SQL Server
internal resource pool.
5 Specifies that an automatic failover should
be initiated on any qualified failure
conditions, including:
• Exhaustion of SQL Engine worker-
threads.
• Detection of an unsolvable deadlock.

nNote
Lack of response by an instance of SQL Server to client requests is not relevant to availability groups.
The FAILURE_CONDITION_LEVEL and HEALTH_CHECK_TIMEOUT values, define a flexible
failover policy for a given group. This flexible failover policy provides you with granular
control over what conditions must cause an automatic failover. For more information, see
Flexible Failover Policy for Automatic Failover of an Availability Group (SQL
Server).
HEALTH_CHECK_TIMEOUT = milliseconds
Specifies the wait time (in milliseconds) for the sp_server_diagnostics system stored
procedure to return server-health information before WSFC cluster assumes that the server
instance is slow or hung. HEALTH_CHECK_TIMEOUT is set at the group level but is relevant
only on availability replicas that are configured for synchronous-commit availability mode
with automatic failover (AVAILIBILITY_MODE = SYNCHRONOUS_COMMIT). Furthermore, a
health-check timeout can trigger an automatic failover only if both the primary and
secondary replicas are configured for automatic failover mode (FAILOVER_MODE =
AUTOMATIC) and the secondary replica is currently synchronized with the primary replica.
The default HEALTH_CHECK_TIMEOUT value is 30000 milliseconds (30 seconds). The
minimum value is 15000 milliseconds (15 seconds), and the maximum value is 4294967295
milliseconds.
Supported only on the primary replica.

Important
sp_server_diagnostics does not perform health checks at the database level.

ADD DATABASE database_name

Specifies a list of one or more user databases that you want to add to the availability group.
These databases must reside on the instance of SQL Server that hosts the current primary
replica. You can specify multiple databases for an availability group, but each database can
belong to only one availability group. For information about the type of databases that an
availability group can support, see Prerequisites,
Restrictions, and
Recommendations for AlwaysOn Availability Groups (SQL Server). To find out

27
 which local databases already belong to an availability group, see the replica_id column in
the sys.databases catalog view.
Supported only on the primary replica.

Note
After you have created the availability group, you will need connect to each server instance that hosts
a secondary replica and then prepare each secondary database and join it to the availability group. For
more information, see Start Data Movement on an AlwaysOn Secondary Database (SQL
Server).
REMOVE DATABASE database_name
Removes the specified primary database and the corresponding secondary databases from
the availability group. Supported only on the primary replica.
For information about the recommended follow up after removing an availability database
from an availability group, see Remove a Primary Database from an Availability
Group (SQL Server).
ADD REPLICA ON
Specifies from one to four SQL server instances to host secondary replicas in an availability
group. Each replica is specified by its server instance address followed by a WITH (…) clause.
Supported only on the primary replica.
You need to join every new secondary replica to the availability group. For more information,
see the description of the JOIN option, later in this section.

<server_instance>
Specifies the address of the instance of SQL Server that is the host for an replica. The address
format depends on whether the instance is the default instance or a named instance and
whether it is a standalone instance or a failover cluster instance (FCI). The syntax is as follows:
{ 'system_name[\instance_name]' | 'FCI_network_name[\instance_name]' }
The components of this address are as follows:
system_name
Is the NetBIOS name of the computer system on which the target instance of SQL Server
resides. This computer must be a WSFC node.

FCI_network_name
Is the network name that is used to access a SQL Server failover cluster. Use this if the
server instance participates as a SQL Server failover partner. Executing SELECT
@@SERVERNAME on an FCI server instance returns its entire
'FCI_network_name[\instance_name]' string (which is the full replica name).

instance_name
Is the name of an instance of a SQL Server that is hosted by system_name or
FCI_network_name and that has HADR service is enabled. For a default server instance,

28
 instance_name is optional. The instance name is case insensitive. On a stand-alone server
instance, this value name is the same as the value returned by executing SELECT
@@SERVERNAME.
\
Is a separator used only when specifying instance_name, in order to separate it from
system_name or FCI_network_name.

For information about the prerequisites for WSFC nodes and server instances, see
Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability
Groups (SQL Server).
ENDPOINT_URL = 'TCP://system-address:port'
Specifies the URL path for the database mirroring endpoint on the instance of SQL
Server that will host the availability replica that you are adding or modifying.
ENDPOINT_URL is required in the ADD REPLICA ON clause and optional in the MODIFY
REPLICA ON clause. For more information, see Specify the Endpoint URL When
Adding or Modifying an Availability Replica.
'TCP://system-address:port'
Specifies a URL for specifying an endpoint URL or read-only routing URL. The URL parameters
are as follows:
system-address
Is a string, such as a system name, a fully qualified domain name, or an IP address, that
unambiguously identifies the destination computer system.

port
Is a port number that is associated with the mirroring endpoint of the server instance (for
the ENDPOINT_URL option) or the port number used by the Database Engine of the server
instance (for the READ_ONLY_ROUTING_URL option).

AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT }

Specifies whether the primary replica has to wait for the secondary replica to acknowledge
the hardening (writing) of the log records to disk before the primary replica can commit the
transaction on a given primary database. The transactions on different databases on the
same primary replica can commit independently.
SYNCHRONOUS_COMMIT
Specifies that the primary replica will wait to commit transactions until they have been
hardened on this secondary replica (synchronous-commit mode). You can specify
SYNCHRONOUS_COMMIT for up to three replicas, including the primary replica.

ASYNCHRONOUS_COMMIT
Specifies that the primary replica commits transactions without waiting for this secondary
replica to harden the log (synchronous-commit availability mode). You can specify

29
 ASYNCHRONOUS_COMMIT for up to five availability replicas, including the primary replica.

AVAILABILITY_MODE is required in the ADD REPLICA ON clause and optional in the MODIFY
REPLICA ON clause. For more information, see Availability Modes (AlwaysOn
Availability Groups).
FAILOVER_MODE = { AUTOMATIC | MANUAL }
Specifies the failover mode of the availability replica that you are defining.
AUTOMATIC
Enables automatic failover. AUTOMATIC is supported only if you also specify
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT. You can specify AUTOMATIC for two
availability replicas, including the primary replica.

Note
SQL Server Failover Cluster Instances (FCIs) do not support automatic failover by availability groups,
so any availability replica that is hosted by an FCI can only be configured for manual failover.

MANUAL
Enables manual failover or forced manual failover (forced failover) by the database
administrator.

FAILOVER_MODE is required in the ADD REPLICA ON clause and optional in the MODIFY
REPLICA ON clause. Two types of manual failover exist, manual failover without data loss and
forced failover (with possible data loss), which are supported under different conditions. For
more information, see Failover Modes (AlwaysOn Availability Groups).

BACKUP_PRIORITY = n
Specifies your priority for performing backups on this replica relative to the other replicas in
the same availability group. The value is an integer in the range of 0..100. These values have
the following meanings:
• 1..100 indicates that the availability replica could be chosen for performing backups. 1
indicates the lowest priority, and 100 indicates the highest priority. If BACKUP_PRIORITY
= 1, the availability replica would be chosen for performing backups only if no higher
priority availability replicas are currently available.
• 0 indicates that this availability replica will never be chosen for performing backups. This
is useful, for example, for a remote availability replica to which you never want backups
to fail over.
For more information, see Backup on Secondary Replicas (AlwaysOn Availability
Groups).
SECONDARY_ROLE ( … )
Specifies role-specific settings that will take effect if this availability replica currently owns the
secondary role (that is, whenever it is a secondary replica). Within the parentheses, specify
either or both secondary-role options. If you specify both, use a comma-separated list.

30
 The secondary role options are as follows:
ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL }
Specifies whether the databases of a given availability replica that is performing the
secondary role (that is, is acting as a secondary replica) can accept connections from
clients, one of:
NO
No user connections are allowed to secondary databases of this replica. They are not
available for read access. This is the default behavior.

READ_ONLY
Only connections are allowed to the databases in the secondary replica where the
Application Intent property is set to ReadOnly. For more information about this
property, see Using Connection String Keywords with SQL Server Native
Client.
ALL
All connections are allowed to the databases in the secondary replica for read-only
access.

For more information, see Read-Only Access to Secondary Replicas.

READ_ONLY_ROUTING_URL = 'TCP://system-address:port'
Specifies the URL to be used for routing read-intent connection requests to this availability
replica. This is the URL on which the SQL Server Database Engine listens. Typically, the
default instance of the SQL Server Database Engine listens on TCP port 1433.
For a named instance, you can obtain the port number by querying the port and
type_desc columns of the sys.dm_tcp_listener_states dynamic management view. The
server instance uses the Transact-SQL listener (type_desc = 'TSQL').

Note
For a named instance of SQL Server, the Transact-SQL listener should be configured to use a specific
port. For more information, see Configure a Server to Listen on a Specific TCP Port (SQL
Server Configuration Manager).
PRIMARY_ROLE ( … )
Specifies role-specific settings that will take effect if this availability replica currently owns the
primary role (that is, whenever it is the primary replica). Within the parentheses, specify
either or both primary-role options. If you specify both, use a comma-separated list.
The primary role options are as follows:
ALLOW_CONNECTIONS = { READ_WRITE | ALL }
Specifies the type of connection that the databases of a given availability replica that is
performing the primary role (that is, is acting as a primary replica) can accept from clients,
one of:

31
 READ_WRITE
Connections where the Application Intent connection property is set to ReadOnly are
disallowed. When the Application Intent property is set to ReadWrite or the Application
Intent connection property is not set, the connection is allowed. For more information
about Application Intent connection property, see Using Connection String
Keywords with SQL Server Native Client.
ALL
All connections are allowed to the databases in the primary replica. This is the default
behavior.

READ_ONLY_ROUTING_LIST = { ( ‘<server_instance>’ [ ,...n ] ) | NONE }

Specifies a comma-separated list of server instances that host availability replicas for this
availability group that meet the following requirements when running under the secondary
role:
• Be configured to allow all connections or read-only connections (see the
ALLOW_CONNECTIONS argument of the SECONDARY_ROLE option, above).
• Have their read-only routing URL defined (see the READ_ONLY_ROUTING_URL
argument of the SECONDARY_ROLE option, above).
The READ_ONLY_ROUTING_LIST values are as follows:
<server_instance>
Specifies the address of the instance of SQL Server that is the host for an availability
replica that is a readable secondary replica when running under the secondary role.
Use a comma-separated list to specify all of the server instances that might host a
readable secondary replica. Read-only routing will follow the order in which server
instances are specified in the list. If you include a replica's host server instance on the
replica's read-only routing list, placing this server instance at the end of the list is
typically a good practice, so that read-intent connections go to a secondary replica, if
one is available.

NONE
Specifies that when this availability replica is the primary replica, read-only routing will
not be supported. This is the default behavior. When used with MODIFY REPLICA ON,
this value disables an existing list, if any.

SESSION_TIMEOUT = seconds
Specifies the session-timeout period in seconds. If you do not specify this option, by default,
the time period is 10 seconds. The minimum value is 5 seconds.

Important
We recommend that you keep the time-out period at 10 seconds or greater.
For more information about the session-timeout period, see Overview of AlwaysOn
Availability Groups (SQL Server).
32
MODIFY REPLICA ON
Modifies any of the replicas of the availability group. The list of replicas to be modified
contains the server instance address and a WITH (…) clause for each replica.
Supported only on the primary replica.

REMOVE REPLICA ON
Removes the specified secondary replica from the availability group. The current primary
replica cannot be removed from an availability group. On being removed, the replica stops
receiving data. Its secondary databases are removed from the availability group and enter the
RESTORING state.
Supported only on the primary replica.

Note
If you remove a replica while it is unavailable or failed, when it comes back online it will discover that it
no longer belongs the availability group.

JOIN
Causes the local server instance to host a secondary replica in the specified availability group.
Supported only on a secondary replica that has not yet been joined to the availability group.
For more information, see Join a Secondary Replica to an Availability Group (SQL
Server).
FAILOVER
Initiates a manual failover of the availability group without data loss to the secondary replica
to which you are connected. The secondary replica will take over the primary role and recover
its copy of each database and bring them online as the new primary databases. The former
primary replica concurrently transitions to the secondary role, and its databases become
secondary databases and are immediately suspended. Potentially, these roles can be
switched back and forth by a series of failures.

Note
A failover command returns as soon as the target secondary replica has accepted the command.
However, database recovery occurs asynchronously after the availability group has finished failing
over.
Supported only on a synchronous-commit secondary replica that is currently synchronized
with the primary replica. Note that for a secondary replica to be synchronized the primary
replica must also be running in synchronous-commit mode.
For information about the limitations, prerequisites and recommendations for a performing a
planned manual failover, see Perform a Planned Manual Failover of an Availability
Group (SQL Server).

33
FORCE_FAILOVER_ALLOW_DATA_LOSS

Caution
Forcing service, which might involve some data loss, is strictly a disaster recovery method. Therefore,
We strongly recommend that you force failover only if the primary replica is no longer running, you
are willing to risk losing data, and you must restore service to the availability group immediately.
Forces failover of the availability group, with possible data loss, to the secondary replica to
which you are connected. The secondary replica will take over the primary role and recover
its copy of each database and bring them online as the new primary databases. On any
remaining secondary replicas, every secondary database is suspended until manually
resumed. When the former primary replica becomes available, it will switch to the secondary
role, and its databases will become suspended secondary databases.
Supported only on a secondary replica.

Note
A failover command returns as soon as the target secondary replica has accepted the command.
However, database recovery occurs asynchronously after the availability group has finished failing
over.
For information about the limitations, prerequisites and recommendations for forcing failover
and the effect of a forced failover on the former primary databases, see Perform a Forced
Manual Failover of an Availability Group (SQL Server).
ADD LISTENER ‘dns_name’ ( <add_listener_option> )
Defines a new availability group listener for this availability group. Supported only on the
primary replica.

Important
• Before you create your first listener, we strongly recommend that you read
Prerequisites, Restrictions, and Recommendations for AlwaysOn
Client Connectivity (SQL Server).
• After you create a listener for a given availability group, we strongly recommend
that you do the following:

dns_name
Specifies the DNS host name of the availability group listener. The DNS name of the listener
must be unique in the domain and in NetBIOS.
dns_name is a string value. This name can contain only alphanumeric characters, dashes (-),
and hyphens (_), in any order. DNS host names are case insensitive. The maximum length is
63 characters.
We recommend that you specify a meaningful string. For example, for an availability group
named AG1, a meaningful DNS host name would be ag1-listener.

Important
NetBIOS recognizes only the first 15 chars in the dns_name. If you have two WSFC clusters that are

34
 controlled by the same Active Directory and you try to create availability group listeners in both of
clusters using names with more than 15 characters and an identical 15 character prefix, you will get an
error reporting that the Virtual Network Name resource could not be brought online. For information
about prefix naming rules for DNS names, see Assigning Domain Names.

<add_listener_option>
ADD LISTENER takes one of the following options:
WITH DHCP [ ON { ( ‘four_part_ipv4_address’, ‘four_part_ipv4_mask’ ) } ]
Specifies that the availability group listener will use the Dynamic Host Configuration
Protocol (DHCP). Optionally, use the ON clause to identify the network on which this
listener will be created. DHCP is limited to a single subnet that is used for every server
instances that hosts an availability replica in the availability group.

Important
We do not recommend DHCP in production environment. If there is a down time and the DHCP IP
lease expires, extra time is required to register the new DHCP network IP address that is associated
with the listener DNS name and impact the client connectivity. However, DHCP is good for setting
up your development and testing environment to verify basic functions of availability groups and
for integration with your applications.
For example:
WITH DHCP ON ('10.120.19.0','255.255.254.0')

WITH IP ( { ( ‘four_part_ipv4_address’, ‘four_part_ipv4_mask’ ) | ( ‘ipv6_address’ ) } [ ,

...n ] ) [ , PORT = listener_port ]
Specifies that, instead of using DHCP, the availability group listener will use one or more
static IP addresses. To create an availability group across multiple subnets, each subnet
requires one static IP address in the listener configuration. For a given subnet, the static IP
address can be either an IPv4 address or an IPv6 address. Contact your network
administrator to get a static IP address for each subnet that will host an availability replica
for the new availability group.
For example:
WITH IP ( ('10.120.19.155','255.255.254.0') )

four_part_ipv4_address
Specifies an IPv4 four-part address for an availability group listener. For example,
10.120.19.155.

four_part_ipv4_mask
Specifies an IPv4 four-part mask for an availability group listener. For example,
255.255.254.0.

ipv6_address
Specifies an IPv6 address for an availability group listener. For example,
2001::4898:23:1002:20f:1fff:feff:b3a3.

35
PORT = listener_port
Specifies the port number—listener_port—to be used by an availability group listener that is
specified by a WITH IP clause. PORT is optional.
The default port number, 1433, is supported. However, if you have security concerns, we
recommend using a different port number.
For example: WITH IP ( ('2001::4898:23:1002:20f:1fff:feff:b3a3')
) , PORT = 7777

MODIFY LISTENER ‘dns_name’ ( <modify_listener_option> )

Modifies an existing availability group listener for this availability group. Supported only on
the primary replica.

<modify_listener_option>
MODIFY LISTENER takes one of the following options:
ADD IP { ( ‘four_part_ipv4_address’, ‘four_part_ipv4_mask’ ) | ( ‘dns_nameipv6_address’
)}
Adds the specified IP address to the availability group listener specified by dns_name.

PORT = listener_port
See the description of this argument earlier in this section.

RESTART LISTENER ‘dns_name’

Restarts the listener that is associated with the specified DNS name. Supported only on the
primary replica.

REMOVE LISTENER ‘dns_name’

Removes the listener that is associated with the specified DNS name. Supported only on the
primary replica.

Prerequisites and Restrictions

For information about prerequisites and restrictions on availability replicas and on their host
server instances and computers, see Prerequisites, Restrictions, and Recommendations for
AlwaysOn Availability Groups (SQL Server).
For information about restrictions on the AVAILABILITY GROUP Transact-SQL statements, see
Overview of "HADR" Transact-SQL Statements (SQL Server).
Security
Permissions
Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL
AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL
SERVER permission.
Examples
36
• A. Joining a secondary replica to an availability group
• B. Forcing failover of an availability group
A. Joining a secondary replica to an availability group
The following example, joins a secondary replica to which you are connected to the AccountsAG
availability group.
ALTER AVAILABILITY GROUP AccountsAG JOIN;
GO

B. Forcing failover of an availability group

The following example forces the AccountsAG availability group to fail over to the secondary
replica to which you are connected.
ALTER AVAILABILITY GROUP AccountsAG FORCE_FAILOVER_ALLOW_DATA_LOSS;
GO

See Also
CREATE AVAILABILITY GROUP (Transact-SQL)
ALTER DATABASE SET HADR (Transact-SQL)
DROP AVAILABILITY GROUP (Transact-SQL)
sys.availability_replicas (Transact-SQL)
sys.availability_groups (Transact-SQL)
Troubleshooting AlwaysOn Availability Groups Configuration (SQL Server)
Overview of AlwaysOn Availability Groups (SQL Server)
Client Connectivity and Application Failover (AlwaysOn Availability Groups)

ALTER BROKER PRIORITY

Changes the properties of a Service Broker conversation priority.
Transact-SQL Syntax Conventions
Syntax

ALTER BROKER PRIORITY ConversationPriorityName

FOR CONVERSATION
{ SET ( [ CONTRACT_NAME = {ContractName | ANY } ]
[ [ , ] LOCAL_SERVICE_NAME = {LocalServiceName | ANY } ]
[ [ , ] REMOTE_SERVICE_NAME = {'RemoteServiceName' | ANY } ]
[ [ , ] PRIORITY_LEVEL = { PriorityValue | DEFAULT } ]

37
 )
}
[;]

Arguments
ConversationPriorityName
Specifies the name of the conversation priority to be changed. The name must refer to a
conversation priority in the current database.

SET
Specifies the criteria for determining if the conversation priority applies to a conversation.
SET is required and must contain at least one criterion: CONTRACT_NAME,
LOCAL_SERVICE_NAME, REMOTE_SERVICE_NAME, or PRIORITY_LEVEL.

CONTRACT_NAME = {ContractName | ANY}

Specifies the name of a contract to be used as a criterion for determining if the conversation
priority applies to a conversation. ContractName is a Database Engine identifier, and must
specify the name of a contract in the current database.
ContractName
Specifies that the conversation priority can be applied only to conversations where the
BEGIN DIALOG statement that started the conversation specified ON CONTRACT
ContractName.

ANY
Specifies that the conversation priority can be applied to any conversation, regardless of
which contract it uses.

If CONTRACT_NAME is not specified, the contract property of the conversation priority is not
changed.

LOCAL_SERVICE_NAME = {LocalServiceName | ANY}

Specifies the name of a service to be used as a criterion to determine if the conversation
priority applies to a conversation endpoint.
LocalServiceName is a Database Engine identifier and must specify the name of a service in
the current database.
LocalServiceName
Specifies that the conversation priority can be applied to the following:
• Any initiator conversation endpoint whose initiator service name matches
LocalServiceName.
• Any target conversation endpoint whose target service name matches
LocalServiceName.

38
 ANY
• Specifies that the conversation priority can be applied to any conversation endpoint,
regardless of the name of the local service used by the endpoint.

If LOCAL_SERVICE_NAME is not specified, the local service property of the conversation

priority is not changed.

REMOTE_SERVICE_NAME = {'RemoteServiceName' | ANY}

Specifies the name of a service to be used as a criterion to determine if the conversation
priority applies to a conversation endpoint.
RemoteServiceName is a literal of type nvarchar(256). Service Broker uses a byte-by-byte
comparison to match the RemoteServiceName string. The comparison is case-sensitive and
does not consider the current collation. The target service can be in the current instance of
the Database Engine, or a remote instance of the Database Engine.
'RemoteServiceName'
Specifies the conversation priority be assigned to the following:
• Any initiator conversation endpoint whose associated target service name matches
RemoteServiceName.
• Any target conversation endpoint whose associated initiator service name matches
RemoteServiceName.

ANY
Specifies that the conversation priority applies to any conversation endpoint, regardless of
the name of the remote service associated with the endpoint.

If REMOTE_SERVICE_NAME is not specified, the remote service property of the conversation

priority is not changed.

PRIORITY_LEVEL = { PriorityValue | DEFAULT }

Specifies the priority level to assign any conversation endpoint that use the contracts and
services that are specified in the conversation priority. PriorityValue must be an integer literal
from 1 (lowest priority) to 10 (highest priority).
If PRIORITY_LEVEL is not specified, the priority level property of the conversation priority is
not changed.

Remarks
No properties that are changed by ALTER BROKER PRIORITY are applied to existing
conversations. The existing conversations continue with the priority that was assigned when they
were started.
For more information, see CREATE BROKER PRIORITY (Transact-SQL).
Permissions

39
Permission for creating a conversation priority defaults to members of the db_ddladmin or
db_owner fixed database roles, and to the sysadmin fixed server role. Requires ALTER
permission on the database.
Examples
A. Changing only the priority level of an existing conversation priority.
Changes the priority level, but does not change the contract, local service, or remote service
properties.
ALTER BROKER PRIORITY SimpleContractDefaultPriority
FOR CONVERSATION
SET (PRIORITY_LEVEL = 3);

B. Changing all of the properties of an existing conversation priority.

Changes the priority level, contract, local service, and remote service properties.
ALTER BROKER PRIORITY SimpleContractPriority
FOR CONVERSATION
SET (CONTRACT_NAME = SimpleContractB,
LOCAL_SERVICE_NAME = TargetServiceB,
REMOTE_SERVICE_NAME = N'InitiatorServiceB',
PRIORITY_LEVEL = 8);

See Also
CREATE BROKER PRIORITY (Transact-SQL)
DROP BROKER PRIORITY (Transact-SQL)
sys.conversation_priorities (Transact-SQL)

ALTER CERTIFICATE
Changes the private key used to encrypt a certificate, or adds one if none is present. Changes
the availability of a certificate to Service Broker.
Transact-SQL Syntax Conventions
Syntax

ALTER CERTIFICATE certificate_name

REMOVE PRIVATE KEY
|
WITH PRIVATE KEY ( <private_key_spec> [ ,... ] )
|
40
 WITH ACTIVE FOR BEGIN_DIALOG = [ ON | OFF ]

<private_key_spec> ::=
FILE = 'path_to_private_key'
|
DECRYPTION BY PASSWORD = 'key_password'
|
ENCRYPTION BY PASSWORD = 'password'
Arguments
certificate_name
Is the unique name by which the certificate is known in database.

FILE = 'path_to_private_key'
Specifies the complete path, including file name, to the private key. This parameter can be a
local path or a UNC path to a network location. This file will be accessed within the security
context of the SQL Server service account. When you use this option, you must make sure
that the service account has access to the specified file.

DECRYPTION BY PASSWORD = 'key_password'

Specifies the password that is required to decrypt the private key.

ENCRYPTION BY PASSWORD = 'password'

Specifies the password used to encrypt the private key of the certificate in the database.
password must meet the Windows password policy requirements of the computer that is
running the instance of SQL Server. For more information, see EVENTDATA (Transact-
SQL).
REMOVE PRIVATE KEY
Specifies that the private key should no longer be maintained inside the database.

ACTIVE FOR BEGIN_DIALOG = { ON | OFF }

Makes the certificate available to the initiator of a Service Broker dialog conversation.

Remarks
The private key must correspond to the public key specified by certificate_name.
The DECRYPTION BY PASSWORD clause can be omitted if the password in the file is protected
with a null password.
When the private key of a certificate that already exists in the database is imported from a file,
the private key will be automatically protected by the database master key. To protect the
private key with a password, use the ENCRYPTION BY PASSWORD phrase.

41
The REMOVE PRIVATE KEY option will delete the private key of the certificate from the database.
You can do this when the certificate will be used to verify signatures or in Service Broker
scenarios that do not require a private key. Do not remove the private key of a certificate that
protects a symmetric key.
You do not have to specify a decryption password when the private key is encrypted by using
the database master key.

Important
Always make an archival copy of a private key before removing it from a database. For
more information, see BACKUP CERTIFICATE (Transact-SQL).
The WITH PRIVATE KEY option is not available in a contained database.
Permissions
Requires ALTER permission on the certificate.
Examples
A. Changing the password of a certificate
ALTER CERTIFICATE Shipping04
WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'pGF$5DGvbd2439587y',
ENCRYPTION BY PASSWORD = '4-329578thlkajdshglXCSgf');
GO

B. Changing the password that is used to encrypt the private key

ALTER CERTIFICATE Shipping11
WITH PRIVATE KEY (ENCRYPTION BY PASSWORD = '34958tosdgfkh##38',
DECRYPTION BY PASSWORD = '95hkjdskghFDGGG4%');
GO

C. Importing a private key for a certificate that is already present in the database
ALTER CERTIFICATE Shipping13
WITH PRIVATE KEY (FILE = 'c:\\importedkeys\Shipping13',
DECRYPTION BY PASSWORD = 'GDFLKl8^^GGG4000%');
GO

D. Changing the protection of the private key from a password to the database
master key
ALTER CERTIFICATE Shipping15
WITH PRIVATE KEY (DECRYPTION BY PASSWORD = '95hk000eEnvjkjy#F%');
GO

42
See Also
CREATE CERTIFICATE (Transact-SQL)
DROP CERTIFICATE (Transact-SQL)
BACKUP CERTIFICATE (Transact-SQL)
Encryption Hierarchy
EVENTDATA (Transact-SQL)

ALTER CREDENTIAL
Changes the properties of a credential.
Transact-SQL Syntax Conventions
Syntax

ALTER CREDENTIAL credential_name WITH IDENTITY = 'identity_name'

[ , SECRET = 'secret' ]
Arguments
credential_name
Specifies the name of the credential that is being altered.

IDENTITY = 'identity_name'
Specifies the name of the account to be used when connecting outside the server.

SECRET = 'secret'
Specifies the secret required for outgoing authentication. secret is optional.

Remarks
When a credential is changed, the values of both identity_name and secret are reset. If the
optional SECRET argument is not specified, the value of the stored secret will be set to NULL.
The secret is encrypted by using the service master key. If the service master key is regenerated,
the secret is reencrypted by using the new service master key.
Information about credentials is visible in the sys.credentials catalog view.
Permissions
Requires ALTER ANY CREDENTIAL permission. If the credential is a system credential, requires
CONTROL SERVER permission.
Examples
A. Changing the password of a credential

43
The following example changes the secret stored in a credential called Saddles. The credential
contains the Windows login RettigB and its password. The new password is added to the
credential using the SECRET clause.
ALTER CREDENTIAL Saddles WITH IDENTITY = 'RettigB',
SECRET = 'sdrlk8$40-dksli87nNN8';
GO

B. Removing the password from a credential

The following example removes the password from a credential named Frames. The credential
contains Windows login Aboulrus8 and a password. After the statement is executed, the
credential will have a NULL password because the SECRET option is not specified.
ALTER CREDENTIAL Frames WITH IDENTITY = 'Aboulrus8';
GO

See Also
sys.credentials (Transact-SQL)
CREATE CREDENTIAL (Transact-SQL)
DROP CREDENTIAL (Transact-SQL)
CREATE LOGIN (Transact-SQL)
sys.credentials (Transact-SQL)

ALTER CRYPTOGRAPHIC PROVIDER

Alters a cryptographic provider within SQL Server from an Extensible Key Management (EKM)
provider.
Transact-SQL Syntax Conventions
Syntax

ALTER CRYPTOGRAPHIC PROVIDER provider_name

[ FROM FILE = path_of_DLL ]
ENABLE | DISABLE
Arguments
provider_name
Name of the Extensible Key Management provider.

Path_of_DLL
Path of the .dll file that implements the SQL Server Extensible Key Management interface.

44
ENABLE | DISABLE
Enables or disables a provider.

Remarks
If the provider changes the .dll file that is used to implement Extensible Key Management in SQL
Server, you must use the ALTER CRYPTOGRAPHIC PROVIDER statement.
When the .dll file path is updated by using the ALTER CRYPTOGRAPHIC PROVIDER statement,
SQL Server performs the following actions:
• Disables the provider.
• Verifies the DLL signature and ensures that the .dll file has the same GUID as the one
recorded in the catalog.
• Updates the DLL version in the catalog.
When an EKM provider is set to DISABLE, any attempts on new connections to use the provider
with encryption statements will fail.
To disable a provider, all sessions that use the provider must be terminated.
When an EKM provider dll does not implement all of the necessary methods, ALTER
CRYPTOGRAPHIC PROVIDER can return error 33085:
One or more methods cannot be found in cryptographic provider library
'%.*ls'.
When the header file used to create the EKM provider dll is out of date, ALTER CRYPTOGRAPHIC
PROVIDER can return error 33032:
SQL Crypto API version '%02d.%02d' implemented by provider is not supported.
Supported version is '%02d.%02d'.

Permissions
Requires CONTROL permission on the cryptographic provider.
Examples
The following example alters a cryptographic provider, called SecurityProvider in SQL
Server, to a newer version of a .dll file. This new version is
named c:\SecurityProvider\SecurityProvider_v2.dll and is installed on the server. The
provider's certificate must be installed on the server.
/* First, disable the provider to perform the upgrade.
This will terminate all open cryptographic sessions */
ALTER CRYPTOGRAPHIC PROVIDER SecurityProvider
DISABLE;
GO

/* Upgrade the provider .dll file. The GUID must the same

45
as the previous version, but the version can be different. */
ALTER CRYPTOGRAPHIC PROVIDER SecurityProvider
FROM FILE = 'c:\SecurityProvider\SecurityProvider_v2.dll';
GO

/* Enable the upgraded provider. */

ALTER CRYPTOGRAPHIC PROVIDER SecurityProvider
ENABLE;
GO

See Also
Understanding Extensible Key Management (EKM)
CREATE CRYPTOGRAPHIC PROVIDER (Transact-SQL)
DROP CRYPTOGRAPHIC PROVIDER (Transact-SQL)
CREATE SYMMETRIC KEY (Transact-SQL)

ALTER DATABASE
Modifies a database, or the files and filegroups associated with the 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
cannot be modified. To modify database options associated with replication, use
sp_replicationdboption.
Because of its length, the ALTER DATABASE syntax is separated into the following topics:
ALTER DATABASE
The current topic provides the syntax for changing the name and the collation of a database.

ALTER DATABASE File and Filegroup Options

Provides the syntax 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 for changing the attributes of a database by using the SET options of
ALTER DATABASE.

ALTER DATABASE Database Mirroring

Provides the syntax for the SET options of ALTER DATABASE that are related to database
mirroring.

ALTER DATABASE SET HADR

Provides the syntax for the AlwaysOn Availability Groups options of ALTER DATABASE for

46
 configuring a secondary database on a secondary replica of an AlwaysOn availability group.

ALTER DATABASE Compatibility Level

Provides the syntax for the SET options of ALTER DATABASE that are related to database
compatibility levels.

Transact-SQL Syntax Conventions

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> ::=
<external_access_option> ::=
<FILESTREAM_options> ::=

47
 <HADR_options> ::=
<parameterization_option> ::=
<recovery_option> ::=
<service_broker_option> ::=
<snapshot_option> ::=
<sql_option> ::=
<termination> ::=
Arguments
database_name
Is the name of the database to be modified.

Note
This option is not available in a Contained Database.

CURRENT
Designates that the current database in use should be altered.

CONTAINMENT
Specifies the containment status of the database. OFF = non-contained database. PARTIAL =
partially contained database.

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.
For more information about the Windows and SQL collation names, see COLLATE
(Transact-SQL).
<file_and_filegroup_options >::=
For more information, see ALTER DATABASE File and Filegroup Options (Transact-SQL).
<set_database_options >::=
For more information, see ALTER DATABASE SET Options (Transact-SQL), ALTER DATABASE
Database Mirroring (Transact-SQL), ALTER DATABASE SET HADR (Transact-SQL), and ALTER
DATABASE Compatibility Level (Transact-SQL).
Remarks
To remove a database, use DROP DATABASE.
To decrease the size of a database, use DBCC SHRINKDATABASE.

48
The ALTER DATABASE statement must run in autocommit mode (the default transaction
management mode) and is not allowed in an explicit or implicit transaction.
In SQL Server 2005 or later, 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
try to access the filegroup by an SQL statement will fail 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 will fail.
When a database is in the RESTORING state, most ALTER DATABASE statements will fail. The
exception is setting database mirroring options. A database may 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:

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

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.
Changing the Database Collation
Before you apply a different collation to a database, make sure that the following conditions are
in place:
1. You are the only one currently using the database.
2. No schema-bound object depends on the collation of the database.

49
 If the following objects, which depend on the database collation, exist in the database, the
ALTER DATABASE database_name COLLATE statement will fail. SQL Server will return 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 may 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 will cause the change action to fail, and SQL
Server will return an error message specifying the namespace where the duplicate was found.
Viewing 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. Changing the name of a database
The following example changes the name of the AdventureWorks2012 database to Northwind.
USE master;
GO
ALTER DATABASE AdventureWorks2012
Modify Name = Northwind ;
GO

50
B. Changing 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.
USE master;
GO

CREATE DATABASE testdb

COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

ALTER DATABASE testDB

COLLATE French_CI_AI ;
GO

See Also
CREATE DATABASE
DATABASEPROPERTYEX
DROP DATABASE
SET TRANSACTION ISOLATION LEVEL
EVENTDATA
sp_configure
sp_spaceused
sys.databases (Transact-SQL)
sys.database_files
sys.database_mirroring_witnesses
sys.data_spaces (Transact-SQL)
sys.filegroups
sys.master_files (Transact-SQL)
System Databases

ALTER DATABASE File and Filegroup Options

Modifies the files and filegroups associated with the database. Adds or removes files and
filegroups from a database, and changes the attributes of a database or its files and filegroups.
For other ALTER DATABASE options, see ALTER DATABASE (Transact-SQL).
Transact-SQL Syntax Conventions

51
Syntax

ALTER DATABASE database_name

{
<add_or_modify_files>
| <add_or_modify_filegroups>
}
[;]

<add_or_modify_files>::=
{
ADD FILE <filespec> [ ,...n ]
[ TO FILEGROUP { filegroup_name } ]
| ADD LOG FILE <filespec> [ ,...n ]
| REMOVE FILE logical_file_name
| MODIFY FILE <filespec>
}

<filespec>::=
(
NAME = logical_file_name
[ , NEWNAME = new_logical_name ]
[ , FILENAME = {'os_file_name' | 'filestream_path' } ]
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
[ , OFFLINE ]
)

<add_or_modify_filegroups>::=
{
| ADD FILEGROUP filegroup_name
[ CONTAINS FILESTREAM ]
| REMOVE FILEGROUP filegroup_name
| MODIFY FILEGROUP filegroup_name

52
 { <filegroup_updatability_option>
| DEFAULT
| NAME = new_filegroup_name
}
}
<filegroup_updatability_option>::=
{
{ READONLY | READWRITE }
| { READ_ONLY | READ_WRITE }
}

Arguments
<add_or_modify_files>::=
Specifies the file to be added, removed, or modified.
database_name
Is the name of the database to be modified.

ADD FILE
Adds a file to the database.
TO FILEGROUP { filegroup_name }
Specifies the filegroup to which to add the specified file. To display the current filegroups
and which filegroup is the current default, use the sys.filegroups catalog view.

ADD LOG FILE

Adds a log file be added to the specified database.

REMOVE FILE logical_file_name

Removes the logical file description from an instance of SQL Server and deletes the physical
file. The file cannot be removed unless it is empty.
logical_file_name
Is the logical name used in SQL Server when referencing the file.

MODIFY FILE
Specifies the file that should be modified. Only one <filespec> property can be changed at a
time. NAME must always be specified in the <filespec> to identify the file to be modified. If
SIZE is specified, the new size must be larger than the current file size.
To modify the logical name of a data file or log file, specify the logical file name to be
renamed in the NAME clause, and specify the new logical name for the file in the NEWNAME
clause. For example:
MODIFY FILE ( NAME = logical_file_name, NEWNAME =
53
 new_logical_name )
To move a data file or log file to a new location, specify the current logical file name in the
NAME clause and specify the new path and operating system file name in the FILENAME
clause. For example:
MODIFY FILE ( NAME = logical_file_name, FILENAME = '
new_path/os_file_name ' )
When you move a full-text catalog, specify only the new path in the FILENAME clause. Do not
specify the operating-system file name.
For more information, see Moving Database Files.
For a FILESTREAM filegroup, NAME can be modified online. FILENAME can be modified
online; however, the change does not take effect until after the container is physically
relocated and the server is shutdown and then restarted.
You can set a FILESTREAM file to OFFLINE. When a FILESTREAM file is offline, its parent
filegroup will be internally marked as offline; therefore, all access to FILESTREAM data within
that filegroup will fail.

Note
<add_or_modify_files> options are not available in a Contained Database.
<filespec>::=
Controls the file properties.
NAME logical_file_name
Specifies the logical name of the file.
logical_file_name
Is the logical name used in an instance of SQL Server when referencing the file.

NEWNAME new_logical_file_name
Specifies a new logical name for the file.
new_logical_file_name
Is the name to replace the existing logical file name. The name must be unique within the
database and comply with the rules for identifiers. The name can be a character or
Unicode constant, a regular identifier, or a delimited identifier.

FILENAME { 'os_file_name' | 'filestream_path' }

Specifies the operating system (physical) file name.
' os_file_name '
For a standard (ROWS) filegroup, this is the path and file name that is used by the
operating system when you create the file. The file must reside on the server on which SQL
Server is installed. The specified path must exist before executing the ALTER DATABASE
statement.
SIZE, MAXSIZE, and FILEGROWTH parameters cannot be set when a UNC path is specified

54
 for the file.

Note
System databases cannot reside on UNC share directories.
Data files should not be put on compressed file systems unless the files are read-only
secondary files, or if the database is read-only. Log files should never be put on
compressed file systems.
If the file is on a raw partition, os_file_name must specify only the drive letter of an existing
raw partition. Only one file can be put on each raw partition.

'filestream_path'
For a FILESTREAM filegroup, FILENAME refers to a path where FILESTREAM data will be
stored. The path up to the last folder must exist, and the last folder must not exist. For
example, if you specify the path C:\MyFiles\MyFilestreamData, C:\MyFiles must exist before
you run ALTER DATABASE, but the MyFilestreamData folder must not exist.
The filegroup and file (<filespec>) must be created in the same statement.
The SIZEand FILEGROWTH properties do not apply to a FILESTREAM filegroup.

SIZE size
Specifies the file size. SIZE does not apply to FILESTREAM filegroups.
size
Is the size of the file.
When specified with ADD FILE, size is the initial size for the file. When specified with
MODIFY FILE, size is the new size for the file, and must be larger than the current file size.
When size is not supplied for the primary file, the SQL Server uses the size of the primary
file in the model database. When a secondary data file or log file is specified but size is not
specified for the file, the Database Engine makes the file 1 MB.
The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or
terabytes. The default is MB. Specify a whole number and do not include a decimal. To
specify a fraction of a megabyte, convert the value to kilobytes by multiplying the number
by 1024. For example, specify 1536 KB instead of 1.5 MB (1.5 x 1024 = 1536).

MAXSIZE { max_size| UNLIMITED }

Specifies the maximum file size to which the file can grow.
max_size
Is the maximum file size. The KB, MB, GB, and TB suffixes can be used to specify kilobytes,
megabytes, gigabytes, or terabytes. The default is MB. Specify a whole number and do not
include a decimal. If max_size is not specified, the file size will increase until the disk is full.

UNLIMITED
Specifies that the file grows until the disk is full. In SQL Server, a log file specified with
unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16

55
 TB. There is no maximum size when this option is specified for a FILESTREAM container. It
continues to grow until the disk is full.

FILEGROWTH growth_increment
Specifies the automatic growth increment of the file. The FILEGROWTH setting for a file
cannot exceed the MAXSIZE setting. FILEGROWTH does not apply to FILESTREAM filegroups.
growth_increment
Is the amount of space added to the file every time new space is required.
The value can be specified in MB, KB, GB, TB, or percent (%). If a number is specified
without an MB, KB, or % suffix, the default is MB. When % is specified, the growth
increment size is the specified percentage of the size of the file at the time the increment
occurs. The size specified is rounded to the nearest 64 KB.
A value of 0 indicates that automatic growth is set to off and no additional space is
allowed.
If FILEGROWTH is not specified, the default value is 1 MB for data files and 10% for log
files, and the minimum value is 64 KB.

Note
Starting in SQL Server 2005, the default growth increment for data files has changed from 10% to 1
MB. The log file default of 10% remains unchanged.

OFFLINE
Sets the file offline and makes all objects in the filegroup inaccessible.

Caution
Use this option only when the file is corrupted and can be restored. A file set to OFFLINE can only be
set online by restoring the file from backup. For more information about restoring a single file, see
RESTORE (Transact-SQL).

Note
<filespec> options are not available in a Contained Database.
<add_or_modify_filegroups>::=
Add, modify, or remove a filegroup from the database.
ADD FILEGROUP filegroup_name
Adds a filegroup to the database.

CONTAINS FILESTREAM
Specifies that the filegroup stores FILESTREAM binary large objects (BLOBs) in the file system.

REMOVE FILEGROUP filegroup_name

Removes a filegroup from the database. The filegroup cannot be removed unless it is empty.
Remove all files from the filegroup first. For more information, see "REMOVE FILE
logical_file_name," earlier in this topic.

56
 Note
Unless the FILESTREAM Garbage Collector has removed all the files from a FILESTREAM container, the
ALTER DATABASE REMOVE FILE operation to remove a FILESTREAM container will fail and return an
error. See the "Remove FILESTREAM Container" section in Remarks later in this topic.

MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME =

new_filegroup_name }
Modifies the filegroup by setting the status to READ_ONLY or READ_WRITE, making the
filegroup the default filegroup for the database, or changing the filegroup name.
<filegroup_updatability_option>
Sets the read-only or read/write property to the filegroup.

DEFAULT
Changes the default database filegroup to filegroup_name. Only one filegroup in the
database can be the default filegroup. For more information, see Understanding Files
and Filegroups.
NAME = new_filegroup_name
Changes the filegroup name to the new_filegroup_name.

<filegroup_updatability_option>::=
Sets the read-only or read/write property to the filegroup.
READ_ONLY | READONLY
Specifies the filegroup is read-only. Updates to objects in it are not allowed. The primary
filegroup cannot be made read-only. To change this state, you must have exclusive access to
the database. For more information, see the SINGLE_USER clause.
Because a read-only database does not allow data modifications:
• Automatic recovery is skipped at system startup.
• Shrinking the database is not possible.
• No locking occurs in read-only databases. This can cause faster query performance.

Note
The keyword READONLY will be removed in a future version of Microsoft SQL Server. Avoid using
READONLY in new development work, and plan to modify applications that currently use READONLY.
Use READ_ONLY instead.

READ_WRITE | READWRITE
Specifies the group is READ_WRITE. Updates are enabled for the objects in the filegroup. To
change this state, you must have exclusive access to the database. For more information, see
the SINGLE_USER clause.

Note
The keyword READWRITE will be removed in a future version of Microsoft SQL Server. Avoid using

57
 READWRITE in new development work, and plan to modify applications that currently use READWRITE.
Use READ_WRITE instead.

The status of these options can be determined by examining the is_read_only column in the
sys.databases catalog view or the Updateability property of the DATABASEPROPERTYEX
function.
Remarks
To decrease the size of a database, use DBCC SHRINKDATABASE.
You cannot add or remove a file while a BACKUP statement is running.
A maximum of 32,767 files and 32,767 filegroups can be specified for each database.
In SQL Server 2005 or later, 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
try to access the filegroup by an SQL statement will fail 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 will fail.
Moving Files
In SQL Server 2005 or later, you can move system or user-defined data and log files by
specifying the new location in FILENAME. This may be useful in the following scenarios:
• Failure recovery. For example, the database is in suspect mode or shutdown caused by
hardware failure
• Planned relocation
• Relocation for scheduled disk maintenance
For more information, see Moving Database Files.
Initializing Files
By default, data and log files are initialized by filling the files with zeros when you perform one
of the following operations:
• Create a database
• Add files to an existing database
• Increase the size of an existing file
• Restore a database or filegroup
Data files can be initialized instantaneously. This enables for fast execution of these file
operations.
Removing a FILESTREAM Container

58
Even though FILESTREAM container may have been emptied using the “DBCC SHRINKFILE”
operation, the database may still need to maintain references to the deleted files for various
system maintenance reasons. sp_filestream_force_garbage_collection (Transact-SQL) will run the
FILESTREAM Garbage Collector to remove these files when it is safe to do so. Unless the
FILESTREAM Garbage Collector has removed all the files from a FILESTREAM container, the
ALTER DATABASEREMOVE FILE operation will fail to remove a FILESTREAM container and will
return an error. The following process is recommended to remove a FILESTREAM container.
1. Run DBCC SHRINKFILE with the EMPTYFILE option to move the active contents of this
container to other containers.
2. Ensure that Log backups have been taken, in the FULL or BULK_LOGGED recovery model.
3. Ensure that the replication log reader job has been run, if relevant.
4. Run sp_filestream_force_garbage_collection to force the garbage collector to delete any files
that are no longer needed in this container.
5. Execute ALTER DATABASE with the REMOVE FILE option to remove this container.
6. Repeat steps 2 through 4 once more to complete the garbage collection.
7. Use ALTER Database...REMOVE FILE to remove this container.
Examples
A. Adding a file to a database
The following example adds a 5-MB data file to the AdventureWorks2012 database.
USE master;
GO
ALTER DATABASE AdventureWorks2012
ADD FILE
(
NAME = Test1dat2,
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat2.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
);
GO
B. Adding a filegroup with two files to a database
The following example creates the filegroup Test1FG1 in the AdventureWorks2012 database
and adds two 5-MB files to the filegroup.
USE master
GO

59
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2012
ADD FILE
(
NAME = test1dat3,
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
),
(
NAME = test1dat4,
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1;
GO

C. Adding two log files to a database

The following example adds two 5-MB log files to the AdventureWorks2012 database.
USE master;
GO
ALTER DATABASE AdventureWorks2012
ADD LOG FILE
(
NAME = test1log2,
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test2log.ldf',
SIZE = 5MB,
MAXSIZE = 100MB,

60
 FILEGROWTH = 5MB
),
(
NAME = test1log3,
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test3log.ldf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
);
GO

D. Removing a file from a database

The following example removes one of the files added in example B.
USE master;
GO
ALTER DATABASE AdventureWorks2012
REMOVE FILE test1dat4;
GO

E. Modifying a file
The following example increases the size of one of the files added in example B.
USE master;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILE
(NAME = test1dat3,
SIZE = 20MB);
GO

F. Moving a file to a new location

The following example moves the Test1dat2 file created in example A to a new directory.

Note

61
 You must physically move the file to the new directory before running this example.
Afterward, stop and start the instance of SQL Server or take the database OFFLINE
and then ONLINE to implement the change.
USE master;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILE
(
NAME = Test1dat2,
FILENAME = N'c:\t1dat2.ndf'
);
GO
G. Moving tempdb to a new location
The following example moves tempdb from its current location on the disk to another disk
location. Because tempdb is re-created each time the MSSQLSERVER service is started, you do
not have to physically move the data and log files. The files are created when the service is
restarted in step 3. Until the service is restarted, tempdb continues to function in its existing
location.
1. Determine the logical file names of the tempdb database and their current location on disk.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO
2. Change the location of each file by using ALTER DATABASE.
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
GO
3. Stop and restart the instance of SQL Server.
4. Verify the file change.
SELECT name, physical_name

62
 FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
5. Delete the tempdb.mdf and templog.ldf files from their original location.
H. Making a filegroup the default
The following example makes the Test1FG1 filegroup created in example B the default
filegroup. Then, the default filegroup is reset to the PRIMARY filegroup. Note that PRIMARY must
be delimited by brackets or quotation marks.
USE master;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks2012
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

I. Adding a Filegroup Using ALTER DATABASE

The following example adds a FILEGROUP that contains the FILESTREAM clause to the
FileStreamPhotoDB database.
--Create and add a FILEGROUP that CONTAINS the FILESTREAM clause to
--the FileStreamPhotoDB database.
ALTER database FileStreamPhotoDB
ADD FILEGROUP TodaysPhotoShoot
CONTAINS FILESTREAM
GO

--Add a file for storing database photos to FILEGROUP

ALTER database FileStreamPhotoDB
ADD FILE
(
NAME= 'PhotoShoot1',
FILENAME = 'C:\Users\Administrator\Pictures\TodaysPhotoShoot.ndf'
)
TO FILEGROUP TodaysPhotoShoot
GO

63
See Also
CREATE DATABASE
DATABASEPROPERTYEX
DROP DATABASE
sp_spaceused
sys.databases (Transact-SQL)
sys.database_files
sys.data_spaces (Transact-SQL)
sys.filegroups
sys.master_files (Transact-SQL)
Designing and Implementing FILESTREAM Storage
DBCC SHRINKFILE
sp_filestream_force_garbage_collection

ALTER DATABASE SET Options

This topic contains the ALTER DATABASE syntax that is related to setting database options. For
other ALTER DATABASE syntax, see ALTER DATABASE (Transact-SQL). Database mirroring,
AlwaysOn Availability Groups, and compatibility levels are SET options but are described in
separate topics because of their length. For more information, see ALTER DATABASE Database
Mirroring (Transact-SQL), ALTER DATABASE SET HADR (Transact-SQL), and ALTER DATABASE
Compatibility Level (Transact-SQL).
Transact-SQL Syntax Conventions
Syntax

ALTER DATABASE { database_name | CURRENT }

SET
{
<optionspec> [ ,... n ] [ WITH <termination> ]
}

<optionspec> ::=
{
<auto_option>
| <change_tracking_option>
| <containment_option>

64
 | <cursor_option>
| <database_mirroring_option>
| <date_correlation_optimization_option>
| <db_encryption_option>
| <db_state_option>
| <db_update_option>
| <db_user_access_option>
| <external_access_option>
| FILESTREAM ( <FILESTREAM_option> )
| <HADR_options>
| <parameterization_option>
| <recovery_option>
| <target_recovery_time_option>
| <service_broker_option>
| <snapshot_option>
| <sql_option>
}

<auto_option> ::=
{
AUTO_CLOSE { ON | OFF }
| AUTO_CREATE_STATISTICS { ON | OFF }
| AUTO_SHRINK { ON | OFF }
| AUTO_UPDATE_STATISTICS { ON | OFF }
| AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
}

<change_tracking_option> ::=
{
CHANGE_TRACKING
{
= OFF
| = ON [ ( <change_tracking_option_list > [,... n] ) ]
| ( <change_tracking_option_list> [,... n ] )
}

65
}

<change_tracking_option_list> ::=
{
AUTO_CLEANUP = { ON | OFF }
| CHANGE_RETENTION = retention_period { DAYS | HOURS | MINUTES }
}

<containment_option> ::=
CONTAINMENT = { NONE | PARTIAL }

<cursor_option> ::=
{
CURSOR_CLOSE_ON_COMMIT { ON | OFF }
| CURSOR_DEFAULT { LOCAL | GLOBAL }
}

<database_mirroring_option>
ALTER DATABASE Database Mirroring

<date_correlation_optimization_option> ::=
DATE_CORRELATION_OPTIMIZATION { ON | OFF }

<db_encryption_option> ::=
ENCRYPTION { ON | OFF }

<db_state_option> ::=
{ ONLINE | OFFLINE | EMERGENCY }

<db_update_option> ::=
{ READ_ONLY | READ_WRITE }

<db_user_access_option> ::=
{ SINGLE_USER | RESTRICTED_USER | MULTI_USER }

66
<external_access_option> ::=
{
DB_CHAINING { ON | OFF }
| TRUSTWORTHY { ON | OFF }
| DEFAULT_FULLTEXT_LANGUAGE = { <lcid> | <language name> | <language alias> }
| DEFAULT_LANGUAGE = { <lcid> | <language name> | <language alias> }
| NESTED_TRIGGERS = { OFF | ON }
| TRANSFORM_NOISE_WORDS = { OFF | ON }
| TWO_DIGIT_YEAR_CUTOFF = { 1753, ..., 2049, ..., 9999 }
}
<FILESTREAM_option> ::=
{
NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }
| DIRECTORY_NAME = <directory_name>
}
<HADR_options> ::=
ALTER DATABASE SET HADR

<parameterization_option> ::=
PARAMETERIZATION { SIMPLE | FORCED }

<recovery_option> ::=
{
RECOVERY { FULL | BULK_LOGGED | SIMPLE }
| TORN_PAGE_DETECTION { ON | OFF }
| PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
}

<target_recovery_time_option> ::=
TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }

<service_broker_option> ::=
{
ENABLE_BROKER
| DISABLE_BROKER

67
 | NEW_BROKER
| ERROR_BROKER_CONVERSATIONS
| HONOR_BROKER_PRIORITY { ON | OFF}
}

<snapshot_option> ::=
{
ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
| READ_COMMITTED_SNAPSHOT {ON | OFF }
}
<sql_option> ::=
{
ANSI_NULL_DEFAULT { ON | OFF }
| ANSI_NULLS { ON | OFF }
| ANSI_PADDING { ON | OFF }
| ANSI_WARNINGS { ON | OFF }
| ARITHABORT { ON | OFF }
| COMPATIBILITY_LEVEL = { 90 | 100 | 110 }
| CONCAT_NULL_YIELDS_NULL { ON | OFF }
| NUMERIC_ROUNDABORT { ON | OFF }
| QUOTED_IDENTIFIER { ON | OFF }
| RECURSIVE_TRIGGERS { ON | OFF }
}

<termination> ::=
{
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT
}
Arguments
database_name | CURRENT
Is the name of the database to be modified. CURRENT performs the action in the current
database. CURRENT is not supported for all options in all contexts. If CURRENT fails, provide
the database name.

<auto_option> ::=
68
Controls automatic options.

69
AUTO_CLOSE { ON | OFF }
ON
The database is shut down cleanly and its resources are freed after the last user exits.
The database automatically reopens when a user tries to use the database again. For
example, by issuing a USE database_name statement. If the database is shut down cleanly
while AUTO_CLOSE is set to ON, the database is not reopened until a user tries to use the
database the next time the Database Engine is restarted.

OFF
The database remains open after the last user exits.

The AUTO_CLOSE option is useful for desktop databases because it allows for database files
to be managed as regular files. They can be moved, copied to make backups, or even e-
mailed to other users. The AUTO_CLOSE process is asynchronous; repeatedly opening and
closing the database does not reduce performance.

Note
The AUTO_CLOSE option is not available in a Contained Database.
The status of this option can be determined by examining the is_auto_close_on column in the
sys.databases catalog view or the IsAutoClose property of the DATABASEPROPERTYEX
function.

Note
When AUTO_CLOSE is ON, some columns in the sys.databases catalog view and
DATABASEPROPERTYEX function will return NULL because the database is unavailable to retrieve the
data. To resolve this, execute a USE statement to open the database.

Note
Database mirroring requires AUTO_CLOSE OFF.
When the database is set to AUTOCLOSE = ON, an operation that initiates an automatic
database shutdown clears the plan cache for the instance of SQL Server. Clearing the plan
cache causes a recompilation of all subsequent execution plans and can cause a sudden,
temporary decrease in query performance. In SQL Server 2005 Service Pack 2 and higher, 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.

AUTO_CREATE_STATISTICS { ON | OFF }
ON
The query optimizer creates statistics on single columns in query predicates, as necessary,
to improve query plans and query performance. These single-column statistics are created
when the query optimizer compiles queries. The single-column statistics are created only

70
 on columns that are not already the first column of an existing statistics object.
The default is ON. We recommend that you use the default setting for most databases.

OFF
The query optimizer does not create statistics on single columns in query predicates when
it is compiling queries. Setting this option to OFF can cause suboptimal query plans and
degraded query performance.

The status of this option can be determined by examining the is_auto_create_stats_on column
in the sys.databases catalog view or the IsAutoCreateStatistics property of the
DATABASEPROPERTYEX function.
For more information, see the section "Using the Database-Wide Statistics Options" in Using
Statistics to Improve Query Performance.
AUTO_SHRINK { ON | OFF }
ON
The database files are candidates for periodic shrinking.
Both data file and log files can be automatically shrunk. AUTO_SHRINK reduces the size of
the transaction log only if the database is set to SIMPLE recovery model or if the log is
backed up. When set to OFF, the database files are not automatically shrunk during
periodic checks for unused space.
The AUTO_SHRINK option causes files to be shrunk when more than 25 percent of the file
contains unused space. The file is shrunk to a size where 25 percent of the file is unused
space, or to the size of the file when it was created, whichever is larger.
You cannot shrink a read-only database.

OFF
The database files are not automatically shrunk during periodic checks for unused space.

The status of this option can be determined by examining the is_auto_shrink_on column in
the sys.databases catalog view or the IsAutoShrink property of the DATABASEPROPERTYEX
function.

Note
The AUTO_SHRINK option is not available in a Contained Database.

AUTO_UPDATE_STATISTICS { ON | OFF }
ON
Specifies that the query optimizer updates statistics when they are used by a query and
when they might be out-of-date. Statistics become out-of-date after insert, update, delete,
or merge operations change the data distribution in the table or indexed view. The query
optimizer determines when statistics might be out-of-date by counting the number of data
modifications since the last statistics update and comparing the number of modifications
to a threshold. The threshold is based on the number of rows in the table or indexed view.

71
 The query optimizer checks for out-of-date statistics before compiling a query and before
executing a cached query plan. Before compiling a query, the query optimizer uses the
columns, tables, and indexed views in the query predicate to determine which statistics
might be out-of-date. Before executing a cached query plan, the Database Engine verifies
that the query plan references up-to-date statistics.
The AUTO_UPDATE_STATISTICS option applies to statistics created for indexes, single-
columns in query predicates, and statistics that are created by using the CREATE
STATISTICS statement. This option also applies to filtered statistics.
The default is ON. We recommend that you use the default setting for most databases.
Use the AUTO_UPDATE_STATISTICS_ASYNC option to specify whether the statistics are
updated synchronously or asynchronously.

OFF
Specifies that the query optimizer does not update statistics when they are used by a query
and when they might be out-of-date. Setting this option to OFF can cause suboptimal
query plans and degraded query performance.

The status of this option can be determined by examining the is_auto_update_stats_on

column in the sys.databases catalog view or the IsAutoUpdateStatistics property of the
DATABASEPROPERTYEX function.
For more information, see the section "Using the Database-Wide Statistics Options" in Using
Statistics to Improve Query Performance.
AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
ON
Specifies that statistics updates for the AUTO_UPDATE_STATISTICS option are
asynchronous. The query optimizer does not wait for statistics updates to complete before
it compiles queries.
Setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is set to ON.
By default, the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF, and the query
optimizer updates statistics synchronously.

OFF
Specifies that statistics updates for the AUTO_UPDATE_STATISTICS option are synchronous.
The query optimizer waits for statistcs updates to complete before it compiles queries.
Setting this option to OFF has no effect unless AUTO_UPDATE_STATISTICS is set to ON.

The status of this option can be determined by examining the is_auto_update_stats_async_on

column in the sys.databases catalog view.
For more information that describes when to use synchronous or asynchronous statistics
updates, see the section "Using the Database-Wide Statistics Options" in Using Statistics
to Improve Query Performance.
<change_tracking_option> ::=
72
Controls change tracking options. You can enable change tracking, set options, change options,
and disable change tracking. For examples, see the Examples section later in this topic.
ON
Enables change tracking for the database. When you enable change tracking, you can also
set the AUTO CLEANUP and CHANGE RETENTION options.

AUTO_CLEANUP = { ON | OFF }
ON
Change tracking information is automatically removed after the specified retention period.

OFF
Change tracking data is not removed from the database.

CHANGE_RETENTION = retention_period { DAYS | HOURS | MINUTES }

Specifies the minimum period for keeping change tracking information in the database. Data
is removed only when the AUTO_CLEANUP value is ON.
retention_period is an integer that specifies the numerical component of the retention
period.
The default retention period is 2 days. The minimum retention period is 1 minute.

OFF
Disables change tracking for the database. You must disable change tracking on all tables
before you can disable change tracking off the database.

<containment_option> ::=
Controls database containment options.
CONTAINMENT = { NONE | PARTIAL}
NONE
The database is not a contained database.

PARTIAL
The database is a contained database. Setting database containment to partial will fail if
the database has replication, change data capture, or change tracking enabled. Error
checking stops after one failure. For more information about contained databases, see
Understanding Contained Databases.
<cursor_option> ::=
Controls cursor options.

73
CURSOR_CLOSE_ON_COMMIT { ON | OFF }
ON
Any cursors open when a transaction is committed or rolled back are closed.

OFF
Cursors remain open when a transaction is committed; rolling back a transaction closes any
cursors except those defined as INSENSITIVE or STATIC.

Connection-level settings that are set by using the SET statement override the default
database setting for CURSOR_CLOSE_ON_COMMIT. By default, ODBC and OLE DB clients
issue a connection-level SET statement setting CURSOR_CLOSE_ON_COMMIT to OFF for the
session when connecting to an instance of SQL Server. For more information, see SET
CURSOR_CLOSE_ON_COMMIT (Transact-SQL).
The status of this option can be determined by examining the is_cursor_close_on_commit_on
column in the sys.databases catalog view or the IsCloseCursorsOnCommitEnabled property
of the DATABASEPROPERTYEX function.

CURSOR_DEFAULT { LOCAL | GLOBAL }

Controls whether cursor scope uses LOCAL or GLOBAL.
LOCAL
When LOCAL is specified and a cursor is not defined as GLOBAL when created, the scope
of the cursor is local to the batch, stored procedure, or trigger in which the cursor was
created. The cursor name is valid only within this scope. The cursor can be referenced by
local cursor variables in the batch, stored procedure, or trigger, or a stored procedure
OUTPUT parameter. The cursor is implicitly deallocated when the batch, stored procedure,
or trigger ends, unless it was passed back in an OUTPUT parameter. If the cursor is passed
back in an OUTPUT parameter, the cursor is deallocated when the last variable that
references it is deallocated or goes out of scope.

GLOBAL
When GLOBAL is specified, and a cursor is not defined as LOCAL when created, the scope
of the cursor is global to the connection. The cursor name can be referenced in any stored
procedure or batch executed by the connection.

The cursor is implicitly deallocated only at disconnect. For more information, see DECLARE
CURSOR.
The status of this option can be determined by examining the is_local_cursor_default column
in the sys.databases catalog view or the IsLocalCursorsDefault property of the
DATABASEPROPERTYEX function.

<database_mirroring>
For the argument descriptions, see ALTER DATABASE Database Mirroring (Transact-SQL).
<date_correlation_optimization_option> ::=
Controls the date_correlation_optimization option.

74
DATE_CORRELATION_OPTIMIZATION { ON | OFF }
ON
SQL Server maintains correlation statistics between any two tables in the database that are
linked by a FOREIGN KEY constraint and have datetime columns.

OFF
Correlation statistics are not maintained.

To set DATE_CORRELATION_OPTIMIZATION to ON, there must be no active connections to

the database except for the connection that is executing the ALTER DATABASE statement.
Afterwards, multiple connections are supported.
The current setting of this option can be determined by examining the is_date_correlation_on
column in the sys.databases catalog view.

<db_encryption_option> ::=
Controls the database encryption state.
ENCRYPTION {ON | OFF}
Sets the database to be encrypted (ON) or not encrypted (OFF). For more information about
database encryption, see Understanding Transparent Data Encryption (TDE).
When encryption is enabled at the database level all filegroups will be encrypted. Any new
filegroups will inherit the encrypted property. If any filegroups in the database are set to READ
ONLY, the database encryption operation will fail.
You can see the encryption state of the database by using the sys.dm_database_encryption_keys
dynamic management view.
<db_state_option> ::=
Controls the state of the database.
OFFLINE
The database is closed, shut down cleanly, and marked offline. The database cannot be
modified while it is offline.

ONLINE
The database is open and available for use.

EMERGENCY
The database is marked READ_ONLY, logging is disabled, and access is limited to members of
the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes.
For example, a database marked as suspect due to a corrupted log file can be set to the
EMERGENCY state. This could enable the system administrator read-only access to the
database. Only members of the sysadmin fixed server role can set a database to the
EMERGENCY state.

Note

75
 Permissions: ALTER DATABASE permission for the subject database is required to
change a database to the offline or emergency state. The server level ALTER ANY
DATABASE permission is required to move a database from offline to online.
The status of this option can be determined by examining the state and state_desc columns in
the sys.databases catalog view or the Status property of the DATABASEPROPERTYEX function.
For more information, see Database States.
A database marked as RESTORING cannot be set to OFFLINE, ONLINE, or EMERGENCY. A
database may 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.
<db_update_option> ::=
Controls whether updates are allowed on the database.
READ_ONLY
Users can read data from the database but not modify it.

Note
To improve query performance, update statistics before setting a database to READ_ONLY. If
additional statistics are needed after a database is set to READ_ONLY, the Database Engine will create
statistics in tempdb. For more information about statistics for a read-only database, see Statistics.

READ_WRITE
The database is available for read and write operations.

To change this state, you must have exclusive access to the database. For more information, see
the SINGLE_USER clause.
<db_user_access_option> ::=
Controls user access to the database.
SINGLE_USER
Specifies that only one user at a time can access the database. If SINGLE_USER is specified
and there are other users connected to the database the ALTER DATABASE statement will be
blocked until all users disconnect from the specified database. To override this behavior, see
the WITH <termination> clause.
The database remains in SINGLE_USER mode even if the user that set the option logs off. At
that point, a different user, but only one, can connect to the database.
Before you set the database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC
option is set to OFF. When set to ON, the background thread used to update statistics takes a
connection against the database, and you will be unable to access the database in single-user
mode. To view the status of this option, query the is_auto_update_stats_async_on column in
the sys.databases catalog view. If the option is set to ON, perform the following tasks:
1. Set AUTO_UPDATE_STATISTICS_ASYNC to OFF.
2. Check for active asynchronous statistics jobs by querying the
sys.dm_exec_background_job_queue dynamic management view.

76
 If there are active jobs, either allow the jobs to complete or manually terminate them by
using KILL STATS JOB.
RESTRICTED_USER
RESTRICTED_USER allows for only members of the db_owner fixed database role and
dbcreator and sysadmin fixed server roles to connect to the database, but does not limit their
number. All connections to the database are disconnected in the timeframe specified by the
termination clause of the ALTER DATABASE statement. After the database has transitioned to
the RESTRICTED_USER state, connection attempts by unqualified users are refused.

MULTI_USER
All users that have the appropriate permissions to connect to the database are allowed.

The status of this option can be determined by examining the user_access column in the
sys.databases catalog view or the UserAccess property of the DATABASEPROPERTYEX function.
<external_access_option> ::=
Controls whether the database can be accessed by external resources, such as objects from
another database.

77
DB_CHAINING { ON | OFF }
ON
Database can be the source or target of a cross-database ownership chain.

OFF
Database cannot participate in cross-database ownership chaining.

Important
The instance of SQL Server will recognize this setting when the cross db ownership chaining server
option is 0 (OFF). When cross db ownership chaining is 1 (ON), all user databases can participate in
cross-database ownership chains, regardless of the value of this option. This option is set by using
sp_configure.
To set this option, requires CONTROL SERVER permission on the database.
The DB_CHAINING option cannot be set on these system databases: master, model, and
tempdb.
The status of this option can be determined by examining the is_db_chaining_on column in
the sys.databases catalog view.

TRUSTWORTHY { ON | OFF }
ON
Database modules (for example, user-defined functions or stored procedures) that use an
impersonation context can access resources outside the database.

OFF
Database modules in an impersonation context cannot access resources outside the
database.

TRUSTWORTHY is set to OFF whenever the database is attached.

By default, all system databases except the msdb database have TRUSTWORTHY set to OFF.
The value cannot be changed for the model and tempdb databases. We recommend that you
never set the TRUSTWORTHY option to ON for the master database.
To set this option, requires CONTROL SERVER permission on the database.
The status of this option can be determined by examining the is_trustworthy_on column in
the sys.databases catalog view.

DEFAULT_FULLTEXT_LANGUAGE
Specifies the default language value for full-text indexed columns.

Important
This option is allowable only when CONTAINMENT has been set to PARTIAL. If CONTAINMENT is set
to NONE, errors will occur.

DEFAULT_LANGUAGE
Specifies the default language for all newly created logins. Language can be specified by

78
 providing the local id (lcid), the language name, or the language alias. For a list of acceptable
language names and aliases, see sys.syslanguages (Transact-SQL).

Important
This option is allowable only when CONTAINMENT has been set to PARTIAL. If CONTAINMENT is set
to NONE, errors will occur.

NESTED_TRIGGERS
Specifies whether an AFTER trigger can cascade; that is, perform an action that initiates
another trigger, which initiates another trigger, and so on.

Important
This option is allowable only when CONTAINMENT has been set to PARTIAL. If CONTAINMENT is set
to NONE, errors will occur.

TRANSFORM_NOISE_WORDS
Used to suppress an error message if noise words, or stopwords, cause a Boolean operation
on a full-text query to fail.

Important
This option is allowable only when CONTAINMENT has been set to PARTIAL. If CONTAINMENT is set
to NONE, errors will occur.

TWO_DIGIT_YEAR_CUTOFF
Specifies an integer from 1753 to 9999 that represents the cutoff year for interpreting two-
digit years as four-digit years.

Important
This option is allowable only when CONTAINMENT has been set to PARTIAL. If CONTAINMENT is set
to NONE, errors will occur.

<FILESTREAM_option> ::=
Controls the settings for FileTables.
NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }
OFF
Non-transactional access to FileTable data is disabled.

READ_ONLY
FILESTREAM data in FileTables in this database can be read by non-transactional processes.

FULL
Full non-transactional access to FILESTREAM data in FileTables is enabled.

DIRECTORY_NAME = <directory_name>
A windows-compatible directory name. This name should be unique among all the database-
level directory names in the SQL Server instance. Uniqueness comparison is case-insensitive,

79
 regardless of collation settings. This option must be set before creating a FileTable in this
database.

<parameterization_option> ::=
Controls the parameterization option.
PARAMETERIZATION { SIMPLE | FORCED }
SIMPLE
Queries are parameterized based on the default behavior of the database.

FORCED
SQL Server parameterizes all queries in the database.

The current setting of this option can be determined by examining the

is_parameterization_forced column in the sys.databases catalog view.

<recovery_option> ::=
Controls database recovery options and disk I/O error checking.
FULL
Provides full recovery after media failure by using transaction log backups. If a data file is
damaged, media recovery can restore all committed transactions. For more information, see
Recovery Models (SQL Server).
BULK_LOGGED
Provides recovery after media failure by combining the best performance and least amount
of log-space use for certain large-scale or bulk operations. For information about what
operations can be minimally logged, see Transaction Logs (SQL Server). Under the
BULK_LOGGED recovery model, logging for these operations is minimal. For more
information, see Recovery Models (SQL Server).
SIMPLE
A simple backup strategy that uses minimal log space is provided. Log space can be
automatically reused when it is no longer required for server failure recovery. For more
information, see Recovery Models (SQL Server).

Important
The simple recovery model is easier to manage than the other two models but at the expense of
greater data loss exposure if a data file is damaged. All changes since the most recent database or
differential database backup are lost and must be manually reentered.

The default recovery model is determined by the recovery model of the model database. For
more information about selecting the appropriate recovery model, see Database Recovery
Models (SQL Server).
The status of this option can be determined by examining the recovery_model and
recovery_model_desc columns in the sys.databases catalog view or the Recovery property of
the DATABASEPROPERTYEX function.
80
TORN_PAGE_DETECTION { ON | OFF }
ON
Incomplete pages can be detected by the Database Engine.

OFF
Incomplete pages cannot be detected by the Database Engine.

Important
The syntax structure TORN_PAGE_DETECTION ON | OFF will be removed in a future version of SQL
Server. Avoid using this syntax structure in new development work, and plan to modify applications
that currently use the syntax structure. Use the PAGE_VERIFY option instead.

PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }

Discovers damaged database pages caused by disk I/O path errors. Disk I/O path errors can
be the cause of database corruption problems and are generally caused by power failures or
disk hardware failures that occur at the time the page is being written to disk.
CHECKSUM
Calculates a checksum over the contents of the whole page and stores the value in the
page header when a page is written to disk. When the page is read from disk, the
checksum is recomputed and compared to the checksum value stored in the page header.
If the values do not match, error message 824 (indicating a checksum failure) is reported to
both the SQL Server error log and the Windows event log. A checksum failure indicates an
I/O path problem. To determine the root cause requires investigation of the hardware,
firmware drivers, BIOS, filter drivers (such as virus software), and other I/O path
components.

TORN_PAGE_DETECTION
Saves a specific 2-bit pattern for each 512-byte sector in the 8-kilobyte (KB) database page
and stored in the database page header when the page is written to disk. When the page
is read from disk, the torn bits stored in the page header are compared to the actual page
sector information. Unmatched values indicate that only part of the page was written to
disk. In this situation, error message 824 (indicating a torn page error) is reported to both
the SQL Server error log and the Windows event log. Torn pages are typically detected by
database recovery if it is truly an incomplete write of a page. However, other I/O path
failures can cause a torn page at any time.

NONE
Database page writes will not generate a CHECKSUM or TORN_PAGE_DETECTION value.
SQL Server will not verify a checksum or torn page during a read even if a CHECKSUM or
TORN_PAGE_DETECTION value is present in the page header.

Consider the following important points when you use the PAGE_VERIFY option:
• The default is CHECKSUM.
• When a user or system database is upgraded to SQL Server 2005 or a later version, the

81
 PAGE_VERIFY value (NONE or TORN_PAGE_DETECTION) is retained. We recommend that
you use CHECKSUM.

Note
In earlier versions of SQL Server, the PAGE_VERIFY database option is set to NONE for the tempdb
database and cannot be modified. In SQL Server 2008 and later versions, the default value for the
tempdb database is CHECKSUM for new installations of SQL Server. When upgrading an
installation SQL Server, the default value remains NONE. The option can be modified. We
recommend that you use CHECKSUM for the tempdb database.
• TORN_PAGE_DETECTION may use fewer resources but provides a minimal subset of the
CHECKSUM protection.
• PAGE_VERIFY can be set without taking the database offline, locking the database, or
otherwise impeding concurrency on that database.
• CHECKSUM is mutually exclusive to TORN_PAGE_DETECTION. Both options cannot be
enabled at the same time.
When a torn page or checksum failure is detected, you can recover by restoring the data or
potentially rebuilding the index if the failure is limited only to index pages. If you encounter a
checksum failure, to determine the type of database page or pages affected, run DBCC
CHECKDB. For more information about restore options, see RESTORE Arguments
(Transact-SQL). Although restoring the data will resolve the data corruption problem, the
root cause, for example, disk hardware failure, should be diagnosed and corrected as soon as
possible to prevent continuing errors.
SQL Server will retry any read that fails with a checksum, torn page, or other I/O error four
times. If the read is successful in any one of the retry attempts, a message will be written to
the error log and the command that triggered the read will continue. If the retry attempts fail,
the command will fail with error message 824.
For more information about checksum, torn page, read-retry, error messages 823 and 824,
and other SQL Server I/O auditing features, see this Microsoft Web site.
The current setting of this option can be determined by examining the page_verify_option
column in the sys.databases catalog view or the IsTornPageDetectionEnabled property of
the DATABASEPROPERTYEX function.

<target_recovery_time_option> ::=
Specifies the frequency of indirect checkpoints on a per-database basis. The default is 0, which
indicates that the database will use automatic checkpoints, whose frequency depends on the
recovery interval setting of the server instance.

82
TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }
target_recovery_time
Specifies the maximum bound on the time to recover the specified database in the event
of a crash.

SECONDS
Indicates that target_recovery_time is expressed as the number of seconds.

MINUTES
Indicates that target_recovery_time is expressed as the number of minutes.

For more information about indirect checkpoints, see Database Checkpoints (SQL Server).
<service_broker_option> ::=
Controls the following Service Broker options: enables or disables message delivery, sets a new
Service Broker identifier, or sets conversation priorities to ON or OFF.
ENABLE_BROKER
Specifies that Service Broker is enabled for the specified database. Message delivery is
started, and the is_broker_enabled flag is set to true in the sys.databases catalog view. The
database retains the existing Service Broker identifier.

Note
ENABLE_BROKER requires an exclusive database lock. If other sessions have locked resources in the
database, ENABLE_BROKER will wait until the other sessions release their locks. To enable Service
Broker in a user database, ensure that no other sessions are using the database before you run the
ALTER DATABASE SET ENABLE_BROKER statement, such as by putting the database in single user
mode. To enable Service Broker in the msdb database, first stop SQL Server Agent so that Service
Broker can obtain the necessary lock.

DISABLE_BROKER
Specifies that Service Broker is disabled for the specified database. Message delivery is
stopped, and the is_broker_enabled flag is set to false in the sys.databases catalog view. The
database retains the existing Service Broker identifier.

NEW_BROKER
Specifies that the database should receive a new broker identifier. Because the database is
considered to be a new service broker, all existing conversations in the database are
immediately removed without producing end dialog messages. Any route that references the
old Service Broker identifier must be re-created with the new identifier.

ERROR_BROKER_CONVERSATIONS
Specifies that Service Broker message delivery is enabled. This preserves the existing Service
Broker identifier for the database. Service Broker ends all conversations in the database with
an error. This enables applications to perform regular cleanup for existing conversations.

83
HONOR_BROKER_PRIORITY {ON | OFF}
ON
Send operations take into consideration the priority levels that are assigned to
conversations. Messages from conversations that have high priority levels are sent before
messages from conversations that are assigned low priority levels.

OFF
Send operations run as if all conversations have the default priority level.

Changes to the HONOR_BROKER_PRIORITY option take effect immediately for new dialogs or
dialogs that have no messages waiting to be sent. Dialogs that have messages waiting to be
sent when ALTER DATABASE is run will not pick up the new setting until some of the
messages for the dialog have been sent. The amount of time before all dialogs start using the
new setting can vary considerably.
The current setting of this property is reported in the is_broker_priority_honored column in
the sys.databases catalog view.

<snapshot_option> ::=
Determines the transaction isolation level.

84
ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
ON
Enables Snapshot option at the database level. When it is enabled, DML statements start
generating row versions even when no transaction uses Snapshot Isolation. Once this
option is enabled, transactions can specify the SNAPSHOT transaction isolation level. When
a transaction runs at the SNAPSHOT isolation level, all statements see a snapshot of data
as it exists at the start of the transaction. If a transaction running at the SNAPSHOT
isolation level accesses data in multiple databases, either ALLOW_SNAPSHOT_ISOLATION
must be set to ON in all the databases, or each statement in the transaction must use
locking hints on any reference in a FROM clause to a table in a database where
ALLOW_SNAPSHOT_ISOLATION is OFF.

OFF
Turns off the Snapshot option at the database level. Transactions cannot specify the
SNAPSHOT transaction isolation level.

When you set ALLOW_SNAPSHOT_ISOLATION to a new state (from ON to OFF, or from OFF
to ON), ALTER DATABASE does not return control to the caller until all existing transactions in
the database are committed. If the database is already in the state specified in the ALTER
DATABASE statement, control is returned to the caller immediately. If the ALTER DATABASE
statement does not return quickly, use
sys.dm_tran_active_snapshot_database_transactions to determine whether there
are long-running transactions. If the ALTER DATABASE statement is canceled, the database
remains in the state it was in when ALTER DATABASE was started. The sys.databases
catalog view indicates the state of snapshot-isolation transactions in the database. If
snapshot_isolation_state_desc = IN_TRANSITION_TO_ON, ALTER DATABASE
ALLOW_SNAPSHOT_ISOLATION OFF will pause six seconds and retry the operation.
You cannot change the state of ALLOW_SNAPSHOT_ISOLATION if the database is OFFLINE.
If you set ALLOW_SNAPSHOT_ISOLATION in a READ_ONLY database, the setting will be
retained if the database is later set to READ_WRITE.
You can change the ALLOW_SNAPSHOT_ISOLATION settings for the master, model, msdb,
and tempdb databases. If you change the setting for tempdb, the setting is retained every
time the instance of the Database Engine is stopped and restarted. If you change the setting
for model, that setting becomes the default for any new databases that are created, except
for tempdb.
The option is ON, by default, for the master and msdb databases.
The current setting of this option can be determined by examining the
snapshot_isolation_state column in the sys.databases catalog view.

READ_COMMITTED_SNAPSHOT { ON | OFF }
ON
Enables Read-Committed Snapshot option at the database level. When it is enabled, DML
statements start generating row versions even when no transaction uses Snapshot

85
 Isolation. Once this option is enabled, the transactions specifying the read committed
isolation level use row versioning instead of locking. When a transaction runs at the read
committed isolation level, all statements see a snapshot of data as it exists at the start of
the statement.

OFF
Turns off Read-Committed Snapshot option at the database level. Transactions specifying
the READ COMMITTED isolation level use locking.

To set READ_COMMITTED_SNAPSHOT ON or OFF, there must be no active connections to

the database except for the connection executing the ALTER DATABASE command. However,
the database does not have to be in single-user mode. You cannot change the state of this
option when the database is OFFLINE.
If you set READ_COMMITTED_SNAPSHOT in a READ_ONLY database, the setting will be
retained when the database is later set to READ_WRITE.
READ_COMMITTED_SNAPSHOT cannot be turned ON for the master, tempdb, or msdb
system databases. If you change the setting for model, that setting becomes the default for
any new databases created, except for tempdb.
The current setting of this option can be determined by examining the
is_read_committed_snapshot_on column in the sys.databases catalog view.

<sql_option> ::=
Controls the ANSI compliance options at the database level.
ANSI_NULL_DEFAULT { ON | OFF }
Determines the default value, NULL or NOT NULL, of a column or CLR user-defined type
for which the nullability is not explicitly defined in CREATE TABLE or ALTER TABLE statements.
Columns that are defined with constraints follow constraint rules regardless of this setting.
ON
The default value is NULL.

OFF
The default value is NOT NULL.

Connection-level settings that are set by using the SET statement override the default
database-level setting for ANSI_NULL_DEFAULT. By default, ODBC and OLE DB clients issue a
connection-level SET statement setting ANSI_NULL_DEFAULT to ON for the session when
connecting to an instance of SQL Server. For more information, see SET
ANSI_NULL_DFLT_ON.
For ANSI compatibility, setting the database option ANSI_NULL_DEFAULT to ON changes the
database default to NULL.
The status of this option can be determined by examining the is_ansi_null_default_on column
in the sys.databases catalog view or the IsAnsiNullDefault property of the
DATABASEPROPERTYEX function.

86
ANSI_NULLS { ON | OFF }
ON
All comparisons to a null value evaluate to UNKNOWN.

OFF
Comparisons of non-UNICODE values to a null value evaluate to TRUE if both values are
NULL.

Important
In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly
set the option to OFF will produce an error. Avoid using this feature in new development work, and
plan to modify applications that currently use this feature.
Connection-level settings that are set by using the SET statement override the default
database setting for ANSI_NULLS. By default, ODBC and OLE DB clients issue a connection-
level SET statement setting ANSI_NULLS to ON for the session when connecting to an
instance of SQL Server. For more information, see SET ANSI_NULLS.
SET ANSI_NULLS also must be set to ON when you create or make changes to indexes on
computed columns or indexed views.
The status of this option can be determined by examining the is_ansi_nulls_on column in the
sys.databases catalog view or the IsAnsiNullsEnabled property of the DATABASEPROPERTYEX
function.

ANSI_PADDING { ON | OFF }
ON
Strings are padded to the same length before conversion or inserting to a varchar or
nvarchar data type.
Trailing blanks in character values inserted into varchar or nvarchar columns and trailing
zeros in binary values inserted into varbinary columns are not trimmed. Values are not
padded to the length of the column.

OFF
Trailing blanks for varchar or nvarchar and zeros for varbinary are trimmed.

When OFF is specified, this setting affects only the definition of new columns.

Important
In a future version of SQL Server, ANSI_PADDING will always be ON and any applications that explicitly
set the option to OFF will produce an error. Avoid using this feature in new development work, and
plan to modify applications that currently use this feature. We recommend that you always set
ANSI_PADDING to ON. ANSI_PADDING must be ON when you create or manipulate indexes on
computed columns or indexed views.
char(n) and binary(n) columns that allow for nulls are padded to the length of the column
when ANSI_PADDING is set to ON, but trailing blanks and zeros are trimmed when

87
 ANSI_PADDING is OFF. char(n) and binary(n) columns that do not allow nulls are always
padded to the length of the column.
Connection-level settings that are set by using the SET statement override the default
database-level setting for ANSI_PADDING. By default, ODBC and OLE DB clients issue a
connection-level SET statement setting ANSI_PADDING to ON for the session when
connecting to an instance of SQL Server. For more information, see SET ANSI_PADDING.

Important

The status of this option can be determined by examining the is_ansi_padding_on column in
the sys.databases catalog view or the IsAnsiPaddingEnabled property of the
DATABASEPROPERTYEX function.

ANSI_WARNINGS { ON | OFF }
ON
Errors or warnings are issued when conditions such as divide-by-zero occur or null values
appear in aggregate functions.

OFF
No warnings are raised and null values are returned when conditions such as divide-by-
zero occur.

SET ANSI_WARNINGS must be set to ON when you create or make changes to indexes on
computed columns or indexed views.
Connection-level settings that are set by using the SET statement override the default
database setting for ANSI_WARNINGS. By default, ODBC and OLE DB clients issue a
connection-level SET statement setting ANSI_WARNINGS to ON for the session when
connecting to an instance of SQL Server. For more information, see SET
ANSI_WARNINGS.
The status of this option can be determined by examining the is_ansi_warnings_on column in
the sys.databases catalog view or the IsAnsiWarningsEnabled property of the
DATABASEPROPERTYEX function.

ARITHABORT { ON | OFF }
ON
A query is ended when an overflow or divide-by-zero error occurs during query execution.

OFF
A warning message is displayed when one of these errors occurs, but the query, batch, or
transaction continues to process as if no error occurred.

SET ARITHABORT must be set to ON when you create or make changes to indexes on
computed columns or indexed views.
The status of this option can be determined by examining the is_arithabort_on column in the

88
 sys.databases catalog view or the IsArithmeticAbortEnabled property of the
DATABASEPROPERTYEX function.

COMPATIBILITY_LEVEL { 90 | 100 | 110 }

For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).

89
CONCAT_NULL_YIELDS_NULL { ON | OFF }
ON
The result of a concatenation operation is NULL when either operand is NULL. For example,
concatenating the character string "This is" and NULL causes the value NULL, instead of the
value "This is".

OFF
The null value is treated as an empty character string.

CONCAT_NULL_YIELDS_NULL must be set to ON when you create or make changes to

indexes on computed columns or indexed views.

Important
In a future version of SQL Server, CONCAT_NULL_YIELDS_NULL will always be ON and any applications
that explicitly set the option to OFF will produce an error. Avoid using this feature in new development
work, and plan to modify applications that currently use this feature.
Connection-level settings that are set by using the SET statement override the default
database setting for CONCAT_NULL_YIELDS_NULL. By default, ODBC and OLE DB clients issue
a connection-level SET statement setting CONCAT_NULL_YIELDS_NULL to ON for the session
when connecting to an instance of SQL Server. For more information, see SET
CONCAT_NULL_YIELDS_NULL.
The status of this option can be determined by examining the is_concat_null_yields_null_on
column in the sys.databases catalog view or the IsNullConcat property of the
DATABASEPROPERTYEX function.

QUOTED_IDENTIFIER { ON | OFF }
ON
Double quotation marks can be used to enclose delimited identifiers.
All strings delimited by double quotation marks are interpreted as object identifiers.
Quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be
keywords and can include characters not generally allowed in Transact-SQL identifiers. If a
single quotation mark (') is part of the literal string, it can be represented by double
quotation marks (").

OFF
Identifiers cannot be in quotation marks and must follow all Transact-SQL rules for
identifiers. Literals can be delimited by either single or double quotation marks.

SQL Server also allows for identifiers to be delimited by square brackets ([ ]). Bracketed
identifiers can always be used, regardless of the setting of QUOTED_IDENTIFIER. For more
information, see Database Identifiers.
When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the
metadata of the table, even if the option is set to OFF when the table is created.
Connection-level settings that are set by using the SET statement override the default

90
 database setting for QUOTED_IDENTIFIER. By default, ODBC and OLE DB clients issue a
connection-level SET statement setting QUOTED_IDENTIFIER to ON when connecting to an
instance of SQL Server. For more information, see SET QUOTED_IDENTIFIER.
The status of this option can be determined by examining the is_quoted_identifier_on column
in the sys.databases catalog view or the IsQuotedIdentifiersEnabled property of the
DATABASEPROPERTYEX function.

NUMERIC_ROUNDABORT { ON | OFF }
ON
An error is generated when loss of precision occurs in an expression.

OFF
Losses of precision do not generate error messages and the result is rounded to the
precision of the column or variable storing the result.

NUMERIC_ROUNDABORT must be set to OFF when you create or make changes to indexes
on computed columns or indexed views.
The status of this option can be determined by examining the is_numeric_roundabort_on
column in the sys.databases catalog view or the IsNumericRoundAbortEnabled property of
the DATABASEPROPERTYEX function.

RECURSIVE_TRIGGERS { ON | OFF }
ON
Recursive firing of AFTER triggers is allowed.

OFF
Only direct recursive firing of AFTER triggers is not allowed. To also disable indirect
recursion of AFTER triggers, set the nested triggers server option to 0 by using
sp_configure.

Note
Only direct recursion is prevented when RECURSIVE_TRIGGERS is set to OFF. To disable indirect
recursion, you must also set the nested triggers server option to 0.
The status of this option can be determined by examining the is_recursive_triggers_on
column in the sys.databases catalog view or the IsRecursiveTriggersEnabled property of the
DATABASEPROPERTYEX function.

WITH <termination> ::=

Specifies when to roll back incomplete transactions when the database is transitioned from one
state to another. If the termination clause is omitted, the ALTER DATABASE statement waits
indefinitely if there is any lock on the database. Only one termination clause can be specified,
and it follows the SET clauses.

Note

91
 Not all database options use the WITH <termination> clause. For more information, see
the table under "Setting Options of the "Remarks" section of this topic.
ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
Specifies whether to roll back after the specified number of seconds or immediately.

NO_WAIT
Specifies that if the requested database state or option change cannot complete immediately
without waiting for transactions to commit or roll back on their own, the request will fail.

Remarks
Setting Options
To retrieve current settings for database options, use the sys.databases catalog view or
DATABASEPROPERTYEX
After you set a database option, the modification takes effect immediately.
To change the default values for any one of the database options for all newly created
databases, change the appropriate database option in the model database.
Not all database options use the WITH <termination> clause or can be specified in combination
with other options. The following table lists these options and their option and termination
status.

Options category Can be specified with other Can use the WITH
options <termination> clause

<db_state_option> Yes Yes

<db_user_access_option> Yes Yes
<db_update_option> Yes Yes
<external_access_option> Yes No
<cursor_option> Yes No
<auto_option> Yes No
<sql_option> Yes No
<recovery_option> Yes No
<target_recovery_time_option> No Yes
<database_mirroring_option> No No
ALLOW_SNAPSHOT_ISOLATION No No
READ_COMMITTED_SNAPSHOT No Yes
<service_broker_option> Yes No

92
Options category Can be specified with other Can use the WITH
options <termination> clause

DATE_CORRELATION_OPTIMIZATION Yes Yes

<parameterization_option> Yes Yes
<change_tracking_option> Yes Yes
<db_encryption> Yes No

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

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

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.
Examples
A. Setting options on a database
The following example sets the recovery model and data page verification options for the
AdventureWorks2012 sample database.
USE master;
GO
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL, PAGE_VERIFY CHECKSUM;
GO

B. Setting the database to READ_ONLY

Changing the state of a database or filegroup to READ_ONLY or READ_WRITE requires exclusive
access to the database. The following example sets the database to SINGLE_USER mode to

93
obtain exclusive access. The example then sets the state of the AdventureWorks2012 database
to READ_ONLY and returns access to the database to all users.

Note
This example uses the termination option WITH ROLLBACK IMMEDIATE in the first ALTER
DATABASE statement. All incomplete transactions will be rolled back and any other
connections to the database will be immediately disconnected.
USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO

C. Enabling snapshot isolation on a database

The following example enables the snapshot isolation framework option for the
AdventureWorks2012 database.
USE AdventureWorks2012;
GO
-- Check the state of the snapshot_isolation_framework
-- in the database.
SELECT name, snapshot_isolation_state,
snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks2012';
GO
USE master;
GO
ALTER DATABASE AdventureWorks2012
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

94
-- Check again.
SELECT name, snapshot_isolation_state,
snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks2012';
GO

The result set shows that the snapshot isolation framework is enabled.
name snapshot_isolation_state description
-------------------- ------------------------ ----------
AdventureWorks2012 1 ON

D. Enabling, modifying, and disabling change tracking

The following example enables change tracking for the AdventureWorks2012 database and sets
the retention period to 4 days.
ALTER DATABASE AdventureWorks2012
SET CHANGE_TRACKING = ON
(AUTO_CLEANUP = ON, CHANGE_RETENTION = 2 DAYS);

The following example shows how to change the retention period to 3 days.
ALTER DATABASE AdventureWorks2012
SET CHANGE_TRACKING (CHANGE_RETENTION = 3 DAYS);

The following example shows how to disable change tracking for the AdventureWorks2012
database.
ALTER DATABASE AdventureWorks2012
SET CHANGE_TRACKING = OFF;

See Also
ALTER DATABASE Compatibility Level (Transact-SQL)
ALTER DATABASE Database Mirroring (Transact-SQL)
ALTER DATABASE SET HADR (Transact-SQL)
Using Statistics to Improve Query Performance
CREATE DATABASE
Configuring and Managing Change Tracking
DATABASEPROPERTYEX
95
DROP DATABASE
SET TRANSACTION ISOLATION LEVEL
sp_configure
sys.databases (Transact-SQL)
sys.data_spaces (Transact-SQL)

ALTER DATABASE Database Mirroring

Note
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this
feature in new development work, and plan to modify applications that currently use this
feature. Use AlwaysOn Availability Groups instead.
Controls database mirroring for a database. Values specified with the database mirroring
options apply to both copies of the database and to the database mirroring session as a whole.
Only one <database_mirroring_option> is permitted per ALTER DATABASE statement.

Note
We recommend that you configure database mirroring during off-peak hours because
configuration can affect performance.
For ALTER DATABASE options, see ALTER DATABASE (Transact-SQL). For ALTER DATABASE SET
options, see ALTER DATABASE SET Options (Transact-SQL).
Transact-SQL Syntax Conventions
Syntax

ALTER DATABASE database_name

SET { <partner_option> | <witness_option> }
<partner_option> ::=
PARTNER { = 'partner_server'
| FAILOVER
| FORCE_SERVICE_ALLOW_DATA_LOSS
| OFF
| RESUME
| SAFETY { FULL | OFF }
| SUSPEND
| TIMEOUT integer
}
<witness_option> ::=
WITNESS { = 'witness_server'
96
 | OFF
}

Arguments

Important
A SET PARTNER or SET WITNESS command can complete successfully when entered, but
fail later.

Note
ALTER DATABASE database mirroring options are not available for a contained database.
database_name
Is the name of the database to be modified.

PARTNER <partner_option>
Controls the database properties that define the failover partners of a database mirroring
session and their behavior. Some SET PARTNER options can be set on either partner; others
are restricted to the principal server or to the mirror server. For more information, see the
individual PARTNER options that follow. A SET PARTNER clause affects both copies of the
database, regardless of the partner on which it is specified.
To execute a SET PARTNER statement, the STATE of the endpoints of both partners must be
set to STARTED. Note, also, that the ROLE of the database mirroring endpoint of each partner
server instance must be set to either PARTNER or ALL. For information about how to specify
an endpoint, see How
to: Create a Mirroring Endpoint for Windows
Authentication (Transact-SQL). To learn the role and state of the database mirroring
endpoint of a server instance, on that instance, use the following Transact-SQL statement:
SELECT role_desc, state_desc FROM
sys.database_mirroring_endpoints
<partner_option> ::=

Note
Only one <partner_option> is permitted per SET PARTNER clause.
'partner_server'
Specifies the server network address of an instance of SQL Server to act as a failover
partner in a new database mirroring session. Each session requires two partners: one starts
as the principal server, and the other starts as the mirror server. We recommend that these
partners reside on different computers.
This option is specified one time per session on each partner. Initiating a database
mirroring session requires two ALTER DATABASE database SET PARTNER = 'partner_server'
statements. Their order is significant. First, connect to the mirror server, and specify the
principal server instance as partner_server (SET PARTNER = 'principal_server'). Second,
connect to the principal server, and specify the mirror server instance as partner_server
97
 (SET PARTNER = 'mirror_server'); this starts a database mirroring session between these
two partners. For more information, see Overview of Setting Up Database
Mirroring (Transact-SQL).
The value of partner_server is a server network address. This has the following syntax:
TCP://<system-address>:<port>
where
• <system-address> is a string, such as a system name, a fully qualified domain name, or
an IP address, that unambiguously identifies the destination computer system.
• <port> is a port number that is associated with the mirroring endpoint of the partner
server instance.
For more information, see Specifying a Server Network Address (Database
Mirroring).
The following example illustrates the SET PARTNER = 'partner_server' clause:
'TCP://MYSERVER.mydomain.Adventure-Works.com:7777'

Important
If a session is set up by using the ALTER DATABASE statement instead of SQL Server Management
Studio, the session is set to full transaction safety by default (SAFETY is set to FULL) and runs in
high-safety mode without automatic failover. To allow automatic failover, configure a witness; to run
in high-performance mode, turn off transaction safety (SAFETY OFF).

FAILOVER
Manually fails over the principal server to the mirror server. You can specify FAILOVER only
on the principal server. This option is valid only when the SAFETY setting is FULL (the
default).
The FAILOVER option requires master as the database context.

FORCE_SERVICE_ALLOW_DATA_LOSS
Forces database service to the mirror database after the principal server fails with the
database in an unsynchronized state or in a synchronized state when automatic failover
does not occur.
We strongly recommend that you force service only if the principal server is no longer
running. Otherwise, some clients might continue to access the original principal database
instead of the new principal database.
FORCE_SERVICE_ALLOW_DATA_LOSS is available only on the mirror server and only under
all the following conditions:
• The principal server is down.
• WITNESS is set to OFF or the witness is connected to the mirror server.
Force service only if you are willing to risk losing some data in order to restore service to
the database immediately.
Forcing service suspends the session, temporarily preserving all the data in the original
98
 principal database. Once the original principal is in service and able to communicate with
the new principal server, the database administrator can resume service. When the session
resumes, any unsent log records and the corresponding updates are lost.

OFF
Removes a database mirroring session and removes mirroring from the database. You can
specify OFF on either partner. For information, see about the impact of removing
mirroring, see Removing Database Mirroring.

RESUME
Resumes a suspended database mirroring session. You can specify RESUME only on the
principal server.

SAFETY { FULL | OFF }

Sets the level of transaction safety. You can specify SAFETY only on the principal server.
The default is FULL. With full safety, the database mirroring session runs synchronously (in
high-safety mode). If SAFETY is set to OFF, the database mirroring session runs
asynchronously (in high-performance mode).
The behavior of high-safety mode depends partly on the witness, as follows:
• When safety is set to FULL and a witness is set for the session, the session runs in
high-safety mode with automatic failover. When the principal server is lost, the session
automatically fails over if the database is synchronized and the mirror server instance
and witness are still connected to each other (that is, they have quorum). For more
information, see Quorum in Database Mirroring Sessions.
If a witness is set for the session but is currently disconnected, the loss of the mirror
server causes the principal server to go down.
• When safety is set to FULL and the witness is set to OFF, the session runs in high-
safety mode without automatic failover. If the mirror server instance goes down, the
principal server instance is unaffected. If the principal server instance goes down, you
can force service (with possible data loss) to the mirror server instance.
If SAFETY is set to OFF, the session runs in high-performance mode, and automatic failover
and manual failover are not supported. However, problems on the mirror do not affect the
principal, and if the principal server instance goes down, you can, if necessary, force service
(with possible data loss) to the mirror server instance—if WITNESS is set to OFF or the
witness is currently connected to the mirror. For more information on forcing service, see
"FORCE_SERVICE_ALLOW_DATA_LOSS" earlier in this section.

Important
High-performance mode is not intended to use a witness. However, whenever you set SAFETY to
OFF, we strongly recommend that you ensure that WITNESS is set to OFF.

SUSPEND
Pauses a database mirroring session.

99
 You can specify SUSPEND on either partner.

TIMEOUT integer
Specifies the time-out period in seconds. The time-out period is the maximum time that a
server instance waits to receive a PING message from another instance in the mirroring
session before considering that other instance to be disconnected.
You can specify the TIMEOUT option only on the principal server. If you do not specify this
option, by default, the time period is 10 seconds. If you specify 5 or greater, the time-out
period is set to the specified number of seconds. If you specify a time-out value of 0 to 4
seconds, the time-out period is automatically set to 5 seconds.

Important
We recommend that you keep the time-out period at 10 seconds or greater. Setting the value to
less than 10 seconds creates the possibility of a heavily loaded system missing PINGs and declaring
a false failure.
For more information, see Possible Failures During Database Mirroring
Sessions.
WITNESS <witness_option>
Controls the database properties that define a database mirroring witness. A SET WITNESS
clause affects both copies of the database, but you can specify SET WITNESS only on the
principal server. If a witness is set for a session, quorum is required to serve the database,
regardless of the SAFETY setting; for more information, see Quorum in Database
Mirroring Sessions.
We recommend that the witness and failover partners reside on separate computers. For
information about the witness, see The Role of the Witness.
To execute a SET WITNESS statement, the STATE of the endpoints of both the principal and
witness server instances must be set to STARTED. Note, also, that the ROLE of the database
mirroring endpoint of a witness server instance must be set to either WITNESS or ALL. For
information about specifying an endpoint, see The Database Mirroring Endpoint.
To learn the role and state of the database mirroring endpoint of a server instance, on that
instance, use the following Transact-SQL statement:
SELECT role_desc, state_desc FROM
sys.database_mirroring_endpoints

Note
Database properties cannot be set on the witness.
<witness_option> ::=

Note
Only one <witness_option> is permitted per SET WITNESS clause.
'witness_server'

100
 Specifies an instance of the Database Engine to act as the witness server for a database
mirroring session. You can specify SET WITNESS statements only on the principal server.
In a SET WITNESS = 'witness_server' statement, the syntax of witness_server is the same as
the syntax of partner_server.

OFF
Removes the witness from a database mirroring session. Setting the witness to OFF
disables automatic failover. If the database is set to FULL SAFETY and the witness is set to
OFF, a failure on the mirror server causes the principal server to make the database
unavailable.

Remarks
Examples
A. Creating a database mirroring session with a witness
Setting up database mirroring with a witness requires configuring security and preparing the
mirror database, and also using ALTER DATABASE to set the partners. For an example of the
complete setup process, see Setting Up Database Mirroring .
B. Manually failing over a database mirroring session
Manual failover can be initiated from either database mirroring partner. Before failing over, you
should verify that the server you believe to be the current principal server actually is the
principal server. For example, for the database, on that server instance that you think is the
current principal server, execute the following query:
SELECT db.name, m.mirroring_role_desc
FROM sys.database_mirroring m
JOIN sys.databases db
ON db.database_id = m.database_id
WHERE db.name = N'AdventureWorks2012';
GO
If the server instance is in fact the principal, the value of mirroring_role_desc is Principal. If
this server instance were the mirror server, the SELECT statement would return Mirror.
The following example assumes that the server is the current principal.
1. Manually fail over to the database mirroring partner:
ALTER DATABASE AdventureWorks2012 SET PARTNER FAILOVER;
GO
2. To verify the results of the failover on the new mirror, execute the following query:
SELECT db.name, m.mirroring_role_desc
FROM sys.database_mirroring m
JOIN sys.databases db

101
 ON db.database_id = m.database_id
WHERE db.name = N'AdventureWorks2012';
GO
The current value of mirroring_role_desc is now Mirror.
See Also
CREATE DATABASE
DATABASEPROPERTYEX
sys.database_mirroring_witnesses

ALTER DATABASE SET HADR

This topic contains the ALTER DATABASE syntax for setting AlwaysOn Availability Groups
options on a secondary database. Only one SET HADR option is permitted per ALTER DATABASE
statement. These options are supported only on secondary replicas.
Transact-SQL Syntax Conventions
Syntax

ALTER DATABASE database_name

SET HADR
{
{ AVAILABILITY GROUP = group_name | OFF }
| { SUSPEND | RESUME }
}
;
Arguments
database_name
Is the name of the secondary database to be modified.

SET HADR
Executes the specified AlwaysOn Availability Groups command on the specified database.

{ AVAILABILITY GROUP = group_name | OFF }

Joins or removes the availability database from the specified availability group, as follows:
group_name
Joins the specified database on the secondary replica that is hosted by the server instance
on which you execute the command to the availability group specified by group_name.
The prerequisites for this operation are as follows:
• The database must already have been added to the availability group on the primary

102
 replica.
• The primary replica must be active. For information about how troubleshoot an
inactive primary replica, see Troubleshooting AlwaysOn Availability Groups
Configuration (SQL Server).
• The primary replica must be online, and the secondary replica must be connected to
the primary replica.
• The secondary database must have been restored using WITH NORECOVERY from
recent database and log backups of the primary database, ending with a log backup
that is recent enough to permit the secondary database to catch up to the primary
database.

Note
To add a database to the availability group, connect to the server instance that hosts the
primary replica, and use the ALTER AVAILABILITY GROUP group_name ADD DATABASE
database_name statement.
For more information, see Joining a Secondary Database to an Availability
Group (SQL Server).
OFF
Removes the specified secondary database from the availability group.
Removing a secondary database can be useful if it has fallen far behind the primary
database, and you do not want to wait for the secondary database to catch up. After
removing the secondary database, you can update it by restoring a sequence of backups
ending with a recent log backup (using RESTORE … WITH NORECOVERY).

Important
To completely remove an availability database from an availability group, connect to the server
instance that hosts the primary replica, and use the ALTER
AVAILABILITY GROUP group_name
REMOVE DATABASE availability_database_name statement. For more information, see Removing
an Availability Database from an Availability Group (SQL Server).
SUSPEND
Suspends data movement on a secondary database. A SUSPEND command returns as soon
as it has been accepted by the replica that hosts the target database, but actually suspending
the database occurs asynchronously.
The scope of the impact depends on where you execute the ALTER DATABASE statement:
• If you suspend a secondary database on a secondary replica, only the local secondary
database is suspended. Existing connections on the readable secondary remain usable.
New connections to the suspended database on the readable secondary are not allowed
until data movement is resumed.
• If you suspend a database on the primary replica, data movement is suspended to the
corresponding secondary databases on every secondary replica. Existing connections on

103
 a readable secondary remain usable and new connections can be made.
• When data movement is suspended due to a forced manual failover, connections to the
new secondary replica are not allowed while data movement is suspended.
When a database on a secondary replica is suspended, both the database and replica
become unsynchronized and are marked as NOT SYNCHRONIZED.

Important
While a secondary database is suspended, the send queue of the corresponding primary database will
accumulate unsent transaction log records. Connections to the secondary replica return data that was
available at the time the data movement was suspended.

Note
Suspending and resuming an AlwaysOn secondary database does not directly affect the availability of
the primary database, though suspending a secondary database can impact redundancy and failover
capabilities for the primary database, until the suspended secondary database is resumed. This is in
contrast to database mirroring, where the mirroring state is suspended on both the mirror database
and the principal database until mirroring is resumed. Suspending an AlwaysOn primary database
suspends data movement on all the corresponding secondary databases, and redundancy and failover
capabilities cease for that database until the primary database is resumed.
For more information, see Suspend a Secondary Database in an Availability
Group (SQL Server).
RESUME
Resumes suspended data movement on the specified secondary database. A RESUME
command returns as soon as it has been accepted by the replica that hosts the target
database, but actually resuming the database occurs asynchronously.
The scope of the impact depends on where you execute the ALTER DATABASE statement:
• If you resume a secondary database on a secondary replica, only the local secondary
database is resumed. Data movement is resumed unless the database has also been
suspended on the primary replica.
• If you resume a database on the primary replica, data movement is resumed to every
secondary replica on which the corresponding secondary database has not also been
suspended locally. To resume a secondary database that was individually suspended on
a secondary replica, connect to the server instance that hosts the secondary replica and
resume the database there.
Under synchronous-commit mode, the database state changes to SYNCHRONIZING. If
no other database is currently suspended, the replica state also changes to
SYNCHRONIZING.
For more information, see Resume a Secondary Database in an Availability
Group (SQL Server).
Database States

104
When a secondary database is joined to an availability group, the local secondary replica
changes the state of that secondary database from RESTORING to ONLINE. If a secondary
database is removed from the availability group, it is set back to the RESTORING state by the
local secondary replica. This allows you to apply subsequent log backups from the primary
database to that secondary database.
Restrictions
Execute ALTER DATABASE statements outside of both transactions and batches.
Security
Permissions
Requires ALTER permission on the database. Joining a database to an availability group requires
membership in the db_owner fixed database role.
Examples
The following example joins the secondary database, AccountsDb1, to the local secondary
replica of the AccountsAG availability group.
ALTER DATABASE AccountsDb1 SET HADR AVAILABILITY GROUP = AccountsAG;

Note
To see this Transact-SQL statement used in context, see Example: Setting Up an
Availability Group Using Windows Authentication (Transact-SQL).
See Also
ALTER DATABASE (Transact-SQL)
ALTER AVAILABILITY GROUP (Transact-SQL)
CREATE AVAILABILITY GROUP (Transact-SQL)
Overview of AlwaysOn Availability Groups (SQL Server)
Troubleshoot AlwaysOn Availability Groups Configuration (SQL Server)

ALTER DATABASE Compatibility Level

Sets certain database behaviors to be compatible with the specified version of SQL Server. For
other ALTER DATABASE options, see ALTER DATABASE (Transact-SQL).
Transact-SQL Syntax Conventions
Syntax

ALTER DATABASE database_name

SET COMPATIBILITY_LEVEL = { 90 | 100 | 110 }
Arguments
database_name
Is the name of the database to be modified.

105
COMPATIBILITY_LEVEL { 90 | 100 | 110 }
Is the version of SQL Server with which the database is to be made compatible. The value
must be one of the following:
90 = SQL Server 2005
100 = SQL Server 2008 and SQL Server 2008 R2
110 = SQL Server 2012

Remarks
For all installations of SQL Server 2012, the default compatibility level is 110. Databases created
in SQL Server 2012 are set to this level unless the model database has a lower compatibility
level. When a database is upgraded to SQL Server 2012 from any earlier version of SQL Server,
the database retains its existing compatibility level if it is at least 90. Upgrading a database with
a compatibility level below 90 sets the database to compatibility level 90. This applies to both
system and user databases. Use ALTER DATABASE to change the compatibility level of the
database. To view the current compatibility level of a database, query the compatibility_level
column in the sys.databases catalog view.
Using Compatibility Level for Backward Compatibility
Compatibility level affects behaviors only for the specified database, not for the entire server.
Compatibility level provides only partial backward compatibility with earlier versions of SQL
Server. Use compatibility level as an interim migration aid to work around version differences in
the behaviors that are controlled by the relevant compatibility-level setting. If existing SQL
Server applications are affected by behavioral differences in SQL Server 2012, convert the
application to work properly. Then use ALTER DATABASE to change the compatibility level to
100. The new compatibility setting for a database takes effect when the database is next made
current (whether as the default database on login or on being specified in a USE statement).
Best Practices
Changing the compatibility level while users are connected to the database can produce
incorrect result sets for active queries. For example, if the compatibility level changes while a
query plan is being compiled, the compiled plan might be based on both the old and new
compatibility levels, resulting in an incorrect plan and potentially inaccurate results. Furthermore,
the problem may be compounded if the plan is placed in the plan cache and reused for
subsequent queries. To avoid inaccurate query results, we recommend the following procedure
to change the compatibility level of a database:
1. Set the database to single-user access mode by using ALTER DATABASE SET SINGLE_USER.
2. Change the compatibility level of the database.
3. Put the database in multiuser access mode by using ALTER DATABASE SET MULTI_USER.
4. For more information about setting the access mode of a database, see ALTER DATABASE
(Transact-SQL).
Compatibility Levels and Stored Procedures

106
When a stored procedure executes, it uses the current compatibility level of the database in
which it is defined. When the compatibility setting of a database is changed, all of its stored
procedures are automatically recompiled accordingly.
Differences Between Compatibility Level 90 and Level 100
This section describes new behaviors introduced with compatibility level 100.

Compatibility-level setting of 90 Compatibility-level setting of 100 Possibility of

impact

The QUOTED_IDENTIFER setting is The QUOTED IDENTIFIER session Medium

always set to ON for multistatement setting is honored when
table-valued functions when they are multistatement table-valued functions
created regardless of the session level are created.
setting.
When you create or alter a partition The current language setting is used to Medium
function, datetime and smalldatetime evaluate datetime and smalldatetime
literals in the function are evaluated literals in the partition function.
assuming US_English as the language
setting.
The FOR BROWSE clause is allowed The FOR BROWSE clause is not allowed Medium
(and ignored) in INSERT and SELECT in INSERT and SELECT INTO
INTO statements. statements.
Full-text predicates are allowed in the Full-text predicates are not allowed in Low
OUTPUT clause. the OUTPUT clause.
CREATE FULLTEXT STOPLIST, ALTER CREATE FULLTEXT STOPLIST, ALTER Low
FULLTEXT STOPLIST, and DROP FULLTEXT STOPLIST, and DROP
FULLTEXT STOPLIST are not supported. FULLTEXT STOPLIST are supported.
The system stoplist is automatically
associated with new full-text indexes.
MERGE is not enforced as a reserved MERGE is a fully reserved keyword. The Low
keyword. MERGE statement is supported under
both 100 and 90 compatibility levels.
Using the <dml_table_source> You can capture the results of an Low
argument of the INSERT statement OUTPUT clause in a nested INSERT,
raises a syntax error. UPDATE, DELETE, or MERGE statement,
and insert those results into a target
table or view. This is done using the
<dml_table_source> argument of the
INSERT statement.

107
Compatibility-level setting of 90 Compatibility-level setting of 100 Possibility of
impact

Unless NOINDEX is specified, DBCC Unless NOINDEX is specified, DBCC Low

CHECKDB or DBCC CHECKTABLE CHECKDB or DBCC CHECKTABLE
performs both physical and logical performs both physical and logical
consistency checks on a single table or consistency checks on a single table
indexed view and on all its and on all its nonclustered indexes.
nonclustered and XML indexes. Spatial However, on XML indexes, spatial
indexes are not supported. indexes, and indexed views, only
physical consistency checks are
performed by default.
If WITH EXTENDED_LOGICAL_CHECKS
is specified, logical checks are
performed on indexed views, XML
indexes, and spatial indexes, where
present. By default, physical
consistency checks are performed
before the logical consistency checks.
If NOINDEX is also specified, only the
logical checks are performed.
When an OUTPUT clause is used with a When an OUTPUT clause is used with a Low
data manipulation language (DML) data manipulation language (DML)
statement and a run-time error occurs statement and a run-time error occurs
during statement execution, the entire during statement execution, the
transaction is terminated and rolled behavior depends on the SET
back. XACT_ABORT setting. If SET
XACT_ABORT is OFF, a statement abort
error generated by the DML statement
using the OUTPUT clause will
terminate the statement, but the
execution of the batch continues and
the transaction is not rolled back. If
SET XACT_ABORT is ON, all run-time
errors generated by the DML
statement using the OUTPUT clause
will terminate the batch, and the
transaction is rolled back.
CUBE and ROLLUP are not enforced as CUBE and ROLLUP are reserved Low
reserved keywords. keywords within the GROUP BY clause.
Strict validation is applied to elements Lax validation is applied to elements of Low

108
Compatibility-level setting of 90 Compatibility-level setting of 100 Possibility of
impact
of the XML anyType type. the anyType type. For more
information, see Wildcard Components
and Content Validation.
The special attributes xsi:nil and The special attributes xsi:nil and Low
xsi:type cannot be queried or xsi:type are stored as regular
modified by data manipulation attributes and can be queried and
language statements. modified.
This means that /e/@xsi:nil fails For example, executing the query
while /e/@* ignores the xsi:nil and SELECT x.query('a/b/@*') returns
xsi:type attributes. However, /e all attributes including xsi:nil and
returns the xsi:nil and xsi:type xsi:type. To exclude these types in the
attributes for consistency with SELECT query, replace @* with @*[namespace-
xmlCol, even if xsi:nil = "false". uri(.) != "insert xsi namespace uri"
and not (local-name(.) = "type"
or local-name(.) ="nil".
A user-defined function that converts A user-defined function that converts Low
an XML constant string value to a SQL an XML constant string value to a SQL
Server datetime type is marked as Server datetime type is marked as non-
deterministic. deterministic.
The XML union and list types are not The union and list types are fully Low
fully supported. supported including the following
functionality:
• Union of list
• Union of union
• List of atomic types
• List of union
The SET options required for an The SET options required for an Low
xQuery method are not validated when xQuery method are validated when the
the method is contained in a view or method is contained in a view or inline
inline table-valued function. table-valued function. An error is
raised if the SET options of the method
are set incorrectly.
XML attribute values that contain end- XML attribute values that contain end- Low
of-line characters (carriage return and of-line characters (carriage return and
line feed) are not normalized line feed) are normalized according to
according to the XML standard. That is, the XML standard. That is, all line
both characters are returned instead of breaks in external parsed entities
109
Compatibility-level setting of 90 Compatibility-level setting of 100 Possibility of
impact
a single line-feed character. (including the document entity) are
normalized on input by translating
both the two-character sequence #xD
#xA and any #xD that is not followed
by #xA to a single #xA character.
Applications that use attributes to
transport string values that contain
end-of-line characters will not receive
these characters back as they are
submitted. To avoid the normalization
process, use the XML numeric
character entities to encode all end-of-
line characters.
The column properties ROWGUIDCOL The column properties ROWGUIDCOL Low
and IDENTITY can be incorrectly and IDENTITY cannot be named as a
named as a constraint. For example the constraint. Error 156 is returned.
statement CREATE TABLE T (C1 int
CONSTRAINT MyConstraint
IDENTITY) executes, but the
constraint name is not preserved and
is not accessible to the user.
Updating columns by using a two-way Updating columns by using a two-way Low
assignment such as UPDATE T1 SET assignment produces expected results
@v = column_name = <expression> because only the statement starting
can produce unexpected results value of the column is accessed during
because the live value of the variable statement execution.
can be used in other clauses such as
the WHERE and ON clause during
statement execution instead of the
statement starting value. This can
cause the meanings of the predicates
to change unpredictably on a per-row
basis.
This behavior is applicable only when
the compatibility level is set to 90.
Variable assignment is allowed in a Variable assignment is not allowed in a Low
statement containing a top-level statement containing a top-level
UNION operator, but returns UNION operator. Error 10734 is

110
Compatibility-level setting of 90 Compatibility-level setting of 100 Possibility of
impact
unexpected results. For example, in the returned.
following statements, local variable @v To resolve the error, rewrite the query
is assigned the value of the column as shown in the following example.
BusinessEntityID from the union of
DECLARE @v int;
two tables. By definition, when the
SELECT statement returns more than SELECT @v = BusinessEntityID
one value, the variable is assigned the FROM
last value that is returned. In this case, (SELECT BusinessEntityID
the variable is correctly assigned the FROM HumanResources.Employee
last value, however, the result set of
UNION ALL
the SELECT UNION statement is also
returned. SELECT BusinessEntityID
FROM
ALTER DATABASE
HumanResources.EmployeeAddress)
AdventureWorks2012
AS Test;
SET compatibility_level = 90;
SELECT @v;
GO
USE AdventureWorks2012;
GO
DECLARE @v int;
SELECT @v = BusinessEntityID
FROM HumanResources.Employee
UNION ALL
SELECT @v = BusinessEntityID
FROM
HumanResources.EmployeeAddress;
SELECT @v;

The ODBC function {fn CONVERT()} The ODBC function {fn CONVERT()} Low
uses the default date format of the uses style 121 (a language-
language. For some languages, the independent YMD format) when
default format is YDM, which can result converting to the ODBC data types
in conversion errors when CONVERT() SQL_TIMESTAMP, SQL_DATE,
is combined with other functions, such SQL_TIME, SQLDATE, SQL_TYPE_TIME,
as {fn CURDATE()}, that expect a YMD and SQL_TYPE_TIMESTAMP.
format.
The ODBC function {fn CURDATE()} The ODBC function {fn CURDATE()} Low
returns only the date in the format returns both date and time, for

111
Compatibility-level setting of 90 Compatibility-level setting of 100 Possibility of
impact
'YYYY-MM-DD'. example 'YYYY-MM-DD hh:mm:ss.
Datetime intrinsics such as DATEPART Datetime intrinsics such as DATEPART Low
do not require string input values to require string input values to be valid
be valid datetime literals. For example, datetime literals. Error 241 is returned
SELECT DATEPART (year, '2007/05-30') when an invalid datetime literal is
compiles successfully. used.

Differences Between Lower Compatibility Levels and Level 110

This section describes new behaviors introduced with compatibility level 110.

Compatibility-level setting of 100 or lower Compatibility-level setting of 110

Common language runtime (CLR) database CLR database objects are executed with
objects are executed with version 4 of the version 4 of the CLR.
CLR. However, some behavior changes
introduced in version 4 of the CLR are
avoided. For more information, see What's
New in CLR Integration.
The XQuery functions string-length and The XQuery functions string-length and
substring count each surrogate as two substring count each surrogate as one
characters. character.
PIVOT is allowed in a recursive common PIVOT is not allowed in a recursive
table expression (CTE) query. However, the common table expression (CTE) query. An
query returns incorrect results when there error is returned.
are multiple rows per grouping.
The RC4 algorithm is only supported for New material cannot be encrypted using
backward compatibility. New material can RC4 or RC4_128. Use a newer algorithm
only be encrypted using RC4 or RC4_128 such as one of the AES algorithms instead.
when the database is in compatibility level In SQL Server 2012, material encrypted
90 or 100. (Not recommended.) In SQL using RC4 or RC4_128 can be decrypted in
Server 2012, material encrypted using RC4 any compatibility level.
or RC4_128 can be decrypted in any
compatibility level.
The default style for CAST and CONVERT Under compatibility level 110, the default
operations on time and datetime2 data style for CAST and CONVERT operations on
types is 121 except when either type is time and datetime2 data types is always
used in a computed column expression. For 121. If your query relies on the old
112
Compatibility-level setting of 100 or lower Compatibility-level setting of 110
computed columns, the default style is 0. behavior, use a compatibility level less than
This behavior impacts computed columns 110, or explicitly specify the 0 style in the
when they are created, used in queries affected query.
involving auto-parameterization, or used inUpgrading the database to compatibility
constraint definitions. level 110 will not change user data that has
The following example shows the difference been stored to disk. You must manually
between styles 0 and 121. It does not correct this data as appropriate. For
demonstrate the behavior described above. example, if you used SELECT INTO to create
For more information about date and time a table from a source that contained a
styles, see CAST and CONVERT (Transact- computed column expression described
SQL). above, the data (using style 0) would be
CREATE TABLE t1 (c1 time(7), c2 stored rather than the computed column
datetime2); definition itself. You would need to
manually update this data to match style
121.
INSERT t1 (c1,c2) VALUES
(GETDATE(), GETDATE());

SELECT CONVERT(nvarchar(16),c1,0)
AS TimeStyle0

,CONVERT(nvarchar(16),c1,121)AS
TimeStyle121
,CONVERT(nvarchar(32),c2,0)
AS Datetime2Style0

,CONVERT(nvarchar(32),c2,121)AS
Datetime2Style121
FROM t1;

-- Returns values such as the

following.
TimeStyle0 TimeStyle121
Datetime2Style0
Datetime2Style121
---------------- ---------------- -
------------------- ---------------

113
Compatibility-level setting of 100 or lower Compatibility-level setting of 110

-----------
3:15PM 15:15:35.8100000
Jun 7 2011 3:15PM 2011-06-07
15:15:35.8130000

Any columns in remote tables of type Any columns in remote tables of type
smalldatetime that are referenced in a smalldatetime that are referenced in a
partitioned view are mapped as datetime. partitioned view are mapped as
Corresponding columns in local tables (in smalldatetime. Corresponding columns in
the same ordinal position in the select list) local tables (in the same ordinal position in
must be of type datetime. the select list) must be of type
smalldatetime.
After upgrading to 110, the distributed
partitioned view will fail because of the
data type mismatch. You can resolve this by
changing the data type on the remote table
to datetime or setting the compatibility
level of the local database to 100 or lower.
SOUNDEX function implements the SOUNDEX function implements the
following rules. following rules
1. If character_expression has any double 1. If character_expression has any double
letters, they are treated as one letter. letters, they are treated as one letter.
2. If a vowel (A, E, I, O, U) separates two 2. If character_expression has different
consonants that have the same soundex letters side-by-side that have the same
code, the consonant to the right of the number in the soundex coding guide,
vowel is coded. they are treated as one letter.
3. If a vowel (A, E, I, O, U) separates two
consonants that have the same soundex
code, the consonant to the right of the
vowel is coded.
4. If H or W separate two consonants that
have the same soundex code, the
consonant to the right of the vowel is
not coded.
The additional rules may cause the values
computed by the SOUNDEX function to be
different than the values computed under
earlier compatibility levels. After upgrading
to compatibility level 110, you may need to

114
Compatibility-level setting of 100 or lower Compatibility-level setting of 110
rebuild the indexes, heaps, or CHECK
constraints that use the SOUNDEX function.
For more information, see SOUNDEX
(Transact-SQL)

Reserved Keywords
The compatibility setting also determines the keywords that are reserved by the Database
Engine. The following table shows the reserved keywords that are introduced by each of the
compatibility levels.

Compatibility-level setting Reserved keywords

110 WITHIN GROUP, TRY_CONVERT,

SEMANTICKEYPHRASETABLE,
SEMANTICSIMILARITYDETAILSTABLE,
SEMANTICSIMILARITYTABLE
100 CUBE, MERGE, ROLLUP
90 EXTERNAL, PIVOT, UNPIVOT, REVERT,
TABLESAMPLE

At a given compatibility level, the reserved keywords include all of the keywords introduced at
or below that level. Thus, for instance, for applications at level 110, all of the keywords listed in
the preceding table are reserved. At the lower compatibility levels, level-100 keywords remain
valid object names, but the level-110 language features corresponding to those keywords are
unavailable.
Once introduced, a keyword remains reserved. For example, the reserved keyword PIVOT, which
was introduced in compatibility level 90, is also reserved in levels 100 and 110.
If an application uses an identifier that is reserved as a keyword for its compatibility level, the
application will fail. To work around this, enclose the identifier between either brackets ([ ]) or
quotation marks (" "); for example, to upgrade an application that uses the identifier EXTERNAL
to compatibility level 90, you could change the identifier to either [EXTERNAL] or "EXTERNAL".
For more information, see Reserved Keywords (Transact-SQL).
Permissions
Requires ALTER permission on the database.
Examples
A. Changing the compatibility level

115
The following example changes the compatibility level of the database to 110, SQL Server
2012.
ALTER DATABASE AdventureWorks2012
SET COMPATIBILITY_LEVEL = 110;
GO
See Also
ALTER DATABASE
Reserved Keywords
CREATE DATABASE
DATABASEPROPERTYEX
sys.databases (Transact-SQL)
sys.database_files

ALTER DATABASE AUDIT SPECIFICATION

Alters a database audit specification object using the SQL Server Audit feature. For more
information, see Understanding SQL Server Audit.
Transact-SQL Syntax Conventions
Syntax

ALTER DATABASE AUDIT SPECIFICATION audit_specification_name

{
[ FOR SERVER AUDIT audit_name ]
[ { { ADD | DROP } (
{ <audit_action_specification> | audit_action_group_name }
)
} [, ...n] ]
[ WITH ( STATE = { ON | OFF } ) ]
}
[;]
<audit_action_specification>::=
{
<action_specification>[ ,...n ]ON [ class :: ] securable [ ( column [ ,...n ] ) ]
BY principal [ ,...n ]
}

116
<action_specification>::=
{
action [ ( column [ ,...n ] ) ]
}
Arguments
audit_specification_name
The name of the audit specification.

audit_name
The name of the audit to which this specification is applied.

audit_action_specification
Name of one or more database-level auditable actions. For a list of audit action groups, see
SQL Server Audit Action Groups and Actions.
audit_action_group_name
Name of one or more groups of database-level auditable actions. For a list of audit action
groups, see SQL Server Audit Action Groups and Actions.
class
Class name (if applicable) on the securable.

securable
Table, view, or other securable object in the database on which to apply the audit action or
audit action group. For more information, see Securables.

column
Column name (if applicable) on the securable.

principal
Name of SQL Server principal on which to apply the audit action or audit action group. For
more information, see Principals (Database Engine).
WITH ( STATE = { ON | OFF } )
Enables or disables the audit from collecting records for this audit specification. Audit
specification state changes must be done outside a user transaction and may not have other
changes in the same statement when the transition is ON to OFF.

Remarks
Database audit specifications are non-securable objects that reside in a given database. You
must set the state of an audit specification to the OFF option in order to make changes to a
database audit specification. If ALTER DATABASE AUDIT SPECIFICATION is executed when an
audit is enabled with any options other than STATE=OFF, you will receive an error message. For
more information, see tempdb Database.

117
Permissions
Users with the ALTER ANY DATABASE AUDIT permission can alter database audit specifications
and bind them to any audit.
After a database audit specification is created, it can be viewed by principals with the CONTROL
SERVER, or ALTER ANY DATABASE AUDIT permissions, the sysadmin account, or principals
having explicit access to the audit.
Examples
The following example alters a database audit specification called
HIPPA_Audit_DB_Specification that audits the SELECT statements by the dbo user, for a SQL
Server audit called HIPPA_Audit.
ALTER DATABASE AUDIT SPECIFICATION HIPPA_Audit_DB_Specification
FOR SERVER AUDIT HIPPA_Audit
ADD (SELECT
ON Table1(Column1)
BY dbo)
WITH STATE = ON;
GO
For a full example about how to create an audit, see Understanding SQL Server Audit.

Updated content

Corrected the Permissions section.

See Also
CREATE SERVER AUDIT (Transact-SQL)
ALTER SERVER AUDIT (Transact-SQL)
DROP SERVER AUDIT (Transact-SQL)
CREATE SERVER AUDIT SPECIFICATION (Transact-SQL)
ALTER SERVER AUDIT SPECIFICATION (Transact-SQL)
DROP SERVER AUDIT SPECIFICATION (Transact-SQL)
CREATE DATABASE AUDIT SPECIFICATION (Transact-SQL)
DROP DATABASE AUDIT SPECIFICATION (Transact-SQL)
ALTER AUTHORIZATION (Transact-SQL)
fn_get_audit_file (Transact-SQL)
sys.server_audits (Transact-SQL)

118
sys.server_file_audits (Transact-SQL)
sys.server_audit_specifications (Transact-SQL)
sys.server_audit_specifications_details (Transact-SQL)
sys.database_ audit_specifications (Transact-SQL)
sys.audit_database_specification_details (Transact-SQL)
sys.dm_server_audit_status
sys.dm_audit_actions
Create a Server Audit and Server Audit Specification

ALTER DATABASE ENCRYPTION KEY

Alters an encryption key and certificate that is used for transparently encrypting a database. For
more information about transparent database encryption, see Understanding Transparent Data
Encryption (TDE).
Transact-SQL Syntax Conventions
Syntax

ALTER DATABASE ENCRYPTION KEY

REGENERATE WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }
|
ENCRYPTION BY SERVER
{
CERTIFICATE Encryptor_Name |
ASYMMETRIC KEY Encryptor_Name
}
[;]
Arguments
REGENERATE WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }
Specifies the encryption algorithm that is used for the encryption key.

ENCRYPTION BY SERVER CERTIFICATE Encryptor_Name

Specifies the name of the certificate used to encrypt the database encryption key.

ENCRYPTION BY SERVER ASYMMETRIC KEY Encryptor_Name

Specifies the name of the asymmetric key used to encrypt the database encryption key.

Remarks

119
The certificate or asymmetric key that is used to encrypt the database encryption key must be
located in the master system database.
The database encryption key does not have to be regenerated when a database owner (dbo) is
changed.
After a database encryption key has been modified twice, a log backup must be performed
before the database encryption key can be modified again.
Permissions
Requires CONTROL permission on the database and VIEW DEFINITION permission on the
certificate or asymmetric key that is used to encrypt the database encryption key.
Examples
The following example alters the database encryption key to use the AES_256 algorithm.
USE AdventureWorks2012;
GO
ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_256;
GO

See Also
Understanding Transparent Data Encryption (TDE)
SQL Server Encryption
SQL Server and Database Encryption Keys (Database Engine)
Encryption Hierarchy
ALTER DATABASE SET Options (Transact-SQL)
CREATE DATABASE ENCRYPTION KEY (Transact-SQL)
DROP DATABASE ENCRYPTION KEY (Transact-SQL)
sys.dm_database_encryption_keys

ALTER ENDPOINT
Enables modifying an existing endpoint in the following ways:
• By adding a new method to an existing endpoint.
• By modifying or dropping an existing method from the endpoint.
• By changing the properties of an endpoint.

Note

120
 This topic describes the syntax and arguments that are specific to ALTER ENDPOINT. For
descriptions of the arguments that are common to both CREATE ENDPOINT and ALTER
ENDPOINT, see CREATE ENDPOINT (Transact-SQL).
Native XML Web Services (SOAP/HTTP endpoints) is removed beginning in SQL Server 2012.
Transact-SQL Syntax Conventions
Syntax

ALTER ENDPOINT endPointName [ AUTHORIZATION login ]

[ STATE = { STARTED | STOPPED | DISABLED } ]
[ AS { TCP } ( <protocol_specific_items> ) ]
[ FOR { TSQL | SERVICE_BROKER | DATABASE_MIRRORING } (
<language_specific_items>
)]

<AS TCP_protocol_specific_arguments> ::=

AS TCP (
LISTENER_PORT = listenerPort
[ [ , ] LISTENER_IP = ALL | ( 4-part-ip ) | ( "ip_address_v6" ) ]
)
<FOR SERVICE_BROKER_language_specific_arguments> ::=
FOR SERVICE_BROKER (
[ AUTHENTICATION = {
WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
| CERTIFICATE certificate_name
| WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] CERTIFICATE certificate_name
| CERTIFICATE certificate_name WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
}]
[ , ENCRYPTION = { DISABLED
|
{{SUPPORTED | REQUIRED }
[ ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ] }
]

[ , MESSAGE_FORWARDING = {ENABLED | DISABLED} ]

[ , MESSAGE_FORWARD_SIZE = forwardSize

121
)

<FOR DATABASE_MIRRORING_language_specific_arguments> ::=

FOR DATABASE_MIRRORING (
[ AUTHENTICATION = {
WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
| CERTIFICATE certificate_name
| WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] CERTIFICATE certificate_name
| CERTIFICATE certificate_name WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
}]
[ , ENCRYPTION = { DISABLED
|
{{SUPPORTED | REQUIRED }
[ ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ] }
]
[ , ] ROLE = { WITNESS | PARTNER | ALL }
)

Arguments

Note
The following arguments are specific to ALTER ENDPOINT. For descriptions of the
remaining arguments, see CREATE ENDPOINT (Transact-SQL).
AS { TCP }
You cannot change the transport protocol with ALTER ENDPOINT.

AUTHORIZATION login
The AUTHORIZATION option is not available in ALTER ENDPOINT. Ownership can only be
assigned when the endpoint is created.

FOR { TSQL | SERVICE_BROKER | DATABASE_MIRRORING }

You cannot change the payload type with ALTER ENDPOINT.

Remarks
When you use ALTER ENDPOINT, specify only those parameters that you want to update. All
properties of an existing endpoint remain the same unless you explicitly change them.
The ENDPOINT DDL statements cannot be executed inside a user transaction.
For information on choosing an encryption algorithm for use with an endpoint, see Choosing an
Encryption Algorithm.
122
 Note
• The RC4 algorithm is only supported for backward compatibility. New material can only
be encrypted using RC4 or RC4_128 when the database is in compatibility level 90 or
100. (Not recommended.) Use a newer algorithm such as one of the AES algorithms
instead. In SQL Server 2012 material encrypted using RC4 or RC4_128 can be decrypted
in any compatibility level.
• RC4 is a relatively weak algorithm, and AES is a relatively strong algorithm. But AES is
considerably slower than RC4. If security is a higher priority for you than speed, we
recommend you use AES.
Permissions
User must be a member of the sysadmin fixed server role, the owner of the endpoint, or have
been granted ALTER ANY ENDPOINT permission.
To change ownership of an existing endpoint, you must use the ALTER AUTHORIZATION
statement. For more information, see ALTER AUTHORIZATION (Transact-SQL).
For more information, see GRANT Endpoint Permissions (Transact-SQL).
See Also
DROP ENDPOINT (Transact-SQL)
eventdata (Transact-SQL)

ALTER EVENT SESSION

Starts or stops an event session or changes an event session configuration.
Transact-SQL Syntax Conventions
Syntax

ALTER EVENT SESSION event_session_name

ON SERVER
{
[ [ { <add_drop_event> [ ,...n] }
| { <add_drop_event_target> [ ,...n ] } ]
[ WITH ( <event_session_options> [ ,...n ] ) ]
]
| [ STATE = { START | STOP } ]
}

<add_drop_event>::=

123
{
[ ADD EVENT <event_specifier>
[({
[ SET { event_customizable_attribute = <value> [ ,...n ] } ]
[ ACTION ( { [event_module_guid].event_package_name.action_name [ ,...n ] } )
]
[ WHERE <predicate_expression> ]
})]
]
| DROP EVENT <event_specifier> }

<event_specifier> ::=
{
[event_module_guid].event_package_name.event_name
}

<predicate_expression> ::=
{
[ NOT ] <predicate_factor> | {( <predicate_expression> ) }
[ { AND | OR } [ NOT ] { <predicate_factor> | ( <predicate_expression> ) } ]
[ ,...n ]
}

<predicate_factor>::=
{
<predicate_leaf> | ( <predicate_expression> )
}

<predicate_leaf>::=
{
<predicate_source_declaration> { = | < > | ! = | > | > = | < | < = } <value>
| [event_module_guid].event_package_name.predicate_compare_name (
<predicate_source_declaration>, <value> )
}

<predicate_source_declaration>::=

124
{
event_field_name | (
[event_module_guid].event_package_name.predicate_source_name )
}

<value>::=
{
number | 'string'
}

<add_drop_event_target>::=
{
ADD TARGET <event_target_specifier>
[ ( SET { target_parameter_name = <value> [ ,...n] } ) ]
| DROP TARGET <event_target_specifier>
}

<event_target_specifier>::=
{
[event_module_guid].event_package_name.target_name
}

<event_session_options>::=
{
[ MAX_MEMORY = size [ KB | MB ] ]
[ [,] EVENT_RETENTION_MODE = { ALLOW_SINGLE_EVENT_LOSS |
ALLOW_MULTIPLE_EVENT_LOSS | NO_EVENT_LOSS } ]
[ [,] MAX_DISPATCH_LATENCY = { seconds SECONDS | INFINITE } ]
[ [,] MAX_EVENT_SIZE = size [ KB | MB ] ]
[ [,] MEMORY_PARTITION_MODE = { NONE | PER_NODE | PER_CPU } ]
[ [,] TRACK_CAUSALITY = { ON | OFF } ]
[ [,] STARTUP_STATE = { ON | OFF } ]
}
Arguments

125
Term Definition
event_session_name Is the name of an existing event
session.
STATE = START | STOP Starts or stops the event session.
This argument is only valid when
ALTER EVENT SESSION is applied to
an event session object.
ADD EVENT <event_specifier> Associates the event identified by
<event_specifier> with the event
session.
[event_module_guid].event_package_name.event_name Is the name of an event in an event
package, where:
• event_module_guid is the GUID
for the module that contains the
event.
• event_package_name is the
package that contains the action
object.
• event_name is the event object.
Events appear in the
sys.dm_xe_objects view as
object_type 'event'.
SET { event_customizable_attribute = <value> [ ,...n] } Specifies customizable attributes for
the event. Customizable attributes
appear in the
sys.dm_xe_object_columns view as
column_type 'customizable ' and
object_name = event_name.
ACTION ( { Is the action to associate with the
[event_module_guid].event_package_name.action_name [ event session, where:
,...n] } ) • event_module_guid is the GUID
for the module that contains the
event.
• event_package_name is the
package that contains the action
object.
• action_name is the action object.
Actions appear in the

126
 sys.dm_xe_objects view as
object_type 'action'.
WHERE <predicate_expression> Specifies the predicate expression
used to determine if an event
should be processed. If
<predicate_expression> is true, the
event is processed further by the
actions and targets for the session.
If <predicate_expression> is false,
the event is dropped by the session
before being processed by the
actions and targets for the session.
Predicate expressions are limited to
3000 characters, which limits string
arguments.
event_field_name Is the name of the event field that
identifies the predicate source.
[event_module_guid].event_package_name.predicate_sour Is the name of the global predicate
ce_name source where:
• event_module_guid is the GUID
for the module that contains the
event.
• event_package_name is the
package that contains the
predicate object.
• predicate_source_name is
defined in the sys.dm_xe_objects
view as object_type
'pred_source'.
[event_module_guid].event_package_name.predicate_com Is the name of the predicate object
pare_name to associate with the event, where:
• event_module_guid is the GUID
for the module that contains the
event.
• event_package_name is the
package that contains the
predicate object.
• predicate_compare_name is a
global source defined in the

127
 sys.dm_xe_objects view as
object_type 'pred_compare'.
DROP EVENT <event_specifier> Drops the event identified by
<event_specifier>.
<event_specifier> must be valid in
the event session.
ADD TARGET <event_target_specifier> Associates the target identified by
<event_target_specifier> with the
event session.
[event_module_guid].event_package_name.target_name Is the name of a target in the event
session, where:
• event_module_guid is the GUID
for the module that contains the
event.
• event_package_name is the
package that contains the action
object.
• target_name is the action.
Actions appear in
sys.dm_xe_objects view as
object_type 'target'.
SET { target_parameter_name = <value> [, ...n] } Sets a target parameter. Target
parameters appear in the
sys.dm_xe_object_columns view as
column_type 'customizable' and
object_name = target_name.

Important
If you are using the ring
buffer target, we
recommend that you set the
max_memory target
parameter to 2048 kilobytes
(KB) to help avoid possible
data truncation of the XML
output. For more
information about when to
use the different target
types, see SQL Server
Extended Events Targets.

128
DROP TARGET <event_target_specifier> Drops the target identified by
<event_target_specifier>.
<event_target_specifier> must be
valid in the event session.
EVENT_RETENTION_MODE = { Specifies the event retention mode
ALLOW_SINGLE_EVENT_LOSS | to use for handling event loss.
ALLOW_MULTIPLE_EVENT_LOSS | NO_EVENT_LOSS } ALLOW_SINGLE_EVENT_
LOSS
An event can be lost
from the session. A
single event is only
dropped when all the
event buffers are full.
Losing a single event
when event buffers are
full allows for
acceptable SQL Server
performance
characteristics, while
minimizing the loss of
data in the processed
event stream.

ALLOW_MULTIPLE_EVEN
T_LOSS
Full event buffers
containing multiple
events can be lost from
the session. The
number of events lost is
dependent upon the
memory size allocated
to the session, the
partitioning of the
memory, and the size of
the events in the buffer.
This option minimizes
performance impact on
the server when event
buffers are quickly
filled, but large
numbers of events can

129
 be lost from the
session.

NO_EVENT_LOSS
No event loss is
allowed. This option
ensures that all events
raised will be retained.
Using this option forces
all tasks that fire events
to wait until space is
available in an event
buffer. This may cause
detectable performance
issues while the event
session is active. User
connections may stall
while waiting for events
to be flushed from the
buffer.

MAX_DISPATCH_LATENCY = { seconds SECONDS | Specifies the amount of time that

INFINITE } events are buffered in memory
before being dispatched to event
session targets. The minimum
latency value is 1 second. However,
0 can be used to specify INFINITE
latency. By default, this value is set
to 30 seconds.
seconds SECONDS
The time, in seconds, to
wait before starting to
flush buffers to targets.
seconds is a whole
number.

INFINITE
Flush buffers to targets
only when the buffers
are full, or when the
event session closes.

Note
MAX_DISPATCH_LATENCY = 0

130
 SECONDS is equivalent to
MAX_DISPATCH_LATENCY =
INFINITE.

MAX_EVENT_SIZE = size [ KB | MB ] Specifies the maximum allowable

size for events. MAX_EVENT_SIZE
should only be set to allow single
events larger than MAX_MEMORY;
setting it to less than
MAX_MEMORY will raise an error.
size is a whole number and can be a
kilobyte (KB) or a megabyte (MB)
value. If size is specified in kilobytes,
the minimum allowable size is 64
KB. When MAX_EVENT_SIZE is set,
two buffers of size are created in
addition to MAX_MEMORY. This
means that the total memory used
for event buffering is
MAX_MEMORY + 2 *
MAX_EVENT_SIZE.

MEMORY_PARTITION_MODE = { NONE | PER_NODE | Specifies the location where event

PER_CPU } buffers are created.
NONE
A single set of buffers is
created within the SQL
Server instance.

PER_NODE A set of
buffers is
created for
each NUMA
node.
PER_CPU A set of
buffers is
created for
each CPU.

131
TRACK_CAUSALITY = { ON | OFF } Specifies whether or not causality is
tracked. If enabled, causality allows
related events on different server
connections to be correlated
together.
STARTUP_STATE = { ON | OFF } Specifies whether or not to start this
event session automatically when
SQL Server starts.

nNote
If STARTUP_STATE = ON, the
event session will only start if
SQL Server is stopped and
then restarted.

Term Definition
ON The event session
is started at
startup.
OFF The event session
is not started at
startup.

Remarks
The ADD and DROP arguments cannot be used in the same statement.
Permissions
Requires the ALTER ANY EVENT SESSION permission.
Examples
The following example starts an event session, obtains some live session statistics, and then
adds two events to the existing session.
-- Start the event session
ALTER EVENT SESSION test_session
ON SERVER

132
STATE = start
GO
-- Obtain live session statistics
SELECT * FROM sys.dm_xe_sessions
SELECT * FROM sys.dm_xe_session_events
GO

-- Add new events to the session

ALTER EVENT SESSION test_session ON SERVER
ADD EVENT sqlserver.database_transaction_begin,
ADD EVENT sqlserver.database_transaction_end
GO

See Also
CREATE EVENT SESSION (Transact-SQL)
DROP EVENT SESSION (Transact-SQL)
Extended Event Targets
sys.server_event_sessions
sys.dm_xe_objects
sys.dm_xe_object_columns

ALTER FULLTEXT CATALOG

Changes the properties of a full-text catalog.
Transact-SQL Syntax Conventions
Syntax

ALTER FULLTEXT CATALOG catalog_name

{ REBUILD [ WITH ACCENT_SENSITIVITY = { ON | OFF } ]
| REORGANIZE
| AS DEFAULT
}
Arguments
catalog_name
Specifies the name of the catalog to be modified. If a catalog with the specified name does

133
 not exist, Microsoft SQL Server returns an error and does not perform the ALTER operation.

REBUILD
Tells SQL Server to rebuild the entire catalog. When a catalog is rebuilt, the existing catalog is
deleted and a new catalog is created in its place. All the tables that have full-text indexing
references are associated with the new catalog. Rebuilding resets the full-text metadata in
the database system tables.

WITH ACCENT_SENSITIVITY = {ON|OFF}

Specifies if the catalog to be altered is accent-sensitive or accent-insensitive for full-text
indexing and querying.
To determine the current accent-sensitivity property setting of a full-text catalog, use the
FULLTEXTCATALOGPROPERTY function with the accentsensitivity property value against
catalog_name. If the function returns '1', the full-text catalog is accent sensitive; if the
function returns '0', the catalog is not accent sensitive.
The catalog and database default accent sensitivity are the same.

REORGANIZE
Tells SQL Server to perform a master merge, which involves merging the smaller indexes
created in the process of indexing into one large index. Merging the full-text index
fragments can improve performance and free up disk and memory resources. If there are
frequent changes to the full-text catalog, use this command periodically to reorganize the
full-text catalog.
REORGANIZE also optimizes internal index and catalog structures.
Keep in mind that, depending on the amount of indexed data, a master merge may take
some time to complete. Master merging a large amount of data can create a long running
transaction, delaying truncation of the transaction log during checkpoint. In this case, the
transaction log might grow significantly under the full recovery model. As a best practice,
ensure that your transaction log contains sufficient space for a long-running transaction
before reorganizing a large full-text index in a database that uses the full recovery model. For
more information, see Managing the Size of the Transaction Log File.
AS DEFAULT
Specifies that this catalog is the default catalog. When full-text indexes are created with no
specified catalogs, the default catalog is used. If there is an existing default full-text catalog,
setting this catalog AS DEFAULT will override the existing default.

Permissions
User must have ALTER permission on the full-text catalog, or be a member of the db_owner,
db_ddladmin fixed database roles, or sysadmin fixed server role.

Note

134
 To use ALTER FULLTEXT CATALOG AS DEFAULT, the user must have ALTER permission on
the full-text catalog and CREATE FULLTEXT CATALOG permission on the database.
Examples
The following example changes the accentsensitivity property of the default full-text
catalog ftCatalog, which is accent sensitive.
--Change to accent insensitive
USE AdventureWorks;
GO
ALTER FULLTEXT CATALOG ftCatalog
REBUILD WITH ACCENT_SENSITIVITY=OFF;
GO
-- Check Accentsensitivity
SELECT FULLTEXTCATALOGPROPERTY('ftCatalog', 'accentsensitivity');
GO
--Returned 0, which means the catalog is not accent sensitive.

See Also
sys.fulltext_catalogs (Transact-SQL)
Full-Text Search
DROP FULLTEXT CATALOG
Full-Text Search

ALTER FULLTEXT INDEX

Changes the properties of a full-text index.
Transact-SQL Syntax Conventions
Syntax

ALTER FULLTEXT INDEX ON table_name

{ ENABLE
| DISABLE
| SET CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF }
| ADD ( column_name
[ TYPE COLUMN type_column_name ]
[ LANGUAGE language_term ]
[ STATISTICAL_SEMANTICS ]
135
 [,...n] )
[ WITH NO POPULATION ]
| ALTER COLUMN column_name
{ ADD | DROP } STATISTICAL_SEMANTICS
[ WITH NO POPULATION ]
| DROP ( column_name [,...n] )
[ WITH NO POPULATION ]
| START { FULL | INCREMENTAL | UPDATE } POPULATION
| {STOP | PAUSE | RESUME } POPULATION
| SET STOPLIST [ = ] { OFF| SYSTEM | stoplist_name }
[ WITH NO POPULATION ]
| SET SEARCH PROPERTY LIST [ = ] { OFF | property_list_name }
[ WITH NO POPULATION ]
}
[;]
Arguments
table_name
Is the name of the table or indexed view that contains the column or columns included in the
full-text index. Specifying database and table owner names is optional.

ENABLE | DISABLE
Tells SQL Server whether to gather full-text index data for table_name. ENABLE activates the
full-text index; DISABLE turns off the full-text index. The table will not support full-text
queries while the index is disabled.
Disabling a full-text index allows you to turn off change tracking but keep the full-text index,
which you can reactivate at any time using ENABLE. When the full-text index is disabled, the
full-text index metadata remains in the system tables. If CHANGE_TRACKING is in the enabled
state (automatic or manual update) when the full-text index is disabled, the state of the index
freezes, any ongoing crawl stops, and new changes to the table data are not tracked or
propagated to the index.

SET CHANGE_TRACKING {MANUAL | AUTO | OFF}

Specifies whether changes (updates, deletes, or inserts) made to table columns that are
covered by the full-text index will be propagated by SQL Server to the full-text index. Data
changes through WRITETEXT and UPDATETEXT are not reflected in the full-text index, and are
not picked up with change tracking.

Note
For information about the interaction of change tracking and WITH NO POPULATION, see "Remarks,"

136
 later in this topic.

MANUAL
Specifies that the tracked changes will be propagated manually by calling the ALTER
FULLTEXT INDEX … START UPDATE POPULATION Transact-SQL statement (manual
population). You can use SQL Server Agent to call this Transact-SQL statement periodically.

AUTO
Specifies that the tracked changes will be propagated automatically as data is modified in the
base table (automatic population). Although changes are propagated automatically, these
changes might not be reflected immediately in the full-text index. AUTO is the default.

OFF
Specifies that SQL Server will not keep a list of changes to the indexed data.

ADD | DROP column_name

Specifies the columns to be added or deleted from a full-text index. The column or columns
must be of type char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary, or
varbinary(max).
Use the DROP clause only on columns that have been enabled previously for full-text
indexing.
Use TYPE COLUMN and LANGUAGE with the ADD clause to set these properties on the
column_name. When a column is added, the full-text index on the table must be repopulated
in order for full-text queries against this column to work.

Note
Whether the full-text index is populated after a column is added or dropped from a full-text index
depends on whether change-tracking is enabled and whether WITH NO POPULATION is specified. For
more information, see "Remarks," later in this topic.

TYPE COLUMN type_column_name

Specifies the name of a table column, type_column_name, that is used to hold the document
type for a varbinary, varbinary(max), or image document. This column, known as the type
column, contains a user-supplied file extension (.doc, .pdf, .xls, and so forth). The type column
must be of type char, nchar, varchar, or nvarchar.
Specify TYPE COLUMN type_column_name only if column_name specifies a varbinary,
varbinary(max) or image column, in which data is stored as binary data; otherwise, SQL
Server returns an error.

Note
At indexing time, the Full-Text Engine uses the abbreviation in the type column of each table row to
identify which full-text search filter to use for the document in column_name. The filter loads the
document as a binary stream, removes the formatting information, and sends the text from the
document to the word-breaker component. For more information, see Full-Text Search Filters.

137
LANGUAGE language_term
Is the language of the data stored in column_name.
language_term is optional and can be specified as a string, integer, or hexadecimal value
corresponding to the locale identifier (LCID) of a language. If language_term is specified, the
language it represents will be applied to all elements of the search condition. If no value is
specified, the default full-text language of the SQL Server instance is used.
Use the sp_configure stored procedure to access information about the default full-text
language of the SQL Server instance.
When specified as a string, language_term corresponds to the alias column value in the
syslanguages system table. The string must be enclosed in single quotation marks, as in
'language_term'. When specified as an integer, language_term is the actual LCID that
identifies the language. When specified as a hexadecimal value, language_term is 0x followed
by the hex value of the LCID. The hex value must not exceed eight digits, including leading
zeros.
If the value is in double-byte character set (DBCS) format, SQL Server will convert it to
Unicode.
Resources, such as word breakers and stemmers, must be enabled for the language specified
as language_term. If such resources do not support the specified language, SQL Server
returns an error.
For non-BLOB and non-XML columns containing text data in multiple languages, or for cases
when the language of the text stored in the column is unknown, use the neutral (0x0)
language resource. For documents stored in XML- or BLOB-type columns, the language
encoding within the document will be used at indexing time. For example, in XML columns,
the xml:lang attribute in XML documents will identify the language. At query time, the value
previously specified in language_term becomes the default language used for full-text
queries unless language_term is specified as part of a full-text query.

STATISTICAL_SEMANTICS
Creates the additional key phrase and document similarity indexes that are part of statistical
semantic indexing. For more information, see Semantic Search.
[ ,...n]
Indicates that multiple columns may be specified for the ADD, ALTER, or DROP clauses. When
multiple columns are specified, separate these columns with commas.

WITH NO POPULATION
Specifies that the full-text index will not be populated after an ADD or DROP column
operation or a SET STOPLIST operation. The index will only be populated if the user executes
a START...POPULATION command.
When NO POPULATION is specified, SQL Server does not populate an index. The index is
populated only after the user gives an ALTER FULLTEXT INDEX...START POPULATION
command. When NO POPULATION is not specified, SQL Server populates the index.

138
 If CHANGE_TRACKING is enabled and WITH NO POPULATION is specified, SQL Server returns
an error. If CHANGE_TRACKING is enabled and WITH NO POPULATION is not specified, SQL
Server performs a full population on the index.

Note
For more information about the interaction of change tracking and WITH NO POPULATION, see
"Remarks," later in this topic.

{ADD | DROP } STATISTICAL_SEMANTICS

Enables or disables statistical semantic indexing for the specified columns. For more
information, see Semantic Search.
START {FULL|INCREMENTAL|UPDATE} POPULATION
Tells SQL Server to begin population of the full-text index of table_name. If a full-text index
population is already in progress, SQL Server returns a warning and does not start a new
population.

FULL
Specifies that every row of the table be retrieved for full-text indexing even if the rows
have already been indexed.

INCREMENTAL
Specifies that only the modified rows since the last population be retrieved for full-text
indexing. INCREMENTAL can be applied only if the table has a column of the type
timestamp. If a table in the full-text catalog does not contain a column of the type
timestamp, the table undergoes a FULL population.

UPDATE
Specifies the processing of all insertions, updates, or deletions since the last time the
change-tracking index was updated. Change-tracking population must be enabled on a
table, but the background update index or the auto change tracking should not be turned
on.

{STOP | PAUSE | RESUME } POPULATION

Stops, or pauses any population in progress; or stops or resumes any paused population.
STOP POPULATION does not stop auto change tracking or background update index. To
stop change tracking, use SET CHANGE_TRACKING OFF.
PAUSE POPULATION and RESUME POPULATION can only be used for full populations. They
are not relevant to other population types because the other populations resume crawls from
where the crawl stopped.

SET STOPLIST { OFF| SYSTEM | stoplist_name }

Changes the full-text stoplist that is associated with the index, if any.
OFF

139
 Specifies that no stoplist be associated with the full-text index.

SYSTEM
Specifies that the default full-text system STOPLIST should be used for this full-text index.

stoplist_name
Specifies the name of the stoplist to be associated with the full-text index.

For more information, see Stopwords and Stoplists.

SET SEARCH PROPERTY LIST { OFF | property_list_name } [ WITH NO POPULATION ]

Changes the search property list that is associated with the index, if any.
OFF
Specifies that no property list be associated with the full-text index. When you turn off the
search property list of a full-text index (ALTER FULLTEXT INDEX … SET SEARCH PROPERTY
LIST OFF), property searching on the base table is no longer possible.
By default, when you turn off an existing search property list, the full-text index
automatically repopulates. If you specify WITH NO POPULATION when you turn off the
search property list, automatic repopulation does not occur. However, we recommend that
you eventually run a full population on this full-text index at your convenience.
Repopulating the full-text index removes the property-specific metadata of each dropped
search property, making the full-text index smaller and more efficient.

property_list_name
Specifies the name of the search property list to be associated with the full-text index.
Adding a search property list to a full-text index requires repopulating the index to index
the search properties that are registered for the associated search property list. If you
specify WITH NO POPULATION when adding the search property list, you will need to run
a population on the index, at an appropriate time.

Important
If the full-text index was previously associated with a different search it must be rebuilt property list
in order to bring the index into a consistent state. The index is truncated immediately and is empty
until the full population runs. For more information about when changing the search property list
causes rebuilding, see "Remarks," later in this topic.

Note
You can associate a given search property list with more than one full-text index in the same
database.
To find the search property lists on the current database
• sys.registered_search_property_lists
For more information about search property lists, see Using Property Lists to Search

140
 for Document Properties.

Remarks
Interactions of Change Tracking and NO POPULATION Parameter
Whether the full-text index is populated depends on whether change-tracking is enabled and
whether WITH NO POPULATION is specified in the ALTER FULLTEXT INDEX statement. The
following table summarizes the result of their interaction.

Change Tracking WITH NO POPULATION Result

Not Enabled Not specified A full population is performed

on the index.
Not Enabled Specified No population of the index
occurs until an ALTER FULLTEXT
INDEX...START POPULATION
statement is issued.
Enabled Specified An error is raised, and the index
is not altered.
Enabled Not specified A full population is performed
on the index.

For more information about populating full-text indexes, see Full-Text Index Population.
Changing the Search Property List Causes Rebuilding the Index
The first time that a full-text index is associated with a search property list, the index must be
repopulated to index property-specific search terms. The existing index data is not truncated.
However, if you associate the full-text index with a different property list, the index is rebuilt.
Rebuilding immediately truncates the full-text index, removing all existing data, and the index
must be repopulated. While the population progresses, full-text queries on the base table search
only on the the table rows that have already been indexed by the population. The repopulated
index data will include metadata from the registered properties of the newly added search
property list.
Scenarios that cause rebuilding include:
• Switching directly to a different search property list (see "Scenario A," later in this section).
• Turning off the search property list and later associating the index with any search property
list (see "Scenario B," later in this section)

Note

141
 For more information about how full-text search works with search property lists, see
Using Search Property Lists to Search for Properties (Full-Text Search). For information
about full populations, see Full-Text Index Population.
Scenario A: Switching Directly to a Different Search Property List
1. A full-text index is created on table_1 with a search property list spl_1:
CREATE FULLTEXT INDEX ON table_1 (column_name) KEY INDEX
unique_key_index
WITH SEARCH PROPERTY LIST=spl_1,
CHANGE_TRACKING OFF, NO POPULATION;
2. A full population is run on the full-text index:
ALTER FULLTEXT INDEX ON table_1 START FULL POPULATION;
3. The full-text index is later associated a different search property list, spl_2, using the
following statement:
ALTER FULLTEXT INDEX ON table_1 SET SEARCH PROPERTY LIST spl_2;
This statement causes a full population, the default behavior. However, before beginning
this population, the Full-Text Engine automatically truncates the index.
Scenario B: Turning Off the Search Property List and Later Associating the Index
with Any Search Property List
1. A full-text index is created on table_1 with a search property list spl_1, followed by an
automatic full population (the default behavior):
CREATE FULLTEXT INDEX ON table_1 (column_name) KEY INDEX
unique_key_index
WITH SEARCH PROPERTY LIST=spl_1;
2. The search property list is turned off, as follows:
ALTER FULLTEXT INDEX ON table_1
SET SEARCH PROPERTY LIST OFF WITH NO POPULATION;
3. The full-text index is once more associated either the same search property list or a different
one.
For example the following statement re-associates the full-text index with the original search
property list, spl_1:
ALTER FULLTEXT INDEX ON table_1 SET SEARCH PROPERTY LIST spl_1;
This statement starts a full population, the default behavior.

Note
The rebuild would also be required for a different search property list, such as spl_2.
Permissions

142
The user must have ALTER permission on the table or indexed view, or be a member of the
sysadmin fixed server role, or the db_ddladmin or db_owner fixed database roles.
If SET STOPLIST is specified, the user must have REFERENCES permission on the stoplist. If SET
SEARCH PROPERTY LIST is specified, the user must have REFERENCES permission on the search
property list. The owner of the specified stoplist or search property list can grant REFERENCES
permission, if the owner has ALTER FULLTEXT CATALOG permissions.

Note
The public is granted REFERENCES permission to the default stoplist that is shipped with
SQL Server.
Examples
A. Setting manual change tracking
The following example sets manual change tracking on the full-text index on the JobCandidate
table of the AdventureWorks database.
USE AdventureWorks;
GO
ALTER FULLTEXT INDEX ON HumanResources.JobCandidate
SET CHANGE_TRACKING MANUAL;
GO

B. Associating a property list with a full-text index

The following example associates the DocumentPropertyList property list with the full-text
index on the Production.Document table of the AdventureWorks database. This ALTER
FULLTEXT INDEX statement starts a full population, which is the default behavior of the SET
SEARCH PROPERTY LIST clause.

Note
For an example that creates the DocumentPropertyList property list, see CREATE
SEARCH PROPERTY LIST (Transact-SQL).
USE AdventureWorks;
GO
ALTER FULLTEXT INDEX ON Production.Document
SET SEARCH PROPERTY LIST DocumentPropertyList;
GO

C. Removing a search property list

The following example removes the DocumentPropertyList property list from the full-text
index on the Production.Document table of the AdventureWorks database. In this example,
there is no hurry for removing the properties from the index, so the WITH NO POPULATION

143
option is specified. However, property-level searching is longer allowed against this full-text
index.
USE AdventureWorks;
GO
ALTER FULLTEXT INDEX ON Production.Document
SET SEARCH PROPERTY LIST OFF WITH NO POPULATION;
GO

D. Starting a full population

The following example starts a full population on the full-text index on the JobCandidate table
of the AdventureWorks database.
USE AdventureWorks;
GO
ALTER FULLTEXT INDEX ON HumanResources.JobCandidate
START FULL POPULATION;
GO

See Also
sys.fulltext_indexes (Transact-SQL)
CREATE FULLTEXT INDEX
DROP FULLTEXT INDEX
Full-Text Search
Full-Text Index Population

ALTER FULLTEXT STOPLIST

Inserts or deletes a stop word in the default full-text stoplist of the current database.

Important
CREATE FULLTEXT STOPLIST is supported only for compatibility level 100. For
compatibility levels 80 and 90, the system stoplist is always assigned to the database.
Transact-SQL Syntax Conventions
Syntax

ALTER FULLTEXT STOPLIST stoplist_name

{
ADD [N] 'stopword' LANGUAGE language_term
| DROP
144
 {
'stopword' LANGUAGE language_term
| ALL LANGUAGE language_term
| ALL
}
;
Arguments
stoplist_name
Is the name of the stoplist being altered. stoplist_name can be a maximum of 128 characters.

'stopword'
Is a string that could be a word with linguistic meaning in the specified language or a token
that does not have a linguistic meaning. stopword is limited to the maximum token length
(64 characters). A stopword can be specified as a Unicode string.

LANGUAGE language_term
Specifies the language to associate with the stopword being added or dropped.
language_term can be specified as a string, integer, or hexadecimal value corresponding to
the locale identifier (LCID) of the language, as follows:

Format Description

String language_term corresponds to the alias

column value in the sys.syslanguages
(Transact-SQL) compatibility view. The
string must be enclosed in single quotation
marks, as in 'language_term'.
Integer language_term is the LCID of the language.
Hexadecimal language_term is 0x followed by the
hexadecimal value of the LCID. The
hexadecimal value must not exceed eight
digits, including leading zeros. If the value
is in double-byte character set (DBCS)
format, SQL Server converts it to Unicode.

ADD 'stopword' LANGUAGE language_term

Adds a stop word to the stoplist for the language specified by LANGUAGE language_term.

145
 If the specified combination of keyword and the LCID value of the language is not unique in
the STOPLIST, an error is returned. If the LCID value does not correspond to a registered
language, an error is generated.

DROP { 'stopword' LANGUAGE language_term | ALL LANGUAGE language_term | ALL }

Drops a stop word from the stop list.
'stopword' LANGUAGE language_term
Drops the specified stop word for the language specified by language_term.

ALL LANGUAGE language_term

Drops all of the stop words for the language specified by language_term.

ALL
Drops all of the stop words in the stoplist.

Remarks
None.
Permissions
To designate a stoplist as the default stoplist of the database requires ALTER DATABASE
permission. To otherwise alter a stoplist requires being the stoplist owner or membership in the
db_owner or db_ddladmin fixed database roles.
Examples
The following example alters a stoplist named CombinedFunctionWordList, adding the word
'en', first for Spanish and then for French.
ALTER FULLTEXT STOPLIST CombinedFunctionWordList ADD 'en' LANGUAGE 'Spanish';
ALTER FULLTEXT STOPLIST CombinedFunctionWordList ADD 'en' LANGUAGE 'French';

See Also
CREATE FULLTEXT STOPLIST (Transact-SQL)
DROP FULLTEXT STOPLIST (Transact-SQL)
Noise Words
sys.fulltext_stoplists (Transact-SQL)
sys.fulltext_stopwords (Transact-SQL)
Configure and Manage Stopwords and Stoplists for Full-Text Search

ALTER FUNCTION
Alters an existing Transact-SQL or CLR function that was previously created by executing the
CREATE FUNCTION statement, without changing permissions and without affecting any
dependent functions, stored procedures, or triggers.

146
 Transact-SQL Syntax Conventions
Syntax

Scalar Functions
ALTER FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS return_data_type
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
[;]

Inline Table-valued Functions

ALTER FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH <function_option> [ ,...n ] ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
[;]

Multistatement Table-valued Functions

ALTER FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type

147
 [ = default ] }
[ ,...n ]
]
)
RETURNS @return_variable TABLE <table_type_definition>
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN
END
[;]

CLR Functions
ALTER FUNCTION [ schema_name. ] function_name
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type
[ = default ] }
[ ,...n ]
)
RETURNS { return_data_type | TABLE <clr_table_type_definition> }
[ WITH <clr_function_option> [ ,...n ] ]
[ AS ] EXTERNAL NAME <method_specifier>
[;]

<method_specifier>::=
assembly_name.class_name.method_name

Function Options
<function_option>::=
{
[ ENCRYPTION ]
| [ SCHEMABINDING ]
| [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
| [ EXECUTE_AS_Clause ]
}

148
<clr_function_option>::=
}
[ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
| [ EXECUTE_AS_Clause ]
}

Table Type Definitions

<table_type_definition>:: =
( { <column_definition> <column_constraint>
| <computed_column_definition> }
[ <table_constraint> ] [ ,...n ]
)

<clr_table_type_definition>:: =
( { column_name data_type } [ ,...n ] )

<column_definition>::=
{
{ column_name data_type }
[ [ DEFAULT constant_expression ]
[ COLLATE collation_name ] | [ ROWGUIDCOL ]
]
| [ IDENTITY [ (seed , increment ) ] ]
[ <column_constraint> [ ...n ] ]
}
<column_constraint>::=
{
[ NULL | NOT NULL ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ , ...n ] )
[ ON { filegroup | "default" } ]
| [ CHECK ( logical_expression ) ] [ ,...n ]

149
}

<computed_column_definition>::=
column_name AS computed_column_expression

<table_constraint>::=
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
( column_name [ ASC | DESC ] [ ,...n ] )
[ WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ , ...n ] )
| [ CHECK ( logical_expression ) ] [ ,...n ]
}

<index_option>::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS ={ ON | OFF }
}
Arguments
schema_name
Is the name of the schema to which the user-defined function belongs.

function_name
Is the user-defined function to be changed.

Note
Parentheses are required after the function name even if a parameter is not specified.

@parameter_name
Is a parameter in the user-defined function. One or more parameters can be declared.
A function can have a maximum of 2,100 parameters. The value of each declared parameter
must be supplied by the user when the function is executed, unless a default for the

150
 parameter is defined.
Specify a parameter name by using an at sign (@) as the first character. The parameter name
must comply with the rules for identifiers. Parameters are local to the function; the same
parameter names can be used in other functions. Parameters can take the place only of
constants; they cannot be used instead of table names, column names, or the names of other
database objects.

Note
ANSI_WARNINGS is not honored when passing parameters in a stored procedure, user-defined
function, or when declaring and setting variables in a batch statement. For example, if a variable is
defined as char(3), and then set to a value larger than three characters, the data is truncated to the
defined size and the INSERT or UPDATE statement succeeds.

[ type_schema_name. ] parameter_data_type
Is the parameter data type and optionally, the schema to which it belongs. For Transact-SQL
functions, all data types, including CLR user-defined types, are allowed except the timestamp
data type. For CLR functions, all data types, including CLR user-defined types, are allowed
except text, ntext, image, and timestamp data types. The nonscalar types cursor and table
cannot be specified as a parameter data type in either Transact-SQL or CLR functions.
If type_schema_name is not specified, the SQL Server 2005 Database Engine looks for the
parameter_data_type in the following order:
• The schema that contains the names of SQL Server system data types.
• The default schema of the current user in the current database.
• The dbo schema in the current database.

[ = default ]
Is a default value for the parameter. If a default value is defined, the function can be executed
without specifying a value for that parameter.

Note
Default parameter values can be specified for CLR functions except for varchar(max) and
varbinary(max) data types.
When a parameter of the function has a default value, the keyword DEFAULT must be
specified when calling the function to retrieve the default value. This behavior is different
from using parameters with default values in stored procedures in which omitting the
parameter also implies the default value.

return_data_type
Is the return value of a scalar user-defined function. For Transact-SQL functions, all data
types, including CLR user-defined types, are allowed except the timestamp data type. For
CLR functions, all data types, including CLR user-defined types, are allowed except text,
ntext, image, and timestamp data types. The nonscalar types cursor and table cannot be
specified as a return data type in either Transact-SQL or CLR functions.

151
function_body
Specifies that a series of Transact-SQL statements, which together do not produce a side
effect such as modifying a table, define the value of the function. function_body is used only
in scalar functions and multistatement table-valued functions.
In scalar functions, function_body is a series of Transact-SQL statements that together
evaluate to a scalar value.
In multistatement table-valued functions, function_body is a series of Transact-SQL
statements that populate a TABLE return variable.

scalar_expression
Specifies that the scalar function returns a scalar value.

TABLE
Specifies that the return value of the table-valued function is a table. Only constants and
@local_variables can be passed to table-valued functions.
In inline table-valued functions, the TABLE return value is defined through a single SELECT
statement. Inline functions do not have associated return variables.
In multistatement table-valued functions, @return_variable is a TABLE variable used to store
and accumulate the rows that should be returned as the value of the function.
@return_variable can be specified only for Transact-SQL functions and not for CLR functions.

select-stmt
Is the single SELECT statement that defines the return value of an inline table-valued
function.

EXTERNAL NAME <method_specifier>assembly_name.class_name.method_name

Specifies the method of an assembly to bind with the function. assembly_name must match
an existing assembly in SQL Server in the current database with visibility on. class_name must
be a valid SQL Server identifier and must exist as a class in the assembly. If the class has a
namespace-qualified name that uses a period (.) to separate namespace parts, the class name
must be delimited by using brackets ([ ]) or quotation marks (" "). method_name must be a
valid SQL Server identifier and must exist as a static method in the specified class.

Note
By default, SQL Server cannot execute CLR code. You can create, modify, and drop database objects
that reference common language runtime modules; however, you cannot execute these references in
SQL Server until you enable the clr enabled option. To enable the option, use sp_configure.

Note
This option is not available in a contained database.

152
<table_type_definition>( { <column_definition> <column_constraint> |
<computed_column_definition> } [ <table_constraint> ] [ ,...n ])
Defines the table data type for a Transact-SQL function. The table declaration includes
column definitions and column or table constraints.

< clr_table_type_definition > ( { column_name data_type } [ ,...n ] )

Defines the table data types for a CLR function. The table declaration includes only column
names and data types.

<function_option>::= and <clr_function_option>::=

Specifies the function will have one or more of the following options.
ENCRYPTION
Indicates that the Database Engine encrypts the catalog view columns that contains the text
of the ALTER FUNCTION statement. Using ENCRYPTION prevents the function from being
published as part of SQL Server replication. ENCRYPTION cannot be specified for CLR
functions.

SCHEMABINDING
Specifies that the function is bound to the database objects that it references. This condition
will prevent changes to the function if other schema bound objects are referencing it.
The binding of the function to the objects it references is removed only when one of the
following actions occurs:
• The function is dropped.
• The function is modified by using the ALTER statement with the SCHEMABINDING
option not specified.
For a list of conditions that must be met before a function can be schema bound, see
EVENTDATA (Transact-SQL).
RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
Specifies the OnNULLCall attribute of a scalar-valued function. If not specified, CALLED ON
NULL INPUT is implied by default. This means that the function body executes even if NULL is
passed as an argument.
If RETURNS NULL ON NULL INPUT is specified in a CLR function, it indicates that SQL Server
can return NULL when any of the arguments it receives is NULL, without actually invoking the
body of the function. If the method specified in <method_specifier> already has a custom
attribute that indicates RETURNS NULL ON NULL INPUT, but the ALTER FUNCTION statement
indicates CALLED ON NULL INPUT, the ALTER FUNCTION statement takes precedence. The
OnNULLCall attribute cannot be specified for CLR table-valued functions.

EXECUTE AS Clause
Specifies the security context under which the user-defined function is executed. Therefore,
you can control which user account SQL Server uses to validate permissions on any database
objects referenced by the function.

153
 Note
EXECUTE AS cannot be specified for inline user-defined functions.
For more information, see EXECUTE AS Clause (Transact-SQL).
< column_definition >::=
Defines the table data type. The table declaration includes column definitions and constraints.
For CLR functions, only column_name and data_type can be specified.
column_name
Is the name of a column in the table. Column names must comply with the rules for
identifiers and must be unique in the table. column_name can consist of 1 through 128
characters.

data_type
Specifies the column data type. For Transact-SQL functions, all data types, including CLR
user-defined types, are allowed except timestamp. For CLR functions, all data types,
including CLR user-defined types, are allowed except text, ntext, image, char, varchar,
varchar(max), and timestamp.The nonscalar type cursor cannot be specified as a column
data type in either Transact-SQL or CLR functions.

DEFAULT constant_expression
Specifies the value provided for the column when a value is not explicitly supplied during an
insert. constant_expression is a constant, NULL, or a system function value. DEFAULT
definitions can be applied to any column except those that have the IDENTITY property.
DEFAULT cannot be specified for CLR table-valued functions.

COLLATE collation_name
Specifies the collation for the column. If not specified, the column is assigned the default
collation of the database. Collation name can be either a Windows collation name or a SQL
collation name. For a list of and more information, see Windows Collation Name and
SQL Collation Name.
The COLLATE clause can be used to change the collations only of columns of the char,
varchar, nchar, and nvarchar data types.
COLLATE cannot be specified for CLR table-valued functions.

ROWGUIDCOL
Indicates that the new column is a row global unique identifier column. Only one
uniqueidentifier column per table can be designated as the ROWGUIDCOL column. The
ROWGUIDCOL property can be assigned only to a uniqueidentifier column.
The ROWGUIDCOL property does not enforce uniqueness of the values stored in the column.
It also does not automatically generate values for new rows inserted into the table. To
generate unique values for each column, use the NEWID function on INSERT statements. A
default value can be specified; however, NEWID cannot be specified as the default.

154
IDENTITY
Indicates that the new column is an identity column. When a new row is added to the table,
SQL Server provides a unique, incremental value for the column. Identity columns are
typically used together with PRIMARY KEY constraints to serve as the unique row identifier
for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint,
decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table.
Bound defaults and DEFAULT constraints cannot be used with an identity column. You must
specify both the seed and increment or neither. If neither is specified, the default is (1,1).
IDENTITY cannot be specified for CLR table-valued functions.
seed
Is the integer value to be assigned to the first row in the table.

increment
Is the integer value to add to the seed value for successive rows in the table.

< column_constraint >::= and < table_constraint>::=

Defines the constraint for a specified column or table. For CLR functions, the only constraint type
allowed is NULL. Named constraints are not allowed.
NULL | NOT NULL
Determines whether null values are allowed in the column. NULL is not strictly a constraint
but can be specified just like NOT NULL. NOT NULL cannot be specified for CLR table-valued
functions.

PRIMARY KEY
Is a constraint that enforces entity integrity for a specified column through a unique index. In
table-valued user-defined functions, the PRIMARY KEY constraint can be created on only one
column per table. PRIMARY KEY cannot be specified for CLR table-valued functions.

UNIQUE
Is a constraint that provides entity integrity for a specified column or columns through a
unique index. A table can have multiple UNIQUE constraints. UNIQUE cannot be specified for
CLR table-valued functions.

CLUSTERED | NONCLUSTERED
Indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE
constraint. PRIMARY KEY constraints use CLUSTERED, and UNIQUE constraints use
NONCLUSTERED.
CLUSTERED can be specified for only one constraint. If CLUSTERED is specified for a UNIQUE
constraint and a PRIMARY KEY constraint is also specified, the PRIMARY KEY uses
NONCLUSTERED.
CLUSTERED and NONCLUSTERED cannot be specified for CLR table-valued functions.

155
CHECK
Is a constraint that enforces domain integrity by limiting the possible values that can be
entered into a column or columns. CHECK constraints cannot be specified for CLR table-
valued functions.
logical_expression
Is a logical expression that returns TRUE or FALSE.

<computed_column_definition>::=
Specifies a computed column. For more information about computed columns, see CREATE
TABLE (Transact-SQL).
column_name
Is the name of the computed column.

computed_column_expression
Is an expression that defines the value of a computed column.

<index_option>::=
Specifies the index options for the PRIMARY KEY or UNIQUE index. For more information about
index options, see CREATE INDEX (Transact-SQL).
PAD_INDEX = { ON | OFF }
Specifies index padding. The default is OFF.

FILLFACTOR = fillfactor
Specifies a percentage that indicates how full the Database Engine should make the leaf level
of each index page during index creation or change. fillfactor must be an integer value from
1 to 100. The default is 0.

IGNORE_DUP_KEY = { ON | OFF }
Specifies the error response when an insert operation attempts to insert duplicate key values
into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the
index is created or rebuilt. The default is OFF.

STATISTICS_NORECOMPUTE = { ON | OFF }
Specifies whether distribution statistics are recomputed. The default is OFF.

ALLOW_ROW_LOCKS = { ON | OFF }
Specifies whether row locks are allowed. The default is ON.

ALLOW_PAGE_LOCKS = { ON | OFF }
Specifies whether page locks are allowed. The default is ON.

Remarks
ALTER FUNCTION cannot be used to change a scalar-valued function to a table-valued function,
or vice versa. Also, ALTER FUNCTION cannot be used to change an inline function to a

156
multistatement function, or vice versa. ALTER FUNCTION cannot be used to change a Transact-
SQL function to a CLR function or vice-versa.
The following Service Broker statements cannot be included in the definition of a Transact-
SQL user-defined function:
• BEGIN DIALOG CONVERSATION
• END CONVERSATION
• GET CONVERSATION GROUP
• MOVE CONVERSATION
• RECEIVE
• SEND
Permissions
Requires ALTER permission on the function or on the schema. If the function specifies a user-
defined type, requires EXECUTE permission on the type.
See Also
CREATE FUNCTION (Transact-SQL)
DROP FUNCTION (Transact-SQL)
Making Schema Changes on Publication Databases
EVENTDATA (Transact-SQL)

ALTER INDEX
Modifies an existing table or view index (relational or XML) by disabling, rebuilding, or
reorganizing the index; or by setting options on the index.
Transact-SQL Syntax Conventions
Syntax

ALTER INDEX { index_name | ALL }

ON <object>
{ REBUILD
[ [PARTITION = ALL]
[ WITH ( <rebuild_index_option> [ ,...n ] ) ]
| [ PARTITION = partition_number
[ WITH ( <single_partition_rebuild_index_option>
[ ,...n ] )
]
]
157
 ]
| DISABLE
| REORGANIZE
[ PARTITION = partition_number ]
[ WITH ( LOB_COMPACTION = { ON | OFF } ) ]
| SET ( <set_index_option> [ ,...n ] )
}
[;]

<object> ::=
{
[ database_name. [ schema_name ] . | schema_name. ]
table_or_view_name
}

<rebuild_index_option > ::=

{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| ONLINE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW | PAGE }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ]
}
<range> ::=
<partition_number_expression> TO <partition_number_expression>
}

<single_partition_rebuild_index_option> ::=

158
{
SORT_IN_TEMPDB = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
| DATA_COMPRESSION = { NONE | ROW | PAGE } }
}

<set_index_option>::=
{
ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
}
Arguments
index_name
Is the name of the index. Index names must be unique within a table or view but do not have
to be unique within a database. Index names must follow the rules of identifiers.

ALL
Specifies all indexes associated with the table or view regardless of the index type. Specifying
ALL causes the statement to fail if one or more indexes are in an offline or read-only
filegroup or the specified operation is not allowed on one or more index types. The following
table lists the index operations and disallowed index types.

Specifying ALL with this operation Fails if the table has one or more

REBUILD WITH ONLINE = ON XML index

Spatial index
Large object data type columns: image,
text, ntext, varchar(max), nvarchar(max),
varbinary(max), and xml
REBUILD PARTITION = partition_number Nonpartitioned index, XML index, spatial
index, or disabled index
REORGANIZE Indexes with ALLOW_PAGE_LOCKS set to
OFF
REORGANIZE PARTITION = Nonpartitioned index, XML index, spatial
partition_number index, or disabled index

159
 IGNORE_DUP_KEY = ON Spatial index
XML index
ONLINE = ON Spatial index
XML index

If ALL is specified with PARTITION = partition_number, all indexes must be aligned. This
means that they are partitioned based on equivalent partition functions. Using ALL with
PARTITION causes all index partitions with the same partition_number to be rebuilt or
reorganized. For more information about partitioned indexes, see Partitioned Tables and
Indexes.
database_name
Is the name of the database.

schema_name
Is the name of the schema to which the table or view belongs.

table_or_view_name
Is the name of the table or view associated with the index. To display a report of the indexes
on an object, use the sys.indexes catalog view.

REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]

Specifies the index will be rebuilt using the same columns, index type, uniqueness attribute,
and sort order. This clause is equivalent to DBCC DBREINDEX. REBUILD enables a
disabled index. Rebuilding a clustered index does not rebuild associated nonclustered
indexes unless the keyword ALL is specified. If index options are not specified, the existing
index option values stored in sys.indexes are applied. For any index option whose value is
not stored in sys.indexes, the default indicated in the argument definition of the option
applies.
When you rebuild an XML index or a spatial index, the options ONLINE = ON and
IGNORE_DUP_KEY = ON are not valid.
If ALL is specified and the underlying table is a heap, the rebuild operation has no effect on
the table. Any nonclustered indexes associated with the table are rebuilt.
The rebuild operation can be minimally logged if the database recovery model is set to either
bulk-logged or simple.

Note
When you rebuild a primary XML index, the underlying user table is unavailable for the duration of the
index operation.

PARTITION
Specifies that only one partition of an index will be rebuilt or reorganized. PARTITION cannot

160
 be specified if index_name is not a partitioned index.
PARTITION = ALL rebuilds all partitions.

Warning
Creating and rebuilding nonaligned indexes on a table with more than 1,000 partitions is possible, but
is not supported. Doing so may cause degraded performance or excessive memory consumption
during these operations. We recommend using only aligned indexes when the number of partitions
exceed 1,000.

partition_number
Is the partition number of a partitioned index that is to be rebuilt or reorganized.
partition_number is a constant expression that can reference variables. These include user-
defined type variables or functions and user-defined functions, but cannot reference a
Transact-SQL statement. partition_number must exist or the statement fails.

WITH (<single_partition_rebuild_index_option>)
SORT_IN_TEMPDB, MAXDOP, and DATA_COMPRESSION are the options that can be specified
when you rebuild a single partition (PARTITION = n). XML indexes cannot be specified in a
single partition rebuild operation.
Rebuilding a partitioned index cannot be performed online. The entire table is locked during
this operation.

DISABLE
Marks the index as disabled and unavailable for use by the Database Engine. Any index can
be disabled. The index definition of a disabled index remains in the system catalog with no
underlying index data. Disabling a clustered index prevents user access to the underlying
table data. To enable an index, use ALTER INDEX REBUILD or CREATE INDEX WITH
DROP_EXISTING. For more information, see Disable Indexes and Constraints and
Enable Indexes and Constraints.
REORGANIZE
Specifies the index leaf level will be reorganized. ALTER INDEX REORGANIZE statement is
always performed online. This means long-term blocking table locks are not held and queries
or updates to the underlying table can continue during the ALTER INDEX REORGANIZE
transaction. REORGANIZE cannot be specified for a disabled index or an index with
ALLOW_PAGE_LOCKS set to OFF.

WITH ( LOB_COMPACTION = { ON | OFF } )

Specifies that all pages that contain large object (LOB) data are compacted. The LOB data
types are image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml.
Compacting this data can improve disk space use. The default is ON.
ON
All pages that contain large object data are compacted.
Reorganizing a specified clustered index compacts all LOB columns that are contained in

161
 the clustered index. Reorganizing a nonclustered index compacts all LOB columns that are
nonkey (included) columns in the index.
When ALL is specified, all indexes that are associated with the specified table or view are
reorganized, and all LOB columns that are associated with the clustered index, underlying
table, or nonclustered index with included columns are compacted.

OFF
Pages that contain large object data are not compacted.
OFF has no effect on a heap.

The LOB_COMPACTION clause is ignored if LOB columns are not present.

SET ( <set_index option> [ ,... n] )

Specifies index options without rebuilding or reorganizing the index. SET cannot be specified
for a disabled index.

PAD_INDEX = { ON | OFF }
Specifies index padding. The default is OFF.
ON
The percentage of free space that is specified by FILLFACTOR is applied to the
intermediate-level pages of the index. If FILLFACTOR is not specified at the same time
PAD_INDEX is set to ON, the fill factor value stored in sys.indexes is used.

OFF or fillfactor is not specified

The intermediate-level pages are filled to near capacity. This leaves sufficient space for at
least one row of the maximum size that the index can have, based on the set of keys on
the intermediate pages.

For more information, see CREATE INDEX (Transact-SQL).

FILLFACTOR = fillfactor
Specifies a percentage that indicates how full the Database Engine should make the leaf level
of each index page during index creation or alteration. fillfactor must be an integer value
from 1 to 100. The default is 0.

Note
Fill factor values 0 and 100 are the same in all respects.
An explicit FILLFACTOR setting applies only when the index is first created or rebuilt. The
Database Engine does not dynamically keep the specified percentage of empty space in the
pages. For more information, see CREATE INDEX.
To view the fill factor setting, use sys.indexes.

Important
Creating or altering a clustered index with a FILLFACTOR value affects the amount of storage space
the data occupies, because the Database Engine redistributes the data when it creates the clustered

162
 index.

SORT_IN_TEMPDB = { ON | OFF }
Specifies whether to store the sort results in tempdb. The default is OFF.
ON
The intermediate sort results that are used to build the index are stored in tempdb. If
tempdb is on a different set of disks than the user database, this may reduce the time
needed to create an index. However, this increases the amount of disk space that is used
during the index build.

OFF
The intermediate sort results are stored in the same database as the index.

If a sort operation is not required, or if the sort can be performed in memory, the
SORT_IN_TEMPDB option is ignored.
For more information, see tempdb and Index Creation.
IGNORE_DUP_KEY = { ON | OFF }
Specifies the error response when an insert operation attempts to insert duplicate key values
into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the
index is created or rebuilt. The default is OFF.
ON
A warning message will occur when duplicate key values are inserted into a unique index.
Only the rows violating the uniqueness constraint will fail.

OFF
An error message will occur when duplicate key values are inserted into a unique index.
The entire INSERT operation will be rolled back.

IGNORE_DUP_KEY cannot be set to ON for indexes created on a view, non-unique indexes,

XML indexes, spatial indexes, and filtered indexes.
To view IGNORE_DUP_KEY, use sys.indexes.
In backward compatible syntax, WITH IGNORE_DUP_KEY is equivalent to WITH
IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | OFF }
Specifies whether distribution statistics are recomputed. The default is OFF.
ON
Out-of-date statistics are not automatically recomputed.

OFF
Automatic statistics updating are enabled.

To restore automatic statistics updating, set the STATISTICS_NORECOMPUTE to OFF, or

execute UPDATE STATISTICS without the NORECOMPUTE clause.

163
 Important
Disabling automatic recomputation of distribution statistics may prevent the query optimizer from
picking optimal execution plans for queries that involve the table.

ONLINE = { ON | OFF }
Specifies whether underlying tables and associated indexes are available for queries and data
modification during the index operation. The default is OFF.
For an XML index or spatial index, only ONLINE = OFF is supported, and if ONLINE is set to
ON an error is raised.

Note
Online index operations are not available in every edition of Microsoft SQL Server. For a list of features
that are supported by the editions of SQL Server, see Features Supported by the Editions of
SQL Server 2012.
ON
Long-term table locks are not held for the duration of the index operation. During the
main phase of the index operation, only an Intent Share (IS) lock is held on the source
table. This allows queries or updates to the underlying table and indexes to continue. At
the start of the operation, a Shared (S) lock is very briefly held on the source object. At the
end of the operation, an S lock is very briefly held on the source if a nonclustered index is
being created, or an SCH-M (Schema Modification) lock is acquired when a clustered index
is created or dropped online, or when a clustered or nonclustered index is being rebuilt.
ONLINE cannot be set to ON when an index is being created on a local temporary table.

OFF
Table locks are applied for the duration of the index operation. An offline index operation
that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a
nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This
prevents all user access to the underlying table for the duration of the operation. An offline
index operation that creates a nonclustered index acquires a Shared (S) lock on the table.
This prevents updates to the underlying table but allows read operations, such as SELECT
statements.

For more information, see How Online Index Operations Work.

Indexes, including indexes on global temp tables, can be rebuilt online with the following
exceptions:
• XML indexes
• Indexes on local temp tables
• A subset of a partitioned index (An entire partitioned index can be rebuilt online.)
• Clustered indexes if the underlying table contains LOB data types
• Nonclustered indexes that are defined with the image, ntext, and text data type
columns

164
 Nonclustered indexes can be rebuilt online if the table contains LOB data types but none of
these columns are used in the index definition as either key or nonkey columns.

ALLOW_ROW_LOCKS = { ON | OFF }
Specifies whether row locks are allowed. The default is ON.
ON
Row locks are allowed when accessing the index. The Database Engine determines when
row locks are used.

OFF
Row locks are not used.

ALLOW_PAGE_LOCKS = { ON | OFF }
Specifies whether page locks are allowed. The default is ON.
ON
Page locks are allowed when you access the index. The Database Engine determines when
page locks are used.

OFF
Page locks are not used.

Note
An index cannot be reorganized when ALLOW_PAGE_LOCKS is set to OFF.

MAXDOP = max_degree_of_parallelism
Overrides the max degree of parallelism configuration option for the duration of the index
operation. For more information, see Configure the max degree of parallelism
Server Configuration Option. Use MAXDOP to limit the number of processors used in a
parallel plan execution. The maximum is 64 processors.

Important
Although the MAXDOP option is syntactically supported for all XML indexes, for a spatial index or a
primary XML index, ALTER INDEX currently uses only a single processor.
max_degree_of_parallelism can be:
1
Suppresses parallel plan generation.

>1
Restricts the maximum number of processors used in a parallel index operation to the
specified number.

0 (default)
Uses the actual number of processors or fewer based on the current system workload.

For more information, see Configuring Parallel Index Operations.

165
 Note
Parallel index operations are not available in every edition of Microsoft SQL Server. For a list of
features that are supported by the editions of SQL Server, see Features Supported by the
Editions of SQL Server 2012.
DATA_COMPRESSION
Specifies the data compression option for the specified index, partition number, or range of
partitions. The options are as follows:
NONE
Index or specified partitions are not compressed.

ROW
Index or specified partitions are compressed by using row compression.

PAGE
Index or specified partitions are compressed by using page compression.

For more information about compression, see Creating Compressed Tables and
Indexes.
ON PARTITIONS ( { <partition_number_expression> | <range> } [,...n] )
Specifies the partitions to which the DATA_COMPRESSION setting applies. If the index is not
partitioned, the ON PARTITIONS argument will generate an error. If the ON PARTITIONS
clause is not provided, the DATA_COMPRESSION option applies to all partitions of a
partitioned index.
<partition_number_expression> can be specified in the following ways:
• Provide the number for a partition, for example: ON PARTITIONS (2).
• Provide the partition numbers for several individual partitions separated by commas, for
example: ON PARTITIONS (1, 5).
• Provide both ranges and individual partitions: ON PARTITIONS (2, 4, 6 TO 8).
<range> can be specified as partition numbers separated by the word TO, for example: ON
PARTITIONS (6 TO 8).
To set different types of data compression for different partitions, specify the
DATA_COMPRESSION option more than once, for example:
REBUILD WITH
(
DATA_COMPRESSION = NONE ON PARTITIONS (1),
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
)