As always I used to create ordinary stored procedures regarding the second option for creating stored procedure called "Natively Compiled Stored Procedure" I wanna know if there is any performance benefit to using compiled one?
Is there anyone can Help to find out? Thanks
Stored procedures that are marked with NATIVE_COMPILATION are natively compiled. This means the Transact-SQL statements in the procedure are all compiled to native code for efficient execution of performance-critical business logic.
For more information, see Native Compilation of Tables and Stored Procedures