Modifying the collation of a database is not allowed when the database contains memory optimized tables or natively compiled stored procedures

If we will try to change the default collation of memory optimized database in sql server 2014. For example:

ALTER DATABASEMSSQL2014

COLLATE Latin1_General_100_BIN2

We may get following error message:

Msg 41335, Level 16, State 3, Line 20

Modifying the collation of a database is not allowed when the database contains memory optimized tables or natively compiled stored procedures.

Cause: It is limitation of sql server 2014 memory optimized database that we cannot change the default collation of database after creating it.

Solution: Drop the database and recreate the memory optimized table with your default collation. For example:

Using following script get all memory optimized tables and natively complied stored procedures:

SELECT

     SCHEMA_NAME([schema_id]) AS SchemaName,

     name AS TableName

FROM sys.tables

WHERE is_memory_optimized = 1

SELECT

     SCHEMA_NAME([schema_id]) AS SchemaName,

     name AS ProcedureName

FROM sys.procedures

WHERE OBJECTPROPERTY([object_id],'Isschemabound') = 1

By using the SSMS generate the create scripts of tables and procedures and deletes the memory optimized table and natively complied procedures.

Now alter the database:

ALTER DATABASEMSSQL2014

COLLATE Latin1_General_100_BIN2

Now it will work fine.