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:



See Also


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

The Table Operations dialog displays



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

The Database Operation Properties dialog displays



Select the SQL Server Non Clustered Primary Key checkbox



Click on the Save & Close button


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

Learn more