How to Use Data Modeler to Create Unique Indexes with 'Included Columns' clause in SQL Server

Description:

SQL Server allows an Index to have 'Included Columns' clause.  How can we do that using erwin DM?  Also, why are we not seeing this clause for the Unique Indexes?

Solution:

You can add the 'Included' clause for the Indexes as per the steps below.

Steps:

 1-      Select the Table for which the Index is being created. Right click on this and select “Index Properties”

2-      Create a New Index. 

Note here that “Generate as Constraint” should be selected for Unique Indexes.  Constraint syntax takes precedence in Data Modeler when Forward Engineering.

3-      Select the Index Members and Included Columns

4-      Click Close.

 Forward Engineering will then give syntax such as the following…

CREATE UNIQUE NONCLUSTERED INDEX [XAK1Example_Table] ON [Example_Table]
(
          [Example_Col]         ASC
)
INCLUDE( [Example_Included_Col] )
Go

Please note that one only needs a Unique Constraint or a Unique Index, but not both as that will be redundant.  That is why just for Unique Constraint, even the Constraint is created with sort order clause, e.g. ASC (Ascending) or DESC (Descending) which essentially serves the same purpose as an Index.  However, if you want just the Unique Index with 'Included' Columns clause, the 'Generate as Constraint' check box will need to be deselected as shown above.