web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / difference of scope in...
Microsoft Dynamics AX (Archived)

difference of scope in between In memory and temp table in ax 2012

(0) ShareShare
ReportReport
Posted on by rohit1900 439

Hi all,

I have doubt regarding the scope for in memory and temp table and i have gone through some document but didn't understand the basic difference  in scope for the both.

In Memory table is de-allocated as soon as the record buffer goes out of scope.

All types of temporary tables are automatically dropped by the system when the table variable in X++ goes out of scope. A TempDB table is NOT dropped when you set its record buffer variable to null.

Please make me understand this small thing in ax 2012.

*This post is locked for comments

I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 237,959 Most Valuable Professional on at

    Hmm, does it mean that you don't believe the statement you quoted ("All types of temporary tables are automatically dropped by the system when the table variable in X++ goes out of scope")?

  • Suggested answer
    guk1964 Profile Picture
    guk1964 10,888 on at

    A local SQL Server temp table is only visible to the current session. It cannot be seen or used by processes or queries outside of the session in which it  is declared.

    SQL Server temp tables are useful e.g. when  there is a loop of some sort. For example, to process data for a SQL statement when you need a place to store items for your loop to read through. It provides a quick and efficient means to do so. Another  use for SQL Server temp tables is a demanding processi in your sql statement. You create a join, and every time you need to pull records from that result set it has to process this join again. So process this result set once and throw the records into a SQL temp table then you can have the rest of the sql statement refer to the SQL temp table name. Not only does this save on expensive query processing, but it will make your code cleaner.

    Many believe that table variables exist only in memory, but that is simply not true. They reside in the tempdb database much like local SQL Server temp tables. Also like local SQL temp tables, table variables are accessible only within the session that created them. However, unlike SQL temp tables the table variable is only accessible within the current batch. They are not visible outside of the batch, meaning the concept of session hierarchy can be somewhat ignored.

    As far as performance is concerned table variables are useful with small amounts of data (like only a few rows). Otherwise a SQL Server temp table is useful when sifting through large amounts of data. So for most scripts you will most likely see the use of a SQL Server temp table as opposed to a table variable. Not to say that one is more useful than the other, it’s just you have to choose the right tool for the job.

    See also https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/faster-temp-table-and-table-variable-by-using-memory-optimization?view=sql-server-2017

  • rohit1900 Profile Picture
    rohit1900 439 on at

    I understand  that for the temporary tables but how is it different again from the in memory table ("In Memory table is de-allocated as soon as the record buffer goes out of scope.");

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 237,959 Most Valuable Professional on at

    The same is true for TempDB tables - let me remind you the statement once more: "All types of temporary tables are automatically dropped when the table variable in goes out of scope"

    "All types of temporary tables" means InMemory and TempDB.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans