Database Options in SQL Server 2008


Many database options are available, but the most important are these:


Automatic options

Auto Close (AUTO_CLOSE) controls if the database are closed after the last user disconnects.

Auto Shrink 9AUTO_SHRINK) by default, auto shrinking is turned off. This option controls if database files are automatically shrunk in 60-min intervals and space released when more than 20% of the database contains unused space.

Auto Create Statistics (AUTO_CREATE_STATISTICS) by default, this option is turned on, and any missing statistics are built during query optimization.

Auto Update Statistics (AUTO_UPDATE_STATISTICS) any out-of-date statistics are rebuilt during query optimization.

Auto Update Statistics Asynchronously (AUTO_UPDATE_STATISTICS_ASYNC) when it is on, queries will not wait for statistics to be updated before compiling.

Cursor Options

Close Cursor On Commit Enabled (CURSOR_CLOSE_ON_COMMIT) when this option is TRUE, an open cursor automatically when a transaction is committed or rolled back.

Default Cursor (CURSOR_DEFAULT) the possible settings are GLOBAL and LOCAL. Default is GLOBAL, meaning that a cursor not explicitly declared as local may be referenced in any SP (stored procedure), trigger.

Miscellaneous options

Date Correlation Optimization Enabled (DATE_CORRELATION_OPTIMIZATION) when this is set to TRUE, SQL Server maintains correlation statistics between any two tables in the database that are linked by FOREIGN KEY. By default, this is set to FALSE.

Parameterization (PARAMETERIZATION) the default is set to SIMPLE. This determines if queries are parameterized based on behavior of previous versions of SQL Server, or if forced parameterization is used.

ANSI NULL Default (ANSI_NULL_DEFAULT) if this is set to TRUE, columns that don’t specify nullability will allow null values.

ANSI NULLS Enabled (ANSI_NULLS) if this option is set to TRUE, comparisons to null are allowed to match values.

ANSI Padding Enabled (ANSI_PADDING) by default, is set to FALSE. This option determines if trailing blanks will be padded to the full length of varchar and nvarchar columns.

ANSI Warnings Enabled (ANSI_WARNINGS) by default is set to FALSE. Determines if additional warnings are displayed.

Arithmetic Abort Enabled (ARITHABORT) when is set to TRUE, any query that results in an overflow error is terminated, and an error message is displayed.

Concatenate Null Yields Null (CONCAT_NULL_YIELDS_NULL) by default, is set to FALSE, and the NULL values is treated as an empty string.

Cross-Database Ownership Chaining Enabled (DB_CHAINING) controls if the database can be the source or target of a cross-database ownership chain.

Numeric Round-Abort (NUMERIC_ROUNDABORT) when is set to TRUE, any expression that results in a loss of precision generates an error.

Quoted Identifiers Enabled (QUOTED_IDENTIFIER) when true, items in double quotes is are treated as object identifiers.

Recursive Triggers Enabled (RECURSIVE_TRIGGERS) controls if a trigger defined on a table that modifies itself can cause the same trigger to fire again.

Trustworthy (TRUSTWORTHY) determines if stored procedures and user-defined functions can use an impersonation context to access resources outside of the database.

Recovery options

CHECKSUM are used to find incomplete page writes caused by disk I/O errors.

State options

Database Read-Only determines if the database is considered read only.

Encryption Enabled determines if TDE is enabled.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at

Up ↑

%d bloggers like this: