SQL Server Portal

In my earlier article, I explained how to create memory optimized table. However, once I created memory optimized tables, I came across a problem segregating the disk based tables, file tables & memory optimized tables.

SQL Server Hekaton comes to the rescue with an addition of a field(column) namely is_memory_optimized in sys.tables (system table) which facilitates segregation. Given below is the script.

USE hkNorthwind
GO
SELECT
object_id
, name  As [Table Name]
,(CASE WHEN is_filetable=0 AND is_memory_optimized=0
THEN 'DISK BASED TABLE'
WHEN is_filetable=1
THEN 'FILE TABLE'
WHEN is_memory_optimized=1
THEN 'MEMORY OPTIMIZED TABLE' END)
AS [Table Type]
FROM sys.tables
--OUTPUT

Note : You can download the hkNorthwind database from here.

Let me know if you know a better solution.