With SQL Server 2014 Microsoft has introduced Memory Optimized table which is also known as In-Memory OLTP. As it is named, the memory optimized table lives in the memory of the server and all the records are read from and written to the memory. There will be a copy of this table onto the disk as well but that’s for the durability purpose. As soon as it was released it was an instant hit because it will give you a very good performance boost. Many of us have implemented In-Memory and converted the disk based tables to In-Memory tables, this was easy. In this post I will discuss how to convert In-Memory table to disk based table.
There are many who have implemented and deployed SQL Server 2014 in production to use memory optimized tables, and in early days there were lots of issues around with xtp_checkpoint, where the log file grows exponentially and it doesn’t allow to truncate log files when you check log_reuse_wait_desc it shows xtp_checkpoint. Xtp_checkpoint issue has been fixed with SQL Server 2014 SP1 CU4. There are lots of document available on how to implement In-Memory or convert the disk based table into the memory optimized table, however, there is only one documentation available on how to convert the In-Memory table to disk based table.
There can be several cases where this requirement may come, and you will need to do this exercise. It may sound like a big task but it is fairly simple unless you have a list of multiple tables to convert. You may need to really read the link because that is the link that will give you the list about the TSQL constructs that are not supported with In-Memory tables, actually, for SQL Server 2014 this has made it easy to convert In-memory tables to disk based table. This list of not supported constructs has been changed in SQL Server 2016 and your case may very.
How to convert In-Memory table to disk based table
In my case, it was still SQL Server 2014, and the customer wanted the In-Memory table to be converted back to disk based table. I haven’t worked on this kind of assignment prior so I was doing research and bumped up to an article on SQLMAG, as mentioned in an article I was able to convert the In-memory table to disk based table with the help of SELECT INTO statement. Once the table is converted to the disk based table you need to drop the old table and rename the newly created table with the original name of the table you’ve just converted.
This was great because, in SQL Server 2014, many constraints were not supported and if you have to do this task for a single or couple of tables it’s easy. However, the same task may be tedious if you have to do it for multiple tables manually. I was doing a research for some script that can help with copying all the data from a table to another table along with all the constraints and indexes and I found an interesting thread on Stackexchange where Michael Freidgeim has shared his script and the updated and uploaded at github. This is really a beautiful script that can help you when you will have to copy all the existing data along with the constraints and indexes. And that script can help you convert your memory optimized table to disk based table even if you are on the SQL Server 2016 as it supports and copy constraints and indexes both; well you will need to use replace function because the create index statement will have HASH keyword which is not supported disk based table.
Let me know if you have done this kind of assignment and how was your experience.
