How is it different from normal Stored Procedure?
A natively compiled stored procedure is compiled when it is created, rather than when it is executed.
That compile process first translates the store procedure code into “C”, and then into machine language and stores the code as a DLL.
Please find a good article about Natively Compiled Stored Procedure with example: http://www.databasejournal.com/features/mssql/natively-compiled-stored-procedures-with-sql-server-2014.html
Sample Code to create Natively compiled Stored procedure:
CREATE PROC NativelyCompiledInsertTest WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') DECLARE @I int = 1; DECLARE @X float; WHILE @I < 1000000 BEGIN INSERT INTO dbo.InMemoryTest VALUES (@I, @I + 1 * @I * log(@I) + tan (@I) + rand() * cos(@I)); INSERT INTO dbo.InMemoryTest VALUES (@I + 1, @I + 1 * @I * log(@I) + tan (@I) + rand() * cos(@I)); SET @I += 2; END END GO