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

Define a Table in SQL Server 2008

Use the SQL Server Table Editor to define the tables in a database in a SQL Server 2008 physical model.

To define a table in SQL Server 2008

1. Click Tables on the Model menu.

   The SQL Server Table Editor opens.

2. Select the table 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 table. Use the Enter filter text box to filter a very large list of tables to quickly locate the one that you want to define.

   Physical Name

   Specifies the physical name of the table. Change the physical name of the table in this field.

   Schema

   Specifies the schema of the database to which the table belongs. Select the schema from the drop-down list.

   Physical Only

   Specifies whether the table is suppressed from a logical model and appears in a physical model only.

   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:

   Filegroup

   Specifies the file group for the table. Select a file group from the drop-down list.

   Text Image

   Specifies that the text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), and CLR user-defined type columns are stored in the specified file group.

   Filestream Filegroup

   Specifies the file group for FILESTREAM data. If the table is not partitioned, the FILESTREAM column cannot be partitioned. FILESTREAM data for the table must be stored in a single file group. This file group is specified in the FILESTREAM_ON clause.

   Filestream Partition Scheme

   Specifies the partition scheme and its file group for FILESTREAM data. If the table contains FILESTREAM data and the table is partitioned, the FILESTREAM_ON clause must be included and must specify a partition scheme of FILESTREAM file groups. This partition scheme must use the same partition function and partition columns as the partition scheme for the table.

   Change Tracking

   Specifies whether to enable change tracking for the selected table.

   Default: Disable

   Track Columns Updated

   Specifies whether the database engine tracks which change tracked columns were updated.

   Default: Off

   Lock Escalation Type

   Specifies the allowed methods of lock escalation for a table. Select the method to use from the drop-down.

   Vardecimal Storage Format

   Specifies whether vardecimal storage is enabled. When it is set to TRUE, ON, or 1, the selected table is enabled for vardecimal storage format. When it is set to FALSE, OFF, or 0, the table is not enabled for vardecimal storage format.

4. Work with the other tabs in the editor to access additional design features, for example:
   • Define partition columns in the Partitions tab
   • Provide the specifications for capturing changes to data stored in a table using the ChangeDataCapture tab
   • Define how table data is compressed to reduce the amount of storage space it needs using the DataCompression tab
   • Specify validation rules in the Validation tab
   • Define table permissions using the Permission tab
   • Define table volumetrics in the Volumetrics tab
   • Work with style sheets which are used to format the font and other graphical themes used in the display of the table in the Style tab
   • Specify the icon style to use for the table in the Icon tab
   • Specify the order of DDL generation in the Object Creation Order tab
   • View where the table is used within the model in the Where Used tab
5. (Optional) Click the Comment tab and enter any comments that you want to associate with the table.
6. (Optional) Click the UDP tab to work with user-defined properties for the table.
7. (Optional) Click the Notes tab to view history information and view or edit user notes.
8. Click Close.

   The table is defined and the SQL Server Table Editor closes.

More information:

Define a Partition for a SQL Server Table

Define Change Data Capture Settings for a SQL Server 2008 Table

Define Change Data Capture Properties

Define Data Compression for Tables, Table Indexes, or View Indexes in SQL Server 2008

Define Validation Rule Usage for a SQL Server Table

Define SQL Server Permissions

Define Volumetrics for a SQL Server Table

Define the Style for a Table, View, or Column

Specify the Icon to Display for a Table or View

Change the Object Creation Order for Tables or Views

View the Where Used Information for a Physical Object

Add a Comment in a Property Editor

Add a UDP in a SQL Server Physical Property Editor

Edit User Notes or View Object History