Online Documentation for SQL Administrator for SQL Server

Options

Recovery Model

SQL Server recovery models are used for backup and restore operations. A recovery model is a database property that controls the basic behavior of backup and restore operations for a database: how transactions are logged, whether the transaction log requires backup, what kinds of restore operations are available, etc.

 

Full

When this recovery model is set, all transactions are fully logged and all the transaction log records are retained until after they are backed up. In the Enterprise Edition of SQL Server, the full recovery model allows a database to be recovered to the point of failure, assuming that the tail of the log has been backed up after the failure.

 

Bulk-logged

When this recovery model is set, most bulk operations are minimally logged (e.g. index creation and bulk loads), and other transactions are fully logged.

 

Simple

When this recovery model is set, most transactions are minimally logged: only the information required to ensure database consistency after a system crash or after restoring a data backup.

 

Collation

Use the drop-down list to specify collation for the database being created. By default, the server collation is used.

 

The Compatibility Level option group controls the behavior of an instance of Microsoft® SQL Server™, setting behavior to match a specified version of SQL Server.

 

 

 

Automatic options

Auto Close

When this option is enabled, the database is closed and shut down when the last user of the database exits and all processes in the database complete, thereby freeing any resources. The database reopens automatically when a user tries to use the database again. When disabled, the database remains open even if no users are currently using the database.

 

Auto Create Statistics

When this option is enabled, statistics are automatically created on columns used in a predicate. Adding statistics improves query performance because the SQL Server query optimizer can better determine how to evaluate a query. If the statistics are not used, SQL Server automatically deletes them. When disabled, statistics are not automatically created; instead, statistics can be manually created.

 

Create Incremental Statistics

Specify whether to use the incremental option when per partition statistics are created.

 

Auto Shrink

When this option is enabled, the database files are candidates for periodic shrinking. Both data files and log files can be shrunk automatically by SQL Server. 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 disabled, the database files are not automatically shrunk during periodic checks for unused space.

 

Auto Update Statistics

If this option is checked then any missing statistics that is required on a query for its optimization will be created automatically during the query optimization process.

If the option is not checked then statistics must be created manually.

 

Auto Update Statistics Asynchronously

When this option is enabled, existing statistics are automatically updated when the statistics become out-of-date because the data in the tables have been changed. When disabled, existing statistics are not automatically updated; instead, statistics can be manually updated.

 

Containment

Status

When this option is set to Partial - the database is contained database. In a contained databases, some settings usually configured at the server level can be configured at the database level.

 

Default Full-Text Language

Specifies a default language for full-text indexed columns. Linguistic analysis of full-text indexed data is dependent on the language of the data. The default value of this option is the language of the server.

 

Default Language

The default language for all new contained database users, unless otherwise specified.

 

Nested Triggers

Allows triggers to fire other triggers.

 

Transform Noise Words

Suppress an error message if noise words, that is stopwords, cause a Boolean operation on a full-text query to return zero rows.

 

Two Digit Year Cut-off

Indicates the highest year number that can be entered as a two-digit year. Possible values are from 1753 to 9999. Default value is 2049.

 

Cursor options

Close Cursor on Commit

When this option is enabled, any open cursors are closed automatically (in compliance with SQL-92) when a transaction is committed. When disabled, cursors remain open across transaction boundaries, closing only when the connection is closed or when they are explicitly closed.

 

Default Cursor Scope

LOCAL

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.

GLOBAL

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.

 

Database State

Database State

ONLINE

The database is open and available for use (enabled by default).

OFFLINE

The database is closed and shutdown, and marked offline. The database cannot be modified while it is in the offline state.

EMERGENCY

The database is marked READ_ONLY, logging is disabled, and only members of the sysadmin fixed server role can access the database.

 

Read-Only

When this option is enabled, the database is in read-only mode. Users can retrieve data from the database, but cannot modify the data, because a read-only database does not allow data modifications.

 

User Access

MULTI_USER

Allows all users with the appropriate permissions to connect to the database (enabled by default).

RESTRICTED_USER

Allows only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but it does not limit their number.

SINGLE_USER

Allows one user at a time to connect to the database. All other user connections are broken.

 

 

External Access Options

Cross-Database Ownership Chaining

When this option is checked, DB_CHAINING is enabled and the database can be the source or target of a cross-database ownership chain. When the option is unchecked, the database cannot participate in cross-database ownership chaining. You can change this option for user databases, but not for system databases.

 

Trustworthy

When this option is on, database modules (for example, views, user-defined functions, or stored procedures) that use an impersonation context are allowed to access resources outside of the database.

 

