Creating Models › Target Servers for Physical Models › Database Property Editors › SQL Server Property Editors › SQL Server Property Editors › Define a Database in SQL Server

Define a Database in SQL Server

Use the SQL Server Database Editor for the following tasks:

• Create databases
• Define the files that are used to store an existing database
• Create a database snapshot
• Attach a database from the detached files of an existing detached database
• Define database properties in a SQL Server physical model

Follow these steps:

1. Click Model, Target Server, Databases to open the SQL Server Database Editor.
2. Click New to create a database or select an existing database name.
3. Work with the following options:

   Name

   Specifies the name of the database.

   Database Type

   Specifies the type of database. Select one of the following options:

   Primary

   Indicates that the specified files are primary files. If you do not specify Primary, the first file specified in the File Groups is considered as the primary file.

   Mirror

   Indicates that you want to use the database for mirroring.

   Snapshot

   Indicates that you want to create a snapshot of the database. A snapshot is typically created on a mirror database.

   Containment

   Specifies the containment status of the database. Select from one of the following options:

   None

   Indicates that the database is a noncontained database.

   Partial

   Indicates that the database is a partially contained database.

   Compatibility Level

   Specifies the SQL Server version compatibility.

   Collation

   Specifies the default collation for the database. If collation is not specified, the default collation of SQL Server is assigned. Enter either a Windows collation name or a SQL collation name.

   Note: If you have specified the FOR ATTACH or FOR ATTACH_REBUILD clauses, you cannot specify a collation name. In addition, you cannot specify a collation name on a database snapshot.

   Generate

   Generates SQL statements during forward engineering.

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

   SQL 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 is 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 whether you want to consider concatenation results as nulls or empty strings. TRUE specifies that the concatenation operation returns NULL. FALSE specifies that the concatenation operation treats NULL as an empty string and returns 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 square brackets as delimiters for identifiers, regardless of the value of this property.

   Recursive Triggers

   Specifies whether the recursive firing of AFTER triggers is allowed.

   Snapshot Options

   Database

   Specifies the snapshot database. This option is available if you have selected Snapshot for Database Type.

   FileStream Access Options

   Non Transacted Access

   Specifies the level of access that is ranted to Filestream data for nontransactional processes. Select from the following options:

   • OFF: Indicates that access to nontransactional processes is disabled.
   • READONLY: Indicates that the nontransactional processes can read the FILESTREAM data in this database.
   • FULL: Indicates that full access to FILESTREAM FileTables is enabled for nontransactional processes.

   Directory Name

   Specifies the directory name for the Filestream data. Enter a name that is unique among all the Database_Directory names in the SQL Server instance. Set this option before creating a FileTable in this database.

   Database Options

   State

   Specifies the database state. The state can have one of the following values:

   • Online: Indicates that the database is available for access.
   • Offline: Indicates that the database is not available for access.
   • Emergency: Indicates that the database is in single user mode. Typically, users change the database and set to Emergency to indicate that the database is repaired or restored.

   DB Chaining

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

   Trustworthy

   Specifies if the instance of SQL Server trusts the database and its contents. This property also determines whether views, user-defined functions, or stored procedures can access the database when 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. 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 you want to record activities such as insert, update, and delete.

   Delayed Durability

   Specifies the way in which Delayed Durability should be implemented at the database level. Available SQL Server 2014 onward.

   Change Tracking Options

   Change Tracking

   Specifies whether to enable or disable tracking changes 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 Type

   Specifies the timeframe for which you want to maintain tracking information. You can select minutes, hours, or days from the drop-down list.

   Change Retention Period

   Specifies a number that corresponds to the timeframe you select in the Change Retention Period Type drop-down list. For example, you can enter 10 in this field, and then specify whether 10 is minutes, hours, or days in Change Retention Period Type.

   Note: The Change Tracking Options section is displayed if Containment is not selected, or is None. The External Access Options section is displayed if Containment is Partial. Change tracking information is removed only if the Auto Cleanup option is enabled.

   External Access Options

   Nested Triggers

   Specifies whether you want to include cascading triggers.

   Transform Noise Words

   Specifies whether you want to suppress error messages if noise words or stopwords cause a Boolean operation on a full-text query to fail.

   Two Digit Year Cutoff

   Specifies an integer that represents the cutoff year to interpret two-digit years as four-digit years.

   Default Fulltext Language

   Specifies the default language that you want to use for full-text indexed columns. Enter the local id, language, or the language alias.

   Default Language

   Specifies the default language that you want to use for all newly created logins. Language can be specified by providing the local id (lcid), the language name, or the language alias.

5. Click the Files tab to define the filegroups and log files for the database.
6. Click the Options tab to define more options for the database.
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 object.
9. (Optional) Click the Where Used tab to view where the object is used within the model.
10. (Optional) Click the UDP tab to work with user-defined properties for the object.
11. (Optional) Click the Notes tab to view and edit user notes.
12. (Optional) Click the Extended Notes tab to view or edit user notes.
13. Click Close.

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

More information:

Add a Comment in a Property Editor

Add a UDP in a Physical Property Editor