Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER HEKATON – How to find memory optimized tables in the database’

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

memoryoptimizedtables1.1

Note : You can download the hkNorthwind database from here.

Let me know if you know a better solution.

Advertisements

Read Full Post »