Microsoft SQL Server

Note: The DBMS for SQL Server v7.x is deprecated.

The following sections list the extensions provided for MS SQL Server.

Note: In addition to the extensions listed below, PowerDesigner supports the following features for SQL Server 2005 and higher:

User Schemas – Use the schema stereotype to specify that a user is actually a schema, belonging to another user (the "principal").
WithOption – Use the withoptions type to enable access to additional physical options when working with views.
Support for multiple databases during live database reverse engineering.

Abstract Data Types

The following extensions are available on the Microsoft tab:

Name	Description
Assembly	Specifies the assembly to bind with the abstract data type. Scripting name: Assembly

Name

Description

Assembly

Specifies the assembly to bind with the abstract data type.

Scripting name: Assembly

Abstract Data Type Attributes

The following extensions are available on the Microsoft tab:

Name	Description
Nullable	Specifies that the type column allows null value. Scripting name: Nullable
Computed	Specifies that the type column is computed. Scripting name: Specifies that the type column is computed.
Identity	Specifies that the new column is an identity column. Scripting name: Identity
Expression	Specifies an expression that defines the value of a computed column. Scripting name: Expression
Persisted	Specifies that the SQL Server Database Engine will physically store the computed values in the table, and update the values when any other columns on which the computed column depends are updated. Scripting name: Persisted
Seed	Specifies the value used for the very first row loaded into the table. Scripting name: Seed
Increment	Specifies the incremental value added to the identity value of the previous row loaded. Scripting name: Increment
Default	Specifies the value provided for the column when a value is not explicitly supplied during an insert. Scripting name: Default
Row GUID	Specifies that the new column is a row GUID column Scripting name: RowGuidCol
Collation	Specifies the collation for the column. Scripting name: Collate

Columns

The following extensions are available on the Microsoft tab:

Name	Description
Row global unique identifier	[v2000 and higher] Indicates that the new column is a row global unique identifier column. Only one unique identifier column per table can be designated as the ROWGUIDCOL column. Scripting name: ExtRowGuidCol
Sparse	[v2008 and higher] Specifies that the column is a sparse column. The storage of sparse columns is optimized for null values. Sparse columns cannot be designated as NOT NULL. Scripting name: Sparse
Filestream	[v2008 and higher] Specifies that when the FILESTREAM storage attribute is specified for a column, all values for that column are stored in a FILESTREAM data container on the file system Scripting name: Filestream
Do not validate check constraints during replication	Specifies that "NOT FOR REPLICATION" keywords are used to prevent the CHECK constraint from being enforced during the distribution process used by replication. Scripting name: ExtCkcNotForReplication
Default constraint name	Contains the name of the constraint that is used to apply a default value to the column. If empty, the "constraint" keyword is not generated. Scripting name: ExtDeftConstName
Not null constraint name	Contains the name of the constraint that is used to apply a mandatory property of the column. If empty, the "constraint" keyword is not generated. Scripting name: ExtNullConstName
Collation name	[v2005 and higher] A single string that specifies the collation name for a SQL collation. Scripting name: ExtCollation
Identity seed and increment	Is a string composed of two integer values separated by a comma. First value is the seed value of the identity column, meaning the value to be assigned to the first row in the table. Second value is the increment to add to the seed value for successive rows in the table. Scripting name: ExtIdentitySeedInc
Identity value not replicated	Indicates that the IDENTITY property should not be enforced when a replication login inserts data into the table. Scripting name: ExtIdtNotForReplication
XML schema collection	[v2000 and higher] Applies only to the XML data type for associating an XML schema collection with the type. Scripting name: XMLSchemaCollection
Content type	[v2005 and higher] - CONTENT: Specifies that each instance of the XML data type in column_name can contain multiple top-level elements. CONTENT applies only to the XML data type and can be specified only if xml_schema_collection is also specified. If not specified, CONTENT is the default behavior. - DOCUMENT: Specifies that each instance of the XML data type in column_name can contain only one top-level element. DOCUMENT applies only to the XML data type and can be specified only if xml_schema_collection is also specified. Scripting name: ContentType

Cubes

The following extensions are available on the Microsoft tab:

