[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3305) hbm2ddl: MS SQL Server 2005 defaults to Clustered Index on PK, lets use PRIMARY KEY NONCLUSTERED for hbm2ddl as a more reasonable default

[j nadler (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3305?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=38669#action_38669 ] 

j nadler commented on HHH-3305:
-------------------------------

I agree entirely - we're seeing this same problem.  Many users are probably not sophisticated enough with SQL Server to realize what a problem this can create.

I looked through the source.   Here's what needs to happen IMO:

PrimaryKey.java:   The string "primary key" at line 38 should not be hardcoded here; instead it should make a call to Dialect.

Dialect.java:  Add a new method "getCreateTablePrimaryKeyConstraintString" and in the implementation just return "primary key".

SQLServerDialect.java:   Override "getCreateTablePrimaryKeyConstraintString" to return "primary key nonclustered"

Sound reasonable?

> hbm2ddl:  MS SQL Server 2005 defaults to Clustered Index on PK,  lets use PRIMARY KEY NONCLUSTERED for hbm2ddl as a more reasonable default
> -------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: HHH-3305
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3305
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: metamodel
>    Affects Versions: 3.2.1
>            Reporter: Jim Doyle
>
> In SQL Server 2005,  PRIMARY KEY defaults to creating a Clustered Index for the PK column at table creation time.
> To confirm this behaviour Please see:   http://msdn.microsoft.com/en-us/library/ms188066.aspx
> This default behaviour is un-desireable for a number of reasons:
> 1.  No other databases default  to a clustered IDX at the PK ; Not DB2, nor Oracle nor MySQL
> 2.  There may only be ONE clustered IDX per table.  By using this as a default makes it difficult
> for the app developer or DBA to quickly add or change the once-per-table opportunity to use
> the cluster to improve query performance.
> 3.  The default cluster index that Microsoft applies seems only to benefit naive SQL server users with 
> little to no DBA skills. Table insert of new rows onto a Clustered Index PK is always very fast and
> while this optimization may be impressive if your problem space is rapidly inserting new data into large tables,
> this optimization is often an antipattern to users with more plausible query patterns such as scan by non-primary
> key date, numeric amounts or alphabetical data.
> 4.  Defaulting the PK to clustered creates SUBSTANTIAL burden for a DBA when the Clustered Index needs to be removed
> from the PK to be deployed on other columns on the table. As an example of the burden that this creates, for EACH TABLE
> one must:
>      - Discover all tables and columns that link back to the table in question through FOREIGN KEY
>      - Drop all foreign keys
>      - Drop the PK
>      - Add the PK back, with NONCLUSTERED
>      - Put back all the dropped Foreign keys
> 5. Additional arguments against supporting Microsoft's unusual default is given here:
> http://tonesdotnetblog.wordpress.com/2008/05/26/twelve-tips-for-optimising-sql-server-2005-queries
> (Please See Section 2: Use Clustered Index).
> I propose ANY ONE of the  following enhancements to the DDL generator for the SQL Server 2005 Dialect:
> 1.  Change the generated SQL from  colname coltype PRIMARY KEY    TO      colname coltype PRIMARY KEY NONCLUSTERED such that
> PK index generate conforms the the usual, sensible defaults of other database products.
> 2.   Use a Properties attribute (i.e. hibernate.hbm2ddl.dialect.mssql.use_non_clustered_pk) that is evaluated at hbm2ddl runtime 
> that will emit 'NONCLUSTERED' to the PK clause. If this Property is not defined, the hbm2ddl will simply emit PRIMARY KEY as it does
> now and MS SQL Server will use its peculiar and annoying default.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira