Physical Modeling › SQL Server 2008 Property Editors › Define a Database in SQL Server 2008

Define a Database in SQL Server 2008

The SQL Server Database Editor is used to create new databases, define the files used to store an existing database or create a database snapshot, and define database properties in a SQL Server 2008 physical model.

To define a database in SQL Server 2008

1. Click Target - SQL Server, Databases on the Model menu.

   The SQL Server Database Editor opens.

2. Select the database in the Navigation Grid that you want to define and work with the following options:

   Note: Click New on the toolbar to create a new database. Use the Enter filter text box to filter a very large list of databases to quickly locate the one that you want to define.

   Name

   Specifies the name of the database. Change the name of the database in this field.

   Database Type

   Specifies the database type. Select the database type from the drop-down list.

   Compatibility Level

   Specifies the SQL Server version compatibility level.

   Collation

   Specifies the default collation for the database. Enter the collation value in the column. If collation is not specified, the default collation of SQL Server is assigned.

   Note: A collation name cannot be specified if the FOR ATTACH or FOR ATTACH_REBUILD clauses are specified.

   Generate

   Generates SQL during forward engineering. Clear the check box if you do not want to generate SQL.

3. Click the General tab and work with the following options:

   ANSI Nulls Default

   Specifies the default value of a column, alias data type, or CLR user-defined type for which nullability is not explicitly defined. Columns that are defined with constraints follow those constraints regardless of this setting. A TRUE value specifies that the default is NULL and FALSE is NOT NULL.

   ANSI Nulls

   Specifies how comparisons to a NULL value are handled. A TRUE value specifies UNKNOWN comparison and a FALSE value specifies TRUE comparison.

   ANSI Padding

   Specifies whether strings are padded to the same length before a conversion or insertion to a varchar or nvarchar data type.

   ANSI Warnings

   Specifies whether errors or warnings are issued when conditions such as divide-by-zero occur or NULL values appear in aggregate functions. A TRUE value specifies that an error or warning will be issued. A FALSE value specifies that no warnings are issued.

   Arithmetic Abort

   Specifies whether a query ends when an overflow or divide-by-zero error occurs. A TRUE value specifies that the query ends. A FALSE value specifies that a warning message is issued and querying continues.

   Concatenate Nulls Yields Nulls

   Specifies if SQL Server returns a NULL value when a concatenation operation begins and one of the values is NULL, or if it treats it as an empty string. A TRUE value specifies that the concatenation operation return NULL. A FALSE value specifies that the concatenation operation treat the NULL as an empty string and return the parameter that was not NULL.

   Numeric Round Abort

   Specifies whether an error is generated when a loss of precision occurs in an expression.

   Quoted Identifier

   Specifies whether double quotation marks can be used to enclose delimited characters. A TRUE value specifies that double quotation marks are permitted. A FALSE value specifies that double quotation marks are not permitted.

   Note: SQL Server also allows for identifiers to be delimited by square brackets regardless of the value of this property.

   Recursive Triggers

   Specifies whether the recursive firing of AFTER triggers is allowed.

   Database

   Lets you specify the snapshot database.

   State

   Specifies the database state.

   DB Chaining

   Specifies whether the database can participate in a cross-database ownership chain.

   Trustworthy

   Specifies the value for the trustworthy property to determine whether views, user-defined functions, or stored procedures are allowed to access the database if they use an impersonation context.

   Allow Snapshot Isolation

   Specifies whether database transactions can specify the SNAPSHOT transaction isolation level. A TRUE value specifies that transactions can specify the SNAPSHOT transaction isolation level. A FALSE value specifies that transactions cannot specify the SNAPSHOT transaction isolation level.

   Read Committed Snapshot

   Specifies whether database transactions that specify the READ COMMITTED isolation level use row versioning instead of locking. A TRUE value specifies that the READ COMMITTED isolation level use row versioning. A FALSE value specifies that the READ COMMITTED isolation level use locking.

   Vardecimal Storage Format

   Specifies whether to enable the database for vardecimal storage format. A TRUE value specifies that vardecimal storage format is permitted, which allows you to specify that a table in the database be enabled for vardecimal storage. This means that a table can store decimal and numeric columns using a variable-length storage format. A FALSE value specifies that vardecimal storage format is not permitted.

   Is Encrypted

   Specifies whether encryption is enabled or disabled at the database level.

   Is Change Data Capture Enabled

   Specifies whether Change Data Capture is enabled on the database. Select the value from the drop-down list.

   Change Tracking

   Specifies whether to enable or disable change tracking for the database.

   Auto Cleanup

   Specifies whether to enable automatic removal of the change tracking information from the database. An ENABLE value specifies that change tracking information is automatically removed from the database after the specified retention period. A DISABLE value specifies that change tracking information is not removed from the database.

   Change Retention Period

   Specifies the minimum timeframe for retention of change tracking information. Enter a number that corresponds to the timeframe you must select in the Change Retention Period Type drop-down. For example, you can enter 10 in this field, and then specify whether this is minutes, hours, or days in Change Retention Period Type.

   Note: Change tracking information is removed only if the Auto Cleanup option is enabled.

   Change Retention Period Type

   Lets you specify the timeframe that corresponds to the numerical value you entered in the Change Retention Period field. Make a selection from the drop-down.

4. Click the Files tab to define the filegroups and log files for the database.
5. Click the Options tab to further define options for the database.
6. (Optional) Click the Where Used tab to view where the database is used in the model.
7. Click the Permission tab to define the permissions for the database.
8. (Optional) Click the Comment tab and enter any comments that you want to associate with the database.
9. (Optional) Click the UDP tab to work with user-defined properties for the database.
10. (Optional) Click the Notes tab to view history information and view or edit user notes.
11. Click Close.

    The database is defined and the SQL Server Database Editor closes.