Name	Description
Options	[v2000] You can choose between the following: PASSTHROUGH: causes the SELECT clause to be passed directly to the source database without modification by PivotTable Service. If PASSTHROUGH is not specified, PivotTable Service parses the query and formulates a set of queries equivalent to the original that is optimized for the source database and index structures. This set of queries is often more efficient than the specified. DEFER_DATA: causes the query to be parsed locally and executed only when necessary to retrieve data to satisfy a user request. DEFER_DATA is used to specify that a local cube has to be defined in the ROLAP storage mode. ATTEMPT_DEFER: causes PivotTable Service to attempt to parse the query and defer data loading if successful, or, if the query cannot be parsed, to process the specified query immediately as if PASSTHROUGH had been specified. ATTEMPT_ANALYSIS: causes PivotTable Service to attempt to parse the query and formulate an optimized set of queries. If the query cannot be parsed, PivotTable Services processes the query immediately as if PASSTHROUGH had been specified. Scripting name: Options
Storage mode	[v2005 and higher] Specifies the storage mode for the cube. Scripting name: StorageMode
Visible	[v2005 and higher] Determines the visibility of the Cube. Scripting name: Visible

Name

Description

Options

[v2000] You can choose between the following:

PASSTHROUGH: causes the SELECT clause to be passed directly to the source database without modification by PivotTable Service. If PASSTHROUGH is not specified, PivotTable Service parses the query and formulates a set of queries equivalent to the original that is optimized for the source database and index structures. This set of queries is often more efficient than the specified.
DEFER_DATA: causes the query to be parsed locally and executed only when necessary to retrieve data to satisfy a user request. DEFER_DATA is used to specify that a local cube has to be defined in the ROLAP storage mode.
ATTEMPT_DEFER: causes PivotTable Service to attempt to parse the query and defer data loading if successful, or, if the query cannot be parsed, to process the specified query immediately as if PASSTHROUGH had been specified.
ATTEMPT_ANALYSIS: causes PivotTable Service to attempt to parse the query and formulate an optimized set of queries. If the query cannot be parsed, PivotTable Services processes the query immediately as if PASSTHROUGH had been specified.

Scripting name: Options

Storage mode

[v2005 and higher] Specifies the storage mode for the cube.

Scripting name: StorageMode

Visible

[v2005 and higher] Determines the visibility of the Cube.

Scripting name: Visible

Dimensions

The following extensions are available on the Microsoft tab:

Name	Description
Hidden	[v2000] Indicates whether the dimension is hidden from clients. Scripting name: IsHidden
Options	[v2000] Dimension options to manage member uniqueness and specify their storage. You can choose between: UNIQUE_NAME: Member names are unique within the dimension. UNIQUE_KEY: Member keys are unique within the dimension. NOTRELATEDTOFACTTABLE: Indicates that non-leaf members cannot be associated with fact table data. ALLOWSIBLINGSWITHSAMENAME: Determines whether children of a single member in a hierarchy can have identical names. Scripting name: Options
Subtype	[v2000] Indicates the subtype of a dimension. You can choose between: PARENT_CHILD:Indicates that the dimension is a parent-child dimension. LINKED: Indicates that the cube is linked to another cube on a remote Analysis server. MINING: Indicates that the dimension is based on the content of an OLAP data-mining model that has been processed for a cube. Scripting name: SubType
Template	[v2000] Contains a template string that is used to generate captions for system-generated data members. Scripting name: Template
Time	[v2000] Indicates that a dimension refers to time (year, month, week, day, and so on). You can choose between: TIME: Year, month, week, day, and so on. The only valid levels in a time dimension are those defined in the LevelTypes enumeration. The following values post-fixed by an asterisk () are additional values that can be used by the add-in but do not exist in the MDX syntax. You can choose between a dimension that contains: ACCOUNT: () an account structure with parent-child relationships. BILLOFMATERIALS (): a material/component breakdown. The parent-child relationship implies a parent composed of its children. CHANNEL (): a distribution channel. CURRENCY (): currency information. CUSTOMERS (): customer information. The lowest level represents individual customers. GEOGRAPHY (): a geographic hierarchy. ORGANIZATION (): the reporting structure of an organization. PRODUCTS (): product information. The lowest level represents individual products. PROMOTION (): marketing and advertising promotions. QUANTITATIVE (): quantitative elements (such as example, income level, number of children, and so on). RATES (): different types of rates (for example, buy, sell, discounted. and so on). SCENARIO (*): different business scenarios. Scripting name: TimeDef
Type	[v2005 and higher] Provides information about the contents of the dimension. Scripting name: Type
Storage mode	[v2005 and higher] Determines the storage mode for the parent element. Scripting name: StorageMode
AttributeAllMemberName	[v2005 and higher] Contains the caption, in the default language, for the All member of the dimension. Scripting name: AttributeAllMemberName
WriteEnabled	[v2005 and higher] Indicates whether dimension writebacks are available (subject to security permissions). Scripting name: WriteEnabled

