SQL SERVER – Identifying If Database Supports InMemory OLTP Functionality

I had recently received an email about InMemory OLTP Functionality in Email when I was having a vacation with my family in the Australia. Let us read the story about my vacation and SQL Server in this blog post.

My Recent Vacation

I am a workaholic person in general, but I make sure there is enough dedicated time that my family gets from time to time. I make sure there is a good family time spent when my daughter has her vacations. This time I had the liberty to take her to Australia and it was an awesome experience for us as a family. I want to visit again and move around the city much more leisurely.

In every such trip, I have my own daughter time where I take her for a stroll through the market and we buy things that look interesting. This time I found she was getting interested in a toy of shapes. This was nothing strange because she had one and was insisting to get this new one because the old blocks had broken. Since it was her time, I obliged and got her the same. Once we reached home, she was all gaga to play with her new toy. Interestingly, she also searched her toy box to get a few parts from the old blocks. Soon she realized these two – though looked the same didn’t match. Disappointed yet she learned a new lesson. Not all toys are compatible with each other. These were not Lego blocks.

Let us Get Back to SQL

It was time for me to get through the long list of emails which were waiting to be answered. Just like how my daughter identified things are always not the same. I found an interesting mail that stated that some of my InMemory OLTP scripts were not working in their environment.

This got me into an investigation with the developer and finally this is what I had to send him as part of my analysis. There were fundamentally two observations that I had to send him:

• A new database cannot support In-Memory OLTP if it is restored from a database that was created before the In-Memory OLTP features became active.
• When in doubt, you can always run the following T-SQL SELECT to ascertain whether your database supports In-Memory OLTP. A result of 1 means the database does support In-Memory OLTP:

SELECT DatabasePropertyEx(DB_NAME(), 'IsXTPSupported');
GO

The developer after running the query got back to me stating there was something wrong in his environment and it was not supporting InMemory OLTP objects. After he tried the same on a different database, things started to work. Though this script was small, it did help in identifying the problem.

I am curious to know; how many have had the need to use InMemory OLTP Functionality in your environments? What did you do differently to get into this problem? Let me know.

Reference: Pinal Dave (https://blog.sqlauthority.com), YouTube