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

Define a Database in SQL Server 2005

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 2005 physical model.

To define a database in SQL Server 2005

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 database compatibility to determine how the database behaves with an earlier version of Microsoft SQL Server.

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

   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.

   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.

   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.

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. 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. (Optional) Click the Notes tab to view history information and view or edit user notes.
10. Click Close.

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

More information:

Define SQL Server 2005 Database Files

Define Options for a SQL Server 2005 Database

Define Permissions for an Object in a SQL Server Property Editor

Add a Comment in a Property Editor

Add a UDP in a SQL Server Physical Property Editor

Define Physical Files for a Database in SQL Server 2005