If you’re looking to implement the in memory OLTP tables in SQL Server 2014, one of the biggest drawbacks for the same is the fact that tables need to be dropped and recreated , they cannot be altered. While the official documentation simply calls this out and stops there. A simple and effective solution is available for those who are looking. Say you create an inmem table called T1 and during the next release cycle you are looking to modify the table and say add a new column. While you cannot modify the existing table you can create a new table called T2 and then assign a synonym called T1 that internally calls the table T2.
Sure this approach won’t work for natively compiled procedures since they won’t allow you to drop the table T1 until the procedure itself is dropped but for large in mem tables this approach is a quick and easy way to populate and keep ready the deployed table and then perform an almost seem less deployment rather and create a new table and repopulate the data.
Please Consider Subscribing
