Tables in memory and tables normal on procedure stored using SQL Server
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?
sql
sql-server in-memory-database edited Nov 16 '18 at 5:53
marc_s
581k13011211268
asked Nov 15 '18 at 22:56
LucasLucas
82
add a comment |
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?
sql
sql-server in-memory-database edited Nov 16 '18 at 5:53
marc_s
581k13011211268
asked Nov 15 '18 at 22:56
LucasLucas
82
1
Is there a question?
– Andrew
Nov 15 '18 at 22:57
Ok, I'll do my best
– Caius Jard
Nov 15 '18 at 23:12
I am trying to rewrite a stored procedure to Natively compiled stored procedure. The procedure refers to 4 tables, these tables are normal tables (non memory optimised) 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 optimised tables?
– Lucas
Nov 15 '18 at 23:16
@Lucas, when adding details that affect the question, please edit the question rather than leaving a comment. Comments can be ephemeral.
– KevinO
Nov 15 '18 at 23:53
add a comment |
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?
sql
sql-server in-memory-database edited Nov 16 '18 at 5:53
marc_s
581k13011211268
asked Nov 15 '18 at 22:56
LucasLucas
82
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?
sql
sql-server in-memory-database sql
sql-server in-memory-database edited Nov 16 '18 at 5:53
marc_s
581k13011211268
asked Nov 15 '18 at 22:56
LucasLucas
82
edited Nov 16 '18 at 5:53
marc_s
581k13011211268
asked Nov 15 '18 at 22:56
LucasLucas
82
edited Nov 16 '18 at 5:53
marc_s
581k13011211268
edited Nov 16 '18 at 5:53
marc_s
581k13011211268
edited Nov 16 '18 at 5:53
marc_s
581k13011211268
581k13011211268
asked Nov 15 '18 at 22:56
LucasLucas
82
asked Nov 15 '18 at 22:56
LucasLucas
82
asked Nov 15 '18 at 22:56
LucasLucas
82
82
1
Is there a question?
– Andrew
Nov 15 '18 at 22:57
Ok, I'll do my best
– Caius Jard
Nov 15 '18 at 23:12
I am trying to rewrite a stored procedure to Natively compiled stored procedure. The procedure refers to 4 tables, these tables are normal tables (non memory optimised) 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 optimised tables?
– Lucas
Nov 15 '18 at 23:16
@Lucas, when adding details that affect the question, please edit the question rather than leaving a comment. Comments can be ephemeral.
– KevinO
Nov 15 '18 at 23:53
add a comment |
1
Is there a question?
– Andrew
Nov 15 '18 at 22:57
Ok, I'll do my best
– Caius Jard
Nov 15 '18 at 23:12
I am trying to rewrite a stored procedure to Natively compiled stored procedure. The procedure refers to 4 tables, these tables are normal tables (non memory optimised) 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 optimised tables?
– Lucas
Nov 15 '18 at 23:16
@Lucas, when adding details that affect the question, please edit the question rather than leaving a comment. Comments can be ephemeral.
– KevinO
Nov 15 '18 at 23:53
1
1
Is there a question?
– Andrew
Nov 15 '18 at 22:57
Is there a question?
– Andrew
Nov 15 '18 at 22:57
Ok, I'll do my best
– Caius Jard
Nov 15 '18 at 23:12
Ok, I'll do my best
– Caius Jard
Nov 15 '18 at 23:12
I am trying to rewrite a stored procedure to Natively compiled stored procedure. The procedure refers to 4 tables, these tables are normal tables (non memory optimised) 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 optimised tables?
– Lucas
Nov 15 '18 at 23:16
I am trying to rewrite a stored procedure to Natively compiled stored procedure. The procedure refers to 4 tables, these tables are normal tables (non memory optimised) 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 optimised tables?
– Lucas
Nov 15 '18 at 23:16
@Lucas, when adding details that affect the question, please edit the question rather than leaving a comment. Comments can be ephemeral.
– KevinO
Nov 15 '18 at 23:53
@Lucas, when adding details that affect the question, please edit the question rather than leaving a comment. Comments can be ephemeral.
– KevinO
Nov 15 '18 at 23:53
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Nov 16 '18 at 8:29
Andrey NikolovAndrey Nikolov
4,2633822
Well, theFROMclause limits specified here is also a pretty big clue.
– Damien_The_Unbeliever
Nov 16 '18 at 8:33
Yep, absolutely! :) Thanks for the clarification!
– Andrey Nikolov
Nov 16 '18 at 8:36
@AndreyNikolov Thanks for the reply :)
– Lucas
Nov 16 '18 at 16:29
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53329042%2ftables-in-memory-and-tables-normal-on-procedure-stored-using-sql-server%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Nov 16 '18 at 8:29
Andrey NikolovAndrey Nikolov
4,2633822
Well, theFROMclause limits specified here is also a pretty big clue.
– Damien_The_Unbeliever
Nov 16 '18 at 8:33
Yep, absolutely! :) Thanks for the clarification!
– Andrey Nikolov
Nov 16 '18 at 8:36
@AndreyNikolov Thanks for the reply :)
– Lucas
Nov 16 '18 at 16:29
add a comment |
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.
answered Nov 16 '18 at 8:29
Andrey NikolovAndrey Nikolov
4,2633822
Well, theFROMclause limits specified here is also a pretty big clue.
– Damien_The_Unbeliever
Nov 16 '18 at 8:33
Yep, absolutely! :) Thanks for the clarification!
– Andrey Nikolov
Nov 16 '18 at 8:36
@AndreyNikolov Thanks for the reply :)
– Lucas
Nov 16 '18 at 16:29
add a comment |
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.
answered Nov 16 '18 at 8:29
Andrey NikolovAndrey Nikolov
4,2633822
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.
answered Nov 16 '18 at 8:29
Andrey NikolovAndrey Nikolov
4,2633822
answered Nov 16 '18 at 8:29
Andrey NikolovAndrey Nikolov
4,2633822
answered Nov 16 '18 at 8:29
Andrey NikolovAndrey Nikolov
4,2633822
answered Nov 16 '18 at 8:29
Andrey NikolovAndrey Nikolov
4,2633822
4,2633822
Well, theFROMclause limits specified here is also a pretty big clue.
– Damien_The_Unbeliever
Nov 16 '18 at 8:33
Yep, absolutely! :) Thanks for the clarification!
– Andrey Nikolov
Nov 16 '18 at 8:36
@AndreyNikolov Thanks for the reply :)
– Lucas
Nov 16 '18 at 16:29
add a comment |
Well, theFROMclause limits specified here is also a pretty big clue.
– Damien_The_Unbeliever
Nov 16 '18 at 8:33
Yep, absolutely! :) Thanks for the clarification!
– Andrey Nikolov
Nov 16 '18 at 8:36
@AndreyNikolov Thanks for the reply :)
– Lucas
Nov 16 '18 at 16:29
Well, the
FROM clause limits specified here is also a pretty big clue.– Damien_The_Unbeliever
Nov 16 '18 at 8:33
Well, the
FROM clause limits specified here is also a pretty big clue.– Damien_The_Unbeliever
Nov 16 '18 at 8:33
Yep, absolutely! :) Thanks for the clarification!
– Andrey Nikolov
Nov 16 '18 at 8:36
Yep, absolutely! :) Thanks for the clarification!
– Andrey Nikolov
Nov 16 '18 at 8:36
@AndreyNikolov Thanks for the reply :)
– Lucas
Nov 16 '18 at 16:29
@AndreyNikolov Thanks for the reply :)
– Lucas
Nov 16 '18 at 16:29
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53329042%2ftables-in-memory-and-tables-normal-on-procedure-stored-using-sql-server%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Is there a question?
– Andrew
Nov 15 '18 at 22:57
Ok, I'll do my best
– Caius Jard
Nov 15 '18 at 23:12
I am trying to rewrite a stored procedure to Natively compiled stored procedure. The procedure refers to 4 tables, these tables are normal tables (non memory optimised) 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 optimised tables?
– Lucas
Nov 15 '18 at 23:16
@Lucas, when adding details that affect the question, please edit the question rather than leaving a comment. Comments can be ephemeral.
– KevinO
Nov 15 '18 at 23:53