SQL Server token column indexes and null values - heartcombo/devise GitHub Wiki

By default, the Devise migration generator creates a unique index for each token field, like so:

add_index :users, :reset_password_token, unique: true

On SQL Server this index blocks the creation of more than one user because SQL Server treats null values as part of the index by default and, thus, only one row can have the token value set to null.

The fix for this on SQL Server 2008+ is to add a where clause to the index before running the migration:

add_index :users, :reset_password_token, unique: true, where: 'reset_password_token is not null'

This will effectively exclude null values from being part of the unique index.