SQL Server 2014: Triggers on Memory Optimized Tables

SQL Server 2014 CTP1 won’t support triggers on memory optimized tables. In the SQL Server 2014 Management Studio, if you go to object explorer and expand the memory optimized table in the tree, you wont see the folder for Constraints and Triggers. This is one among the difference between the regular table and memory optimized table. Also a disadvantage on using memory optimized table.

In the below screen shot, the table MyTecBits_Names_1 is a memory optimized durable table. MyTecBits_Names_2 is a memory optimized non-durable table. If you notice, there is no folder for Constraints or Triggers. But under the regular tables MyTecBits_Names_3 and MyTecBits_Names_4, the folders for constraints and triggers are available.

SQL-Server-2014 Triggers on Memory Optimized Tables

To test this, If you try to create a trigger on memory optimized table, you will get an error “Msg 10770, The operation ‘CREATE TRIGGER’ is not supported with memory optimized tables.“. Below is the screen shot of the error I got, when I tried to create a trigger on memory optimized table.

SQL-Server-2014-Trigger-On-Memory-Table-02

If you want to test creating trigger on memory optimized table,you have to create a database on SQL Server 2014 optimized for In-Memory OLTP. Then create memory optimized tables.

NOTE: This article is based on SQL Server 2014 CTP1. There may be changes on the upcoming manufacturing release version (RTM) of SQL Server 2014.

Reference: msdn.


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.