Dimension Attributes

The following extensions are available on the Microsoft tab:

Name	Description
Rollup expression	[v2000] Contains a Multidimensional Expressions (MDX) expression used to override the default roll-up mode. Scripting name: CustomRollupExpr
Format key	[v2000] Name of the column or expression that contains member keys. Scripting name: FormatKey
Format name	[v2000] Name of the column or expression that contains member names. Scripting name: FormatName
Hide values	[v2000] Options to hide level members. You can choose between: BLANK_NAME: Hides a level member with an empty name. PARENT_NAME: Hides a level member when the member name is identical to the name of its parent. ONLY_CHILD_AND_BLANK_NAME: Hides a level member when it is the only child of its parent and its name is null or an empty string. ONLY_CHILD_AND_PARENT_NAME: Hides a level member when it is the only child of its parent and is identical to the name of its parent. Scripting name: HideValues
Hidden	[v2000] Indicates whether the level is hidden from client applications. Scripting name: IsHidden
Options	[v2000] Options about member uniqueness, ordering and data source. You can choose between: UNIQUE: Indicates that the members of a level are unique. UNIQUE_NAME: Indicates that their member name columns uniquely identify the level members. UNIQUE_KEY: Indicates that their member key columns uniquely identify the level members. NOTRELATEDTOFACTTABLE: Indicates that the level members cannot be associated with fact table data. SORTBYNAME: Indicates that level members are ordered by their names. SORTBYKEY: Indicates that level members are ordered by their keys. SORTBYPROPERTY <property names>: Indicates that members are ordered by their property <property names>. Scripting name: Options
Root values	[v2000] Determines how the root member or members of a parent-child hierarchy are identified. You can choose between: ROOT_IF_PARENT_IS_BLANK: Only members with a null, a zero, or an empty string in their parent key column are treated as root members. ROOT_IF_PARENT_IS_MISSING: Only members with parents that cannot be found are treated as root members. ROOT_IF_PARENT_IS_SELF: Only members having themselves as parents are treated as root members. ROOT_IF_PARENT_IS_BLANK _OR_SELF_OR_MISSING: Members are treated as root members if they meet one or more of the conditions specified by ROOT_IF_PARENT_IS_BLANK, ROOT_IF_PARENT_IS_SELF, or ROOT_IF_PARENT_IS_MISSING. Scripting name: RootValues
Type	[v2000 and higher] Identifies the specific type of level. You can choose between: ALL: Indicates the top (All) level of a dimension (the one that precalculates all the members of all lower levels). YEAR: a level that refers to years (Time dimension only). QUARTER: a level that refers to (calendar) quarters (Time dimension only). MONTH: a level that refers to months (Time dimension only). WEEK: a level that refers to weeks (Time dimension only). DAY: a level that refers to days (Time dimension only). DAYOFWEEK: a level that refers to days of the week (Time dimension only). DATE: a level that refers to dates (Time dimension only). HOUR: a level that refers to hours (Time dimension only). MINUTE: a level that refers to minutes (Time dimension only). SECOND: Indicates that a level refers to seconds (Time dimension only). Scripting name: Type
MembersWithData	[v2005 and higher] Determines whether to display data members for non-leaf members in the parent attribute. Scripting name: MembersWithData
OrderBy	[v2005 and higher] Describes how to order the members contained in the attribute. Scripting name: OrderBy
MemberNamesUnique	[v2005 and higher] Determines whether member names under the parent element must be unique. Scripting name: MemberNamesUnique
IsAggregatable	[v2005 and higher] Specifies whether the values of the DimensionAttribute element can be aggregated. Scripting name: IsAggregatable
AttributeHierarchyEnabled	[v2005 and higher] Determines whether an attribute hierarchy is enabled for the attribute. Scripting name: AttributeHierarchyEnabled
AttributeHierarchyVisible	[v2005 and higher] Determines whether the attribute hierarchy is visible to client applications. Scripting name: AttributeHierarchyVisible

