Sep 26, 2013

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. 

1 comment:

  1. Anonymous11/30/2014

    thanks, man.
    it would be cool if you could lay out a script for remove memory optimized function, because tables do not need to be removed, just "memory optimized" function

    ReplyDelete