{"id":11368,"date":"2019-04-06T20:45:40","date_gmt":"2019-04-06T20:45:40","guid":{"rendered":"https:\/\/dbtut.com\/?p=11368"},"modified":"2019-04-06T20:45:40","modified_gmt":"2019-04-06T20:45:40","slug":"what-is-natively-compiled-stored-procedure-in-sql-server","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/","title":{"rendered":"What is Natively Compiled Stored Procedure in SQL Server"},"content":{"rendered":"

The Natively Compiled Stored Procedure is a new type of stored procedure offered by SQL Server 2014 with In Memory OLTP. Available in Memory Optimized Tables only. I suggest you read the article “In Memory OLTP in SQL Server<\/a>” for “In Memory OLTP” and “Memory Optimized Tables”.<\/p>\n

Difference Between Stored Procedure and Natively Compiled Stored Procedure<\/strong><\/p>\n

In the Natively Compiled Stored Procedure, TSQL codes in the Procedure are converted to native code to provide better performance.<\/p>\n

In the article “In Memory OLTP in SQL Server<\/a>“, we have said that we can achieve up to 30x more performance with memory optimized tables than disk based tables.<\/p>\n

When we use Memory Optimized Tables in combination with the Natively Compiled Stored Procedure, this difference increases. I suggest you test to see the speed. I will share the necessary code.<\/p>\n

The normal stored procedures we know are compiled when they first run. Therefore, they can receive errors during the first run even if they do not receive errors during creation.<\/p>\n

Because Natively Compiled Stored Procedures are compiled when they are created, you cannot create if there is an error.<\/p>\n

Create Natively Compiled Stored Procedure<\/strong><\/p>\n

If you want to see how fast nativel compiled stored procedure is, you should test it with the below script.<\/p>\n

The script creates a natively compiled stored procedure that insert 50000 records to the table that I have created in the article “In Memory OLTP in SQL Server<\/a>”<\/p>\n

USE [Test]\r\nGO\r\nCREATE PROCEDURE NativelyCompiledSP_Example\r\nWITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER\r\nAS\r\nBEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')\r\nDECLARE @i INT = 0\r\nWHILE @i < 50000\r\nBEGIN\r\nINSERT INTO dbo.MemoryOptimizedTable_Example VALUES (@i,N'My_Name')\r\nSET @i += 1\r\nEND\r\nEND\r\nGO\r\n\r\n--EXEC NativelyCompiledSP_Example\r\n--SQL Server 2016 does not require EXECUTE AS OWNER.\r\n<\/pre>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
The Natively Compiled Stored Procedure is a new type of stored procedure offered by SQL Server 2014 with In Memory OLTP. Available in Memory Optimized Tables only. I suggest you read the article “In Memory OLTP in SQL Server” for “In Memory OLTP” and “Memory Optimized Tables”. Difference Between Stored Procedure and Natively Compiled Stored …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":11370,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3],"tags":[3168,3173,3171,3169,3177,3176,3174,3178,3175,3172,3179,3180,3170],"class_list":["post-11368","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-mssql","tag-create-natively-compiled-stored-procedure","tag-difference-between-natively-compiled-stored-procedure-and-stored-procedure","tag-difference-between-stored-procedure-and-native-stored-procedure","tag-difference-between-stored-procedure-and-natively-compiled-stored-procedure","tag-how-to-create-native-stored-procedures","tag-how-to-create-natively-compiled-stored-procedures","tag-native-compilation-in-sql-server","tag-native-modules","tag-native-stored-procedure","tag-native-stored-procedure-sql-server","tag-natively-compiled-modules","tag-what-is-natively-compiled-modules","tag-what-is-natively-compiled-stored-procedure"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":310,"today_views":0},"yoast_head":"\nWhat is Natively Compiled Stored Procedure in SQL Server - Database Tutorials<\/title>\n<meta name=\"description\" content=\"What is Natively Compiled Stored Procedure in SQL Server\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"What is Natively Compiled Stored Procedure in SQL Server - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"What is Natively Compiled Stored Procedure in SQL Server\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-04-06T20:45:40+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-17.png\" \/>\n\t<meta property=\"og:image:width\" content=\"721\" \/>\n\t<meta property=\"og:image:height\" content=\"396\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"What is Natively Compiled Stored Procedure in SQL Server\",\"datePublished\":\"2019-04-06T20:45:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/\"},\"wordCount\":248,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-17.png\",\"keywords\":[\"Create Natively Compiled Stored Procedure\",\"Difference between Natively Compiled Stored Procedure and Stored Procedure\",\"difference between stored procedure and native stored procedure\",\"Difference Between Stored Procedure and Natively Compiled Stored Procedure\",\"How to create Native stored procedures\",\"How to create Natively compiled stored procedures\",\"native compilation in sql server\",\"native modules\",\"native stored procedure\",\"native stored procedure sql server\",\"Natively compiled modules\",\"what is Natively compiled modules\",\"What is Natively Compiled Stored Procedure\"],\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/\",\"name\":\"What is Natively Compiled Stored Procedure in SQL Server - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-17.png\",\"datePublished\":\"2019-04-06T20:45:40+00:00\",\"description\":\"What is Natively Compiled Stored Procedure in SQL Server\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-17.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-17.png\",\"width\":721,\"height\":396},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"What is Natively Compiled Stored Procedure in SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"What is Natively Compiled Stored Procedure in SQL Server - Database Tutorials","description":"What is Natively Compiled Stored Procedure in SQL Server","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"What is Natively Compiled Stored Procedure in SQL Server - Database Tutorials","og_description":"What is Natively Compiled Stored Procedure in SQL Server","og_url":"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/","og_site_name":"Database Tutorials","article_published_time":"2019-04-06T20:45:40+00:00","og_image":[{"width":721,"height":396,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-17.png","type":"image\/png"}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"What is Natively Compiled Stored Procedure in SQL Server","datePublished":"2019-04-06T20:45:40+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/"},"wordCount":248,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-17.png","keywords":["Create Natively Compiled Stored Procedure","Difference between Natively Compiled Stored Procedure and Stored Procedure","difference between stored procedure and native stored procedure","Difference Between Stored Procedure and Natively Compiled Stored Procedure","How to create Native stored procedures","How to create Natively compiled stored procedures","native compilation in sql server","native modules","native stored procedure","native stored procedure sql server","Natively compiled modules","what is Natively compiled modules","What is Natively Compiled Stored Procedure"],"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/","url":"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/","name":"What is Natively Compiled Stored Procedure in SQL Server - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-17.png","datePublished":"2019-04-06T20:45:40+00:00","description":"What is Natively Compiled Stored Procedure in SQL Server","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-17.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-17.png","width":721,"height":396},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/06\/what-is-natively-compiled-stored-procedure-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"What is Natively Compiled Stored Procedure in SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/11368","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=11368"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/11368\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/11370"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=11368"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=11368"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=11368"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}