Without a doubt, the main limitation of Hekaton, at least as of SQL Server 2014, is that tables cannot be altered: a new table with the required changes will have to be created instead. This is the case for any change you want to make to a table, such as adding a new column or a new index or changing the bucket count of a hash index. Obviously, creating a new table would need several other operations as well, such as first copying its data to another location, dropping the table, creating the new table with the needed changes, and copying the data back, which would require some downtime for the application. This limitation will probably be the biggest challenge to deployed applications, which will, of course, demand serious thinking and architecture design to avoid or minimize changes once the required memory-optimized tables are in production.
Because memory is not immediately freed after a table is dropped, additional memory would also be needed even if a regular table is used to temporarily copy this data. A new memory-optimized table could also be used as the temporary table, but this will further increase the required memory.
In addition, dropping and creating a table will usually also imply some other operations, such as scripting all its permissions. And because natively compiled stored procedures are schema bound, this also means that they need to be dropped first before the table can be dropped. That is, you need to script these procedures, drop them, and create them again once the new table is created. Similar to tables, you may need to script the permissions of the procedures as well. Updating statistics with the FULLSCAN option is also highly recommended after the table is created and all the data loaded to help the query optimizer get the best possible execution plan.
You cannot alter natively compiled stored procedures either—or even recompile them (except in a few limited cases, such as when the SQL Server instance is restarted or when a database is put offline and back online). As mentioned previously, to alter a procedure, you will need to script the permissions, drop the procedure, create the new version of the procedure, and apply the permissions again. This means that the procedure will not be available during these steps.
Finally, there are some differences regarding statistics and recompiles in Hekaton compared with disk-based tables and traditional stored procedures. As the data in Hekaton tables changes, statistics are never automatically updated; you will need to manually update them by running the UPDATE STATISTICS statement with the FULLSCAN and NORECOMPUTE options. Also, even after you get your statistics updated, existing natively compiled stored procedures cannot benefit from them automatically, and as mentioned earlier, you cannot force a recompile either. You will have to manually drop and re-create the stored procedures.
Hekaton tables and stored procedures do not support the full T-SQL surface area that is supported by disk-based tables and regular stored procedures. For example, the following features are not supported on memory-optimized tables:
IDENTITY columns are only partially supported. For more details, see http://msdn.microsoft.com/en-us/library/dn247640(v=sql.120).aspx.
- FOREIGN KEY constraints
- CHECK constraints
- DEFAULT constraints
- Computed columns
- DML triggers
- Some data types
Finally, for an entire list of unsupported T-SQL constructs on Hekaton tables and stored procedures, see http://msdn.microsoft.com/en-us/library/dn246937(v=sql.120).aspx.