1

Is it possible for a memory optimized table in SQL server to have part of its data in memory and the rest on disk?

I have a requirement to load the last 3 months' data into memory, and the rest of it isn't really required to be in memory because it will not be queried.

Is there any way to do this with memory optimized tables? If not, is there any alternate way to do this?

Improve this question
2
  • 2
    Have you considered partitioning the table, frequently accessed partitions will reside in the data cache without requiring memory optimized tables. Commented Feb 21, 2017 at 13:25
  • 1
    @SteveFord Frequently accessed partitions will only remain in cache unless they are evicted due to memory pressure. There is no longer any guarantee of anything remaining in hot buffers since DBCC PINTABLE was removed. The only option that provides a guarantee is Memory-Optimized Tables. It's a well-accepted pattern at this point to have a strategy where hot data is in Memory-Optimized tables, and so-called 'cold' data is on disk. A UNION ALL view, similar to what we had with 'partitioned views' way back when, ties the data together. Application logic can also do this. Commented Feb 19, 2018 at 12:35

2 Answers 2

Reset to default
2

Use a view to union an in-memory table with a standard table (a partitioned view). Run a maintenance process to move data from the in-memory table to the standard table as needed.

You can add check-constraints to the standard table to help eliminate it from the query if that data will not be touched.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This answer is the way to go. learn.microsoft.com/en-us/sql/relational-databases/…
0

The database can have few tables on disk and few in memory,but it is not possible to have ,some of table data in disk and some data in memory

I have a requirement to load the last 3 months' data into memory, and the rest of it isn't really required to be in memory because it will not be queried

why not archive the table regularly ,so it retains only three months data and optimize it for In-memory usage..

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.