hbm2ddl: MS SQL Server 2005 defaults to Clustered Index on PK, lets use PRIMARY KEY NONCLUSTERED for hbm2ddl as a more reasonable default
Description
Activity
Brett MeyerJuly 8, 2014 at 3:11 PM
Bulk rejecting stale issues. If this is still a legitimate issue on ORM 4, feel free to comment and attach a test case. I'll address responses case-by-case. Thanks!
Brett MeyerApril 7, 2014 at 5:45 PM
In an effort to clean up, in bulk, tickets that are most likely out of date, we're transitioning all ORM 3 tickets to an "Awaiting Test Case" state. Please see http://in.relation.to/Bloggers/HibernateORMJIRAPoliciesAndCleanUpTactics for more information.
If this is still a legitimate bug in ORM 4, please provide either a test case that reproduces it or enough detail (entities, mappings, snippets, etc.) to show that it still fails on 4. If nothing is received within 3 months or so, we'll be automatically closing them.
Thank you!
TomTJuly 29, 2011 at 10:57 PM
This has been sitting for some time. Any word on scheduling this for a Hibernate release?
JeffJOctober 10, 2010 at 9:05 PM
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?
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.