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
1 comment:
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
Post a Comment