Database Properties (Options Page)

Use this page to view or modify options for the selected database. For more information about the options available on this page, see Setting Database Options.

  • Collation
    Specify the collation of the database by selecting from the list. For more information, see Working with Collations.
  • Recovery model
    Specify one of the following models for recovering the database: Full, Bulk-Logged, or Simple. For more information about recovery models, see Overview of the Recovery Models.
  • Compatibility level
    Specify the latest version of Microsoft SQL Server that the database supports. Possible values are SQL Server 2005, SQL Server 2000, and SQL Server 7.0. For more information, see sp_dbcmptlevel (Transact-SQL).

Automatic

  • Auto Close
    Specify whether the database shuts down cleanly and frees resources after the last user exits. Possible values are True and False. When True, the database is shut down cleanly and its resources are freed after the last user logs off.
  • Auto Create Statistics
    Specify whether the database automatically creates missing optimization statistics. Possible values are True and False. When True, any missing statistics needed by a query for optimization are automatically built during optimization. For more information, see CREATE STATISTICS (Transact-SQL).
  • Auto Shrink
    Specify whether the database files are available for periodic shrinking. Possible values are True and False. For more information, see AutoShrink Property.
  • Auto Update Statistics
    Specify whether the database automatically updates out-of-date optimization statistics. Possible values are True and False. When True, any out-of-date statistics needed by a query for optimization are automatically built during optimization. For more information, see CREATE STATISTICS (Transact-SQL).
  • Auto Update Statistics Asynchronously
    When True, queries that initiate an automatic update of out-of-date statistics will not wait for the statistics to be updated before compiling. Subsequent queries will use the updated statistics when they are available.

    When False, queries that initiate an automatic update of out-of-date statistics, wait until the updated statistics can be used in the query optimization plan.

    Setting this option to True has no effect unless Auto Update Statistics is also set to True.

Cursor

  • Close Cursor on Commit Enabled
    Specify whether cursors close after the transaction opening the cursor has committed. Possible values are True and False. When True, any cursors that are open when a transaction is committed or rolled back are closed. When False, such cursors remain open when a transaction is committed. When False, rolling back a transaction closes any cursors except those defined as INSENSITIVE or STATIC. For more information, see SET CURSOR_CLOSE_ON_COMMIT (Transact-SQL).
  • Default Cursor
    Specify default cursor behavior. When True, cursor declarations default to LOCAL. When False, Transact-SQL cursors default to GLOBAL. For more information, see Scope of Transact-SQL Cursor Names.