FILESTREAM

Directory name

The directory name for the FILESTREAM data associated with the selected database.

 

Non-transactional Access Level

Specify one of the following options for non-transactional access through the file system to FILESTREAM data stored in FileTables: OFF, READ_ONLY, or FULL.

 

Service Broker Options

Enable Service Broker

Specifies that Service Broker is enabled for the specified database. The IS_BROKER_ENABLED flag is set to TRUE in the sys.databases catalog view and message delivery is started

 

Honor Broker Priority

Setting honor broker priority allows non-default priority settings to be honored for sending messages between computers or SQL Server instances, or in error cases.

 

Snapshot Isolation Level Options

Enable Snapshot Isolation

If this option is enabled, SQL Server will execute snapshot transactions against the specified database when transactions are explicitly set to the SNAPSHOT transaction isolation level.

 

Elevate Isolation to Snapshot on Memory-Optimized Tables

If this option is enabled then the minimum isolation level for accessing memory-optimized tables is SNAPSHOT. Otherwise, the isolation level is not elevated.

 

Read-Commited Snapshot

If this option is enabled, transactions setting the read-committed isolation level use row versioning instead of locking.

 

SQL Options

ANSI NULL Default

When this option is enabled, all user-defined data types or columns that are not explicitly defined as NOT NULL during a CREATE TABLE or ALTER TABLE statement default to allowing null values. Columns that are defined with constraints follow constraint rules regardless of this option.

 

ANSI Nulls

When this option is enabled, all comparisons to a null value evaluate to NULL (unknown). When disabled, comparisons of non-Unicode values to a null value evaluate to TRUE if both values are NULL.

 

ANSI Padding

When this option is enabled, 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. When disabled, the trailing blanks (for varchar or nvarchar) and zeros (for varbinary) are trimmed. This option affects only the definition of new columns.

 

ANSI Warnings

When this option is enabled, errors or warnings are issued when conditions such as "divide by zero" occur or null values appear in aggregate functions. When disabled, no warnings are raised when null values appear in aggregate functions, and null values are returned when conditions such as "divide by zero" occur.

 

Arithmetic Abort

Check this option to terminates a query when an overflow or divide-by-zero error occurs during query execution.

If this option is not checked then a warning message about an occured mistake appears, but a query or transaction continue to execute as if there were no errors.

 

Concatenate Null Yields Null

Check this option to returns NULL when concatenating a NULL value with a string.

 

Numeric Round-Abort

Check this option to generate an error when a loss of precision occurs in an expression.

If this option is not checked then a loss of precision does not cause an error,

 

Quoted Identifiers

Differentiates between single and double quotation marks when evaluating an expression.

 

Recursive Triggers

When this option is enabled, triggers are allowed to fire recursively. When disabled, triggers cannot be fired recursively.

 

Miscellaneous Options

Date Correlation Optimization

If this option is enabled, SQL Server maintains correlation statistics between any two tables in the database that have DATETIME columns and are linked by a one-column foreign key constraint.

 

Database Encryption

Enable the option to use encryption. Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory.

Note: This option is available only for SQL Server 2008 databases and higher.

 

Delayed durability

Controls whether transactions commit fully durable or delayed durable. When it is set to DISABLED, all transactions are fully durable. Any durability options set in an atomic block or commit statement are ignored. If it is set to ALLOWED, transactions are either fully durable or delayed durable, depending upon the durability option set in the atomic block or commit statement. If it is set to FORCED, all transactions are delayed durable. Any durability options set in an atomic block or commit statement are ignored.

 

Enable Change Data Capture

Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.

 

Page Verify

CHECKSUM

A checksum is taken over the contents of the entire page and stored in the page header when a page is written to disk. When a page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header.

TORN_PAGE_DETECTION

A bit is reversed for each 512-byte sector in the 8-kilobyte (KB) database page when the page is written to disk. If a bit is in the wrong state when the page is later read, the page was written incorrectly; a torn page is detected., хранимые в заголовке страницы, сравниваются с действительными сведениями о секторах страницы.

NONE

PAGE_VERIFY is set to OFF and future data page writes will not contain a CHECKSUM or TORN_PAGE_DETECTION bit and the page will not be verified at read time.

 

Parameterization

Simple

Queries are parameterized on the basis of the default behavior of the database. .

Forced

SQL Server parameterizes all queries in the database.

 

Target Recovery Time, sec

The maximum bound on the time (in seconds), to recover the specified database in the event of a crash.