I am trying to rewrite a stored procedure to a natively compiled stored procedure. The procedure refers to 4 tables; these tables are normal tables (non memory-optimized) and I am using 2 views related to it.
Is it possible to create a natively compiled stored procedure without converting any of these objects to memory-optimized tables?
No, natively compiled stored procedures cannot reference disk-based tables, only memory-optimized ones. Here is a quote from Kalen Delaney's book SQL Server Internals: In-Memory OLTP:
However, there are limitations on the T-SQL language constructs that are allowed inside a natively compiled stored procedure, compared to the rich feature set available with interpreted code. In addition, natively compiled stored procedures can only access memory-optimized tables and cannot reference disk-based tables.
I could not find clear statement in the official Microsoft Docs for that, except this limitation, which is direct consequence of the fact, that natively compiled stored procedures can't reference "normal" tables:
For memory-optimized tables, FOREIGN KEY constraints are only supported for foreign keys referencing primary keys of other memory-optimized tables.
FROM clause limits specified here is also a pretty big clue.
