Sql-server – Use Natively-Compiled Stored Procedures in disk-based tables

memory-optimized-tablessql server

Is it completely worthless to use a Natively-Compiled Stored Procedure over disk-based tables? (SQL Server 2016)

Should all the queries inside a Natively-Compiled Stored Procedure point only to in-memory tables? I ask, because I am inserting and updating table T1, but the source for such tasks are disk-based tables.

Best Answer

Natively-compiled stored procedures can only reference:

  • in-memory tables
  • in-memory table variables / TVPs
  • in-memory natively compiled inline TVFs

Sounds like what you want is interop, and that can't be done inside a natively-compiled procedure. If it could, you would be discarding much of the performance benefits of the natively-compiled procedure.

This article is a couple of years old but not much about the core architecture has changed in 2016 / 2017:

Related Question