SQL Server Non Clustered Keys

When you create a primary key in SQL Server, it is created with a 'clustered index' by default. Therefore when you model a primary key for SQL Server in an Enterprise Architect data model, the corresponding DDL creates a clustered index for that primary key by default.

Clustered indexes provide improved performance for accessing the column(s) involved, but only one clustered index is allowed per table.

In some situations, you might be more interested in the performance of columns other than the Primary Key, and therefore must change the default assignment of the clustered index. Enterprise Architect allows you to model this.

How To:

To define a Primary Key as non-clustered for a SQL Server table, follow the steps below:

Step

Action

See Also

1

Right-click on the table in a diagram and select the Operations context menu option

The Table Operations dialog displays

 

2

Highlight the Primary Key Operation and click on the Extended Properties button

The Database Operation Properties dialog displays

 

3

Select the SQL Server Non Clustered Primary Key checkbox

 

4

Click on the Save & Close button

 

Subsequently, you can model an index for that table and define it as clustered instead.

Learn more