Miscellaneous

  • ANSI NULLS Enabled
    Specify the behavior of the Equals (=) and Not Equal To (<>) comparison operators when used with null values. Possible values are True (on) and False (off). When True, all comparisons to a null value evaluate to UNKNOWN. When False, comparisons of non-UNICODE values to a null value evaluate to True if both values are NULL. For more information, see SET ANSI_NULLS (Transact-SQL).
  • ANSI Padding Enabled
    Specify whether ANSI padding is on or off. Permissible values are True (on) and False (off). For more information, see SET ANSI_PADDING (Transact-SQL).
  • ANSI Warnings Enabled
    Specify SQL-92 standard behavior for several error conditions. When True, a warning message is generated if null values appear in aggregate functions (such as SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT). When False, no warning is issued. For more information, see SET ANSI_WARNINGS (Transact-SQL).
  • Arithmetic Abort Enabled
    Specify whether the database option for arithmetic abort is enabled or not. Possible values are True and False. When True, an overflow or divide-by-zero error causes the query or batch to terminate. If the error occurs in a transaction, the transaction is rolled back. When False, a warning message is displayed, but the query, batch, or transaction continues as if no error occurred. For more information, see SET ARITHABORT (Transact-SQL).
  • Concatenate Null Yields Null
    Specify the behavior when null values are concatenated. When the property value is True, string + NULL returns NULL. When False, the result is string. For more information, see SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).
  • Cross-database Ownership Chaining Enabled
    This read-only value indicates if cross-database ownership chaining has been enabled. When True, the database can be the source or target of a cross-database ownership chain. Use the ALTER DATABASE statement to set this property.
  • Date Correlation Optimization Enabled
    When True, SQL Server maintains correlation statistics between any two tables in the database that are linked by a FOREIGN KEY constraint and have datetime columns.

    When False, correlation statistics are not maintained. For more information, see Optimizing Queries That Access Correlated datetime Columns.

  • Numeric Round-Abort
    Specify how the database handles rounding errors. Possible values are True and False. When True, an error is generated when loss of precision occurs in an expression. When False, losses of precision do not generate error messages, and the result is rounded to the precision of the column or variable storing the result. For more information, see SET NUMERIC_ROUNDABORT (Transact-SQL).
  • Parameterization
    When SIMPLE, queries are parameterized based on the default behavior of the database. When FORCED, SQL Server parameterizes all queries in the database. For more information, see Simple Parameterization and Forced Parameterization.
  • Quoted Identifiers Enabled
    Specify whether SQL Server keywords can be used as identifiers (an object or variable name) if enclosed in quotes. Possible values are True and False. For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).
  • Recursive Triggers Enabled
    Specify whether triggers can be fired by other triggers. Possible values are True and False. When set to True, this enables recursive firing of triggers. When set to False, only direct recursion is prevented. To disable indirect recursion, set the nested triggers server option to 0 using sp_configure. For more information, see Using Nested Triggers.
  • Trustworthy
    When displaying True, this read-only option indicates that SQL Server allows access to resources outside the database under an impersonation context established within the database. Impersonation contexts can be established within the database using the EXECUTE AS user statement or the EXECUTE AS clause on database modules.

    To have access, the owner of the database also needs to have the AUTHENTICATE SERVER permission at the server level.

    This property also allows the creation and execution of unsafe and external access assemblies within the database. In addition to setting this property to True, the owner of the database needs the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission at the server level.

    By default, all user databases and all system databases (with the exception of msdb) have this property set to False. The value cannot be changed for the model and tempdb databases.

    TRUSTWORTHY is set to False whenever a database is attached to the server.

    The recommended approach for accessing resources outside the database under an impersonation context is to use certificates and signatures as apposed to the Trustworthy option.

    Use the ALTER DATABASE statement to set this property.

  • VarDecimal Storage Format Enabled
    When True, this database is enabled for the vardecimal storage format. Vardecimal storage format cannot be disabled while any tables in the database are using it. For information about vardecimal storage format, see Storing Decimal Data As Variable Length. This feature requires SQL Server 2005 Service Pack 2. Vardecimal storage format is available only in SQL Server 2005 Enterprise, Developer and Evaluation editions. This option uses sp_db_vardecimal_storage_format.

Recovery

  • Page Verify
    Specify the option used to discover and report incomplete I/O transactions caused by disk I/O errors. Possible values are None, TornPageDetection, and Checksum. For more information, see Understanding and Managing the suspect_pages Table.

State

  • Database Read Only
    Specify whether the database is read only. Possible values are True and False. When True, users can only read data in the database. Users cannot modify the data or database objects; however, the database itself can be deleted using the DROP DATABASE statement. The database cannot be in use when a new value for the Database Read Only option is specified. The master database is the exception, and only the system administrator can use master while the option is being set.
  • Database State
    View the current state of the database. It is not editable. For more information about Database State, see Database States.
  • Restrict Access
    Specify which users may access the database. Possible values are:

    • Multiple
      The normal state for a production database, allows multiple users to access the database at once.
    • Single
      Used for maintenance actions, only one user is allowed to access the database at once.
    • Restricted
      Only members of the db_owner, dbcreator, or sysadmin roles can use the database.

See Also

Other Resources

Modifying a Database
ALTER DATABASE (Transact-SQL)
CREATE DATABASE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006

New content:
  • Added definition for the VarDecimal Storage Format Enabled option that supports the vardecimal storage format.

5 December 2005

New content:
  • Added definitions for Auto Update Statistics Asynchronously, Cross-database Ownership Chaining Enabled, Date Correlation Optimization Enabled, Parameterization, and Trustworthy.