Keep tables in memory

It is now possible to keep a table into memory. There are three options: No, Yes (durable) and Yes (transient). The option Yes (transient) is only available for SQL server projects. For more information about memory optimized tables in SQL server, see microsoft documentation (https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/memory-optimized-tables?view=sql-server-2017)

For DB2 projects, the property KEEPINMEM will be used when a table has Yes (durable). For more information about this property, see IBM documentation (https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/cl/chglf.htm) For Oracle project, the option CACHE will be used when a table has Yes (durable).

For more information about this option, see Oracle documentation (https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7002.htm#i2215507)

Be aware that there are - at least for SQL Server - and depending on the version of SQL server you are using, quitte a few limitations when using in-memory tables. I do support using them, but thorrough regression testing is required when implementing them.

Check here, for example: https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transact-sql-constructs-not-supported-by-in-memory-oltp?view=sql-server-2017

Ester wrote:

It is now possible to keep a table into memory. There are three options: No, Yes (durable) and Yes (transient). The option Yes (transient) is only available for SQL server projects. For more information about memory optimized tables in SQL server..

 

Can you explain the difference between Yes (durable) and Yes (transient)? 

 

In database systems, durability is the ACID property which guarantees that transactions that have committed will survive permanently. For example, if a flight booking reports that a seat has successfully been booked, then the seat will remain booked even if the system crashes.

https://en.m.wikipedia.org/wiki/Durability_(database_systems)

 

Yes (transient) is only stored in memory, so if the server crashes you loose your data. While with durable, the data is stored and will be loaded in memory again. You can use transient for example, when the data is calculated based on other tables.