Grails and MS-SQL Server

When using Microsoft SQL Server as the back-end database for a Grails project, the default hibernate mappings create primary key id columns of type numeric (which can be annoying).

This is because the MSSQL server Dialect is designed to support MSSQL Server versions older than 2000. MSSQL Server 2000 and later has support for BIGINTs, and therefor doesn't need to store it's primary keys in numeric columns.

The fix for this is to create a new SQL Server Dialect for SQL Server 2000 and later. We do this by creating the following file in our Grails project

src/java/org/hibernate/dialect/SQLServer2000Dialect.java

Containing the following code

package org.hibernate.dialect;
import java.sql.Types;

public class SQLServer2000Dialect extends SQLServerDialect {
 public SQLServer2000Dialect() {
  super();
  registerColumnType(Types.BIGINT, "bigint");
  registerColumnType(Types.BIT, "bit");
 }
}

Then we edit our DataSource.groovy that's located under grails-app/conf so that it uses our new dialect.

dialect = "org.hibernate.dialect.SQLServer2000Dialect"

And wala! Our primary keys are bigints!

What this also means is that grails will also set the primary key to be identity columns rather than the not quite as useful hilo algorithm.