[hibernate-issues] [Hibernate-JIRA] Created: (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

[Jim Doyle (JIRA)]

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: Hibernate3
          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