Databases

The following extensions are available on the Microsoft tab:

Name	Description
Primary	Specifies that the associated file specification list defines the primary file. Scripting name: Primary
File	Gets or sets the file specification. Scripting name: FileListFileSpec
Filegroup	Gets or sets the first filegroup name. Scripting name: FilelistFilegroup
File (filegroup)	Gets or sets the Filegroup specification. Scripting name: FileGroupFileSpec
Log on	Gets or sets the log file specification. Scripting name: LogOnFileSpec
Collation name	[v2000 and higher] Specifies the default collation for the database. Collation name can be either a Windows collation name or a SQL collation name. Scripting name: Collate
Attach	Specifies that a database is attached from an existing set of operating system files. Scripting name: ForAttach
With	[v2005 and higher] Controls Service Broker options on the database. Service Broker options can only be specified when the FOR ATTACH clause is used. ENABLE_BROKER: Specifies that Service Broker is enabled for the specified database. NEW_BROKER: Creates a new service_broker_guid value in both sys.databases and the restored database and ends all conversation endpoints with clean up. The broker is enabled, but no message is sent to the remote conversation endpoints. ERROR_BROKER_CONVERSATIONS: Ends all conversations with an error stating that the database is attached or restored. The broker is disabled until this operation is completed and then enabled. Scripting name: ForAttachWith
Attach rebuild log	[v2005 and higher] Specifies that the database is created by attaching an existing set of operating system files. Scripting name: ForAttachRebuildLog
Database chaining	[v2005 and higher] When ON is specified, the database can be the source or target of a cross database ownership chain. When OFF, the database cannot participate in cross database ownership chaining. The default is OFF. Scripting name: WithDbChaining
Trust worthy	[v2005 and higher] When ON is specified, database modules (for example, views, user-defined functions, or stored procedures) that use an impersonation context can access resources outside the database. When OFF, database modules in an impersonation context cannot access resources outside the database. The default is OFF. Scripting name: WithTrustworthy
Snapshot of	[v2005 and higher] Specifies the name of the new database snapshot. Scripting name: AsSnapshotOf
Load	[up to v2000] Indicates that the database is created with the "dbo use only" database option turned on, and the status is set to loading. Scripting name: ForLoad

For information about the extended attributes available on the Mirroring tab, see Database mirroring.

Data Sources

The following extensions are available on the OLE DB tab:

Name	Description
Data provider	Specifies the data provider. You can choose between: .NET Framework Data Provider for Microsoft SQL Server .NET Framework Data Provider for Oracle Native Data Provider for OLE DB Scripting name: DataProvider
Connection string	Specifies the connection string. Scripting name: ConnectionString

Name

Description

Data provider

Specifies the data provider. You can choose between:

.NET Framework Data Provider for Microsoft SQL Server
.NET Framework Data Provider for Oracle
Native Data Provider for OLE DB

Scripting name: DataProvider

Connection string

Specifies the connection string.

Scripting name: ConnectionString

The following extensions are available on the Configuration tab:

Name	Description
Server name	Specifies the server name. Scripting name: ServerName
Authentication	[only for SQL Server] Specifies the Windows Authentication and SQL Server Authentication types. Scripting name: AuthenticationType
User name	Specifies the User name. Scripting name: UserName
Password	Specifies the password. Scripting name: Password
Initial catalog	[only for SQL Server and OLE DB] Specifies the Initial catalog. Scripting name: InitialCatalog
Database File	[only for SQL Server] Specifies a Microsoft SQL Server database file if you select an MSSQL connection. Scripting name: MSSQLDatabaseFile
Logical name	[only for SQL Server] Specifies the logical name of the selected database file. Scripting name: LogicalName
Data providers	[only for OLE DB] Specifies the data provider. Scripting name: DataProvider
Location	[only for OLE DB] Specifies the location for OLEDB. Scripting name: Location
Persist security info	[only for OLE DB] Specifies that security information be persistent. Scripting name: PersistSecurityInfo
Use Windows NT Integrated Security	[only for OLE DB] Specifies whether to use windows NT Integrated Security or not. Scripting name: UseNTIntegratedSecurity

Dimension Hierarchies

The following extensions are available on the Microsoft tab:

Name	Description
Hidden	[v2000] Indicates whether the hierarchy is hidden from client applications. Scripting name: IsHidden
AllMemberName	[v2005 and higher] Contains the caption in the default language for the All member of a Hierarchy element. Scripting name: AllMemberName
MemberNamesUnique	[v2005 and higher] Determines whether member names under the parent element must be unique. Scripting name: MemberNamesUnique
AllowDuplicateNames	[v2005 and higher] Determines whether duplicate names are allowed in a Hierarchy element. Scripting name: AllowDuplicateNames

Fact Measures

The following extensions are available on the Microsoft tab:

Name	Description
Format	[v2000] Format used to display the values of the cube measure. Scripting name: Format
Cube measure function type	[v2000] A value corresponding to the type of aggregate function used by the cube measure. Scripting name: Function
Hidden	[v2000] Indicates whether the measure is visible to the client. Scripting name: IsHidden
Member calculating order	[v2000] Order in which the calculated member will be solved when calculated members intersect each other. Scripting name: SolveOrder
Source column data type	[v2000] Returns an OLE DB enumeration constant that identifies the SourceColumn (in the fact table) data type. Scripting name: Type
AggregateFunction	[v2005 and higher] Defines the common prefix to be used for aggregation names throughout the associated parent element. Scripting name: AggregateFunction
BindingType	[v2005 and higher] Defines the binding type for the measure. Scripting name: BindingType
Visible	[v2005 and higher] Determines the visibility of the Fact Measure. Scripting name: Visible
FormatString	[v2005 and higher] Describes the display format for a CalculationProperty or a Measure element. Scripting name: FormatString

Indexes

Note: For additional information about special SQL Server index types, see XML Indexes (SQL Server) and Spatial Indexes (SQL Server).

The following extensions are available on the Microsoft tab:

Name	Description
Filegroup	Specifies the name of the filegroup. Scripting name: FileGroup
Partition scheme	[v2005 and higher] Specifies the name of the partition scheme. Scripting name: PartitionScheme
Column	[v2005 and higher] Specifies the partitioned column. Scripting name: PartitionSchemeColumn
Fill factor	Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild. Scripting name: FillFactor
Max degree of parallelism	[v2005 and higher] Overrides the max degree of parallelism configuration option for the duration of the index operation. Use MAXDOP to limit the number of processors used in a parallel plan execution. The maximum is 64 processors. Scripting name: MaxDop
Pad index	Specifies index padding. Scripting name: PadIndex
Statistics no recompute	Specifies whether distribution statistics are recomputed. Scripting name: StatisticsNoRecompute
Drop existing	Specifies that the named, preexisting clustered, nonclustered, or XML index is dropped and rebuilt. Scripting name: DropExisting
Online	[v2005 and higher] Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. Scripting name: Online
Sort in temporary database	[v2005 and higher] Specifies whether to store temporary sort results in tempdb. Scripting name: SortInTempDB
Allow row locks	[v2005 and higher] Specifies whether row locks are allowed. Scripting name: AllowRowLocks
Allow page locks	[v2005 and higher] Specifies whether page locks are allowed. Scripting name: AllowPageLocks
Ignore dup key	Specifies the error response to duplicate key values in a multiple row insert operation on a unique clustered or unique nonclustered index. Scripting name: IgnoreDupKey

If the index is not a cluster index, then the Include tab is displayed, allowing you to specify the columns with which it is associated.

Keys

The following extensions are available on the Microsoft tab:

