[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

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-optimisin... (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.