SQL SERVER – Filter In-Memory OLTP Tables in SSMS

Earlier I had written a blog about SQL SERVER – Beginning In-Memory OLTP with Sample Example which covers the basics of working with In-Memory OLTP. Though that post gets you started, one of my colleague asked me if there was an easier way to identify In-Memory Tables when working with SQL Server Management Studio. Also my friend said, their database had 1000’s of tables and it will be impossible to look at any icon difference or something that is tough to see.

This statement got me thinking and I wanted to give him few options. On exploring SSMS, you will be surprised all these exist right in front of our eyes.

T-SQL Way

The simplest solution here was to use the standard system table and query for just the In-Memory tables. The below query will output only In-Memory tables present in the selected database where this query is being run.

SELECT * FROM sys.tables
WHERE is_memory_optimized = 1

Though these age old methods exits, these still don’t help us much when it comes to visualizing the same in SSMS.

Using Object Explorer

Method 2 is using the Object explorer -> Filter settings. In SQL Server Management Studio of SQL Server 2014, we get a nice little option to filter based on In-Memory tables. Right click the Tables node and select Filter Settings. Now in this Dialog we have additional settings that are useful.

Using Object Explorer Details

The 3^rd and simple way is to use the Object Explorer Details (F7) inside SQL Server 2014 SSMS. On the header, right click to add “Memory Optimized” Column. This will show True or False values. Sort on this column and we get all the In-Memory Optimized tables at the top.

I thought these were simple yet powerful tip when working with these special tables inside SQL Server 2014. Do let me know if you have any other method other than this to filter out in-Memory Optimized tables inside SSMS.

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