Creating Models › Creating a Physical Model › Columns › Defining Table Column Properties for Target Databases › Define SQL Server Table Column Properties

Define SQL Server Table Column Properties

In a model based on a SQL Server 2008, 2012, 2014, or 2016 target server, you use the SQL Server tab in the Table Column Editor to manage the data type, null option, and other properties for the selected column.

Note: The options described here are the basic options available for most SQL Server versions and physical data types. These options can vary depending on the SQL Server version that you set as the target database and the physical data type you assign to the table column. For more information about these properties, refer to SQL Server documentation.

To define SQL Server table column properties:

1. Right-click a column in the Model Explorer and click Properties.

   The SQL Server Table Column Editor opens.

2. Select the table from the Table drop-down to define the columns that are available for the table.
3. Select the column 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 column. Use the Enter filter text box to filter a very large list of columns to quickly locate the one that you want to define.

   Physical Name

   Specifies the physical name of the column. You can change the name of the column in this field.

   Domain Parent

   Specifies the domain parent of the column. Select the domain parent from the drop-down list.

   Physical Data Type

   Specifies the physical data type for the selected column. Select a physical data type from the drop-down list.

   Primary Key

   Specifies to designate the selected column as a primary key column. Select the check box to enable this option.

   Physical Only

   Specifies whether the column is suppressed from a logical model (as an attribute) and appears in a physical model only.

   Foreign Key

   Specifies to designate the selected column as a foreign key column. Select the check box to enable this option.

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

   Physical Data type

   Lets you select a data type from the drop-down list to apply to the selected column. 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 column.

   Null Option

   Lets you set whether the selected column allows a null value.

   Average Width

   Lets you enter an estimated average width for the column 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 that column if the option is available. For example, you can estimate a variable width ADDRESS 2 column assigned a width of 50 characters, to be NULL about 30 percent of the time. 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.

   Is Sparse Column Set

   Specifies whether the sparse column is used in a column set.

   Column Mask Function

   Specifies the dynamic data masking function for the column. Available SQL Server 2016 onward.

   Hidden

   Specifies whether the column is a hidden column.

   Column Encryption Key

   Specifies the encryption key to be used for the column. Available SQL Server 2016 onward.

   Encryption Type

   Specifies the type of encryption to be used. Available SQL Server 2016 onward.

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

5. (Optional) Click the Link tab and work with the Link Wizard to link selected columns and to select the properties to synchronize between the linked objects.
6. (Optional) Click the Indexes tab and define the index membership details for the column.
7. (Optional) Click the Constraint tab and specify the validation rule and default value for the column.
8. (Optional) Click the Style tab and format the font and other graphical themes used in the display of the columns.
9. (Optional) Click the Comment tab and enter any comments that you want to associate with the object.
10. (Optional) Click the Where Used tab to view where the object is used within the model.
11. (Optional) Click the UDP tab to work with user-defined properties for the object.
12. (Optional) Click the History tab to view the history information for the entity.
13. (Optional) Click the Notes tab to view and edit user notes.
14. Click Close.

    SQL Server 2008 table column properties are defined and the SQL Server Table Column Editor closes.