Physical Modeling › SQL Server Physical Property Editors › SQL Server Database Objects › Define a SQL Server Database

Define a SQL Server Database

Use the SQL Server Database Editor to define database properties in a SQL Server 2005/2008 physical model.

To define a SQL Server database

1. Click Databases on the Database 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.

   • 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.

   • 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.

   • Recovery

     Specifies the recovery option set for the database. If the recovery option is not specified, the default is set to the recovery model of the model database in SQL Server. Select the recovery type from the drop-down list.

   • 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 datatype, 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.

   • 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.

   • Database Type

     Specifies the database type. This field is populated with the type that is currently defined in the Database Type column of the Navigation Grid; any change you make in this field is also changed in the Navigation Grid.

   • Compatibility Level

     Specifies the SQL Server version compatibility level.

   • Database

     Specifies the source database for the database snapshot.

     Note: This option is only available if the selected database you are working with is designated as a Snapshot in the Database Type column of the Navigation Grid.

4. Click the Files tab to define the filegroups and logfiles for the database.
5. Click the Options tab to further define options for the database.
6. Click the Permission tab to define the permissions for the database.
7. (Optional) Click the Comment tab and enter any comments that you want to associate with the database.
8. (Optional) Click the UDP tab to work with user-defined properties for the database.
9. Click Close.

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