Recently, I started working on a project to migrate all disk based tables to InMemory and regular stored procedures to native compiled stored procedures.
While migrating stored procedures, I encountered several issues, that needed code changes. The native compilation advisor will alert on the issues that needs to be addressed before making the stored procedure native compiled.
This is not complete list but some of the issues I came across in my project
1. Cannot use Select * .
2. Cannot refer user defined functions.
3. Cannot refer tables without schema name.
4. Cannot use CASE expression, Choose, IIF.
5. Cannot use functions such ‘%’,Like.
6. Cannot have debug statements like Print.
7. Cannot have Create\Drop\Truncate Table.
8. Cannot use table variables.(use inmemory table variable)
9. Cannot refer objects in other databases.
10. Cannot use Linked Server Queries.
11. Cannot have explicit Begin Transaction\Commit\Rollback.
12. Lock Hints are not allowed.
I will add more exceptions here as I come across them.