Relationships and Domains › Domains and Data Modeling › Domains in the Physical Model › Specifying Database-Specific Options For a Domain › Define SQL Server Domain Properties

Define SQL Server Domain Properties

In a model based on a SQL Server target server, you use the SQL Server tab in the Domain Editor to manage the data type, null option, and other properties for the selected domain.

Note: The options described here are the basic options available for most physical data types. There can be more options available depending on the physical data type you assign to the domain. We recommend that you refer to your SQL Server documentation for more information about using these properties.

To define SQL Server domain properties

1. Click Domains on the Model menu.

   The Domain Editor opens.

2. Select the domain in the Navigation Grid that you want to define.

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

3. Click the SQL Server tab and work with the following options:

   Code Type

   Lets you specify a code type for the column, either SQL or CLR user-defined type column.

   Physical Data type

   Lets you select a data type from the drop-down list to apply to the selected domain. If the selected data type requires a value for precision, scale, or both, enter the value inside the data type parentheses. For example, you can select the DECIMAL() data type and specify a precision of 2 and a scale of 10 inside the parentheses to assign the data type DECIMAL(2,10) to the selected domain.

   Null Option

   Lets you specify a null option.

   Average Width

   Lets you enter an estimated average width for columns if variable width is allowed based on the data type you assigned. If it is not allowed, an error is displayed in the Details section at the bottom of the editor. You can later use the Volumetrics dialog to calculate table and database size estimates based on these and other values.

   Percent Null

   Lets you enter the estimated percentage of nulls used for columns if the option is available. Use whole numbers. You can later use the Volumetrics dialog to calculate table and database size estimates based on these and other values.

   Row GUID

   Specifies whether the column is a row GUID column.

   Sparse

   Specifies whether the column is a sparse column.

   Collation

   Specifies the collation name for the column. Enter the collation name in this field.

   XML Schema Collection

   Specifies the XML Schema Collection to associate with the column. Select an XML Schema Collection from the drop-down.

   Note: You can only specify this option if the physical data type is set to XML. The schema must first be created in the database.

   XML Document Type

   Specifies how the instances are stored in an XML column, either as XML content or as an XML document.

   Note: You can only specify this option if the physical data type is set to XML and you have specified an XML Schema Collection.

   Populate All Rows With Default Value

   Specifies to populate new rows with default column values. Select the check box to enable this option.

   Persisted

   Specifies that a computed column is PERSISTED. A column marked PERSISTED is physically stored in a table and not a virtual column. When you select the check box for this option, enter the expression to use to define the value of the computed column in the SQL text area of the Expression option.

4. (Optional) Click the Permission tab to define the permissions for the database.
5. (Optional) Click the Constraints tab to define the validation rules and default values for the domain.
6. (Optional) Click the Comment tab and enter any comments that you want to associate with the object.
7. (Optional) Click the Where Used tab to view where the object is used within the model.
8. (Optional) Click the UDP tab to work with user-defined properties for the object.
9. (Optional) Click the Notes tab to view and edit user notes.
10. (Optional) Click the Extended Notes tab to view or edit user notes.
11. Click Close.

    Domain properties are defined and the Domain Editor closes.