Name

Description

Filegroup

Specifies the name of the filegroup.

Scripting name: FileGroup

Fill Factor

Specifies how full SQL Server should make each index page used to store the index data.

Scripting name: FillFactor

References

The following extensions are available on the Microsoft tab:

Name	Description
Do not validate foreign key constraint during replication	Specifies that "NOT FOR REPLICATION" keywords are used to prevent the FOREIGN KEY constraint from being enforced during the distribution process used by replication. Scripting name: ExtFkNotForReplication

Name

Description

Do not validate foreign key constraint during replication

Specifies that "NOT FOR REPLICATION" keywords are used to prevent the FOREIGN KEY constraint from being enforced during the distribution process used by replication.

Scripting name: ExtFkNotForReplication

Storages

The following extensions are available on the Microsoft tab:

Name	Description
Contains filestream	Specifies that the filegroup stores FILESTREAM binary large objects (BLOBs) in the file system. Scripting name: FileStream

Name

Description

Contains filestream

Specifies that the filegroup stores FILESTREAM binary large objects (BLOBs) in the file system.

Scripting name: FileStream

Tables

The following extensions are available on the Microsoft tab:

Name	Description
Do not validate check constraints during replication	Specifies that "NOT FOR REPLICATION" keywords are used to prevent the TABLE CHECK constraint from being enforced during the distribution process used by replication. Scripting name: ExtCktNotForReplication
Table is partitioned	Specifies that the table is partitioned. Scripting name: PartitionedTable
Filegroup	[unpartitioned tables] Specifies the name of the filegroup. Scripting name: FileGroup
Text/Image	[unpartitioned tables] Specifies the name of the filegroup where text and image are stored. Scripting name: TextImageOn
Filestream	[unpartitioned tables] Specifies the name of the filegroup used for filestream. Scripting name: FilestreamOnFilegroup
Compression	[unpartitioned tables] Specifies the compression type of the table (none, row or page). Scripting name: TableCompression
Partition scheme	[partitioned tables, v2005 and higher] Specifies the name of the partition scheme. You must also specify the name of the partitioned column Scripting name: PartitionScheme, PartitionSchemeColumn
Filestream partition scheme	[partitioned tables, v2005 and higher] Specifies the name of the partition scheme. Scripting name: FilestreamPartitionScheme, FilestreamPartitionSchemeColumn
Compression	[partitioned tables] Specifies the partitions that use the compression. Scripting name: DataCompression

Triggers

The following extensions are available on the Microsoft tab:

Name	Description
Option	Is a concatenation of the `WITH ENCRYPTION` (which is illegal for CLR triggers, and which prevents the trigger from being published) and `EXECUTE AS` (which specifies the security context under which the trigger is executed) options. Scripting name: Option

Name

Description

Option

Is a concatenation of the WITH ENCRYPTION (which is illegal for CLR triggers, and which prevents the trigger from being published) and EXECUTE AS (which specifies the security context under which the trigger is executed) options.

Scripting name: Option

An additional property is available for CLR triggers (see CLR Procedures, Functions, and Triggers (SQL Server)).

Users

The following extensions are available on the General tab (v2005 and higher):

Name	Description
Implicit schema	Specifies that the stored procedure `sp_grantdbaccess` will be used instead of a create user statement during database generation. Scripting name: ImplicitSchema
Default schema	Specifies the first schema searched to resolve the names of objects for this user. If the Implicit schema option is selected, then the default schema is initialized to the name of the user. Scripting name: DefaultSchema

Name

Description

Implicit schema

Specifies that the stored procedure sp_grantdbaccess will be used instead of a create user statement during database generation.

Scripting name: ImplicitSchema

Default schema

Specifies the first schema searched to resolve the names of objects for this user. If the Implicit schema option is selected, then the default schema is initialized to the name of the user.

Scripting name: DefaultSchema

Views

The following extensions are available on the Microsoft tab:

Name	Description
Encryption option	Defines the encryption option of the view, respecting the view creation syntax. Scripting name: WithOption

Name

Description

Encryption option

Defines the encryption option of the view, respecting the view creation syntax.

Scripting name: WithOption