Unclustered index for PK in physical model for MS SQL Server

[unknown]

Hi,

Using V10

I need to sometime have non clustered index for primary keys
of some tables.

By default creating a PK constraint resulting of clustered
index for MS SQL Server. How can I turn this off and create
sometime an unclustered index for some PK ? I mean, where
can I do that in the Physical model ???

The script produce must be write this way :

/*==============================================================*/
/* Table : T_E_EMPLOYE_EMP
*/
/*==============================================================*/
create table T_E_EMPLOYE_EMP (
EMP_ID D_N_ID identity,
T_E_EMP_ID D_N_ID null,
EMP_NOM D_A_NOM not null,
EMP_MATRICULE D_A_MATRICULE not null,
constraint PK_T_E_EMPLOYE_EMP primary key (EMP_ID)
NONCLUSTERED
)
go

Thanks a lot

FB

[Marc]

Try checking key physical options tab, there must be a "clustered" item.
On the other hand, you might expect "primary key nonclustered (emp_id)"
instead...

With later version of powerdesigner, there is an attribute "Cluster" on
keys, easier to handle than physical options.

Marc.

Post by unknown
Hi,
Using V10
I need to sometime have non clustered index for primary keys
of some tables.
By default creating a PK constraint resulting of clustered
index for MS SQL Server. How can I turn this off and create
sometime an unclustered index for some PK ? I mean, where
can I do that in the Physical model ???
/*==============================================================*/
/* Table : T_E_EMPLOYE_EMP
*/
/*==============================================================*/
create table T_E_EMPLOYE_EMP (
EMP_ID D_N_ID identity,
T_E_EMP_ID D_N_ID null,
EMP_NOM D_A_NOM not null,
EMP_MATRICULE D_A_MATRICULE not null,
constraint PK_T_E_EMPLOYE_EMP primary key (EMP_ID)
NONCLUSTERED
)
go
Thanks a lot
FB

[TomV]

You can also edit your current DBMS to alter how the PK and options are
generated.

To alter the PK create template, Edit your DBMS (Database->Edit Current
DBMS) and find the following:
Script->Objects->PKey->Create - Command for adding a primary key

In V10 you have to use the options and you can move the
clustered/nonclustered after the columns.

Change from this:
[%USE_SP_PKEY%?[%R%?[exec ]][execute ]sp_primarykey
[%QUALIFIER%?'%QUALIFIER%%TABLE%':%.q:TABLE%], %PKEYCOLUMNS%
:alter table [%QUALIFIER%]%TABLE%[%R%?[ with nocheck]]
add [constraint %CONSTNAME%] primary key [%CLUSTER%] (%PKEYCOLUMNS%)
[%CLUSTER%]
[%OPTIONS%]]
To this:
[%USE_SP_PKEY%?[%R%?[exec ]][execute ]sp_primarykey
[%QUALIFIER%?'%QUALIFIER%%TABLE%':%.q:TABLE%], %PKEYCOLUMNS%
:alter table [%QUALIFIER%]%TABLE%[%R%?[ with nocheck]]
add [constraint %CONSTNAME%] primary key (%PKEYCOLUMNS%) [%CLUSTER%]

[%OPTIONS%]]

For V11 you can make this change. This will explicitly put the string
"clustered" or "nonclustered" in the
statement BEFORE the columns (just move beyond the (%PKEYCOLUMNS%) if
you need it after the columns).

Change from this:
[%USE_SP_PKEY%?[%R%?[exec ]][execute ]sp_primarykey
[%QUALIFIER%?'%QUALIFIER%%TABLE%':%.q:TABLE%], %PKEYCOLUMNS%
:alter table [%QUALIFIER%]%TABLE%[%R%?[ with nocheck]]
add [constraint %CONSTNAME% ]primary key [%CLUSTER%?[%R%?[clustered
]]:nonclustered ](%PKEYCOLUMNS%)
[%OPTIONS%]]
To this:
[%USE_SP_PKEY%?[%R%?[exec ]][execute ]sp_primarykey
[%QUALIFIER%?'%QUALIFIER%%TABLE%':%.q:TABLE%], %PKEYCOLUMNS%
:alter table [%QUALIFIER%]%TABLE%[%R%?[ with nocheck]]
add [constraint %CONSTNAME% ]primary key [%CLUSTER%?clustered
:nonclustered ](%PKEYCOLUMNS%)
[%OPTIONS%]]



Disclaimer: When you edit your DBMS file, strange things can happen.
Make sure to keep a back up of your original. You also have to make
sure everyone using the model has access to the new DBMS file.

Hope this helps.