SQL Server Portal

Today, I came across a situation where I had to get the total number of records in each Memory optimized table in a particular database.
I remember developing a solution earlier (How to get the number of records in a disk based tables. ). I tried this solution on memory optimized table but it did not work. Ooopssssssss.
Given below is the script that we use to get the number of records in a disk based table. I included the table type in the query to demonstrate the problem.

USE hkNorthwind
GO
SELECT
OBJECT_SCHEMA_NAME(OBJECT_ID) AS [Schema Name]
, OBJECT_NAME(OBJECT_ID) As [Table Name]
, SUM([rows]) as [Total Records]
,(CASE WHEN OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1
And OBJECTPROPERTYEX (OBJECT_ID, N'TableIsMemoryOptimized')=0
THEN 'User Table'
WHEN OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1
And OBJECTPROPERTYEX (OBJECT_ID, N'TableIsMemoryOptimized')=1
THEN 'Memory Optimized Table'
ELSE NULL
END) As [Table Type]
FROM sys.partitions
WHERE
(OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1
OR
OBJECTPROPERTYEX (OBJECT_ID, N'TableIsMemoryOptimized')=1)
GROUP BY OBJECT_ID
ORDER BY OBJECT_NAME(OBJECT_ID)
--OUTPUT

As you can see in the above example, it only returned the total number of records in disk based tables and returned zero for memory optimized table. So I started doing my research and found the solution. Given below is the script.

SOLUTION :

USE hkNorthwind
GO
SELECT
OBJECT_SCHEMA_NAME(OBJECT_ID) AS [Schema Name]
, OBJECT_NAME(OBJECT_ID) As [Table Name]
, SUM([rows]) as [Total Records]
FROM sys.partitions
WHERE
(OBJECTPROPERTYEX (OBJECT_ID, N'IsUserTable')=1
And OBJECTPROPERTYEX (OBJECT_ID, N'TableIsMemoryOptimized')=0)
GROUP BY OBJECT_ID

UNION

SELECT OBJECT_SCHEMA_NAME(Ind.OBJECT_ID) AS [Schema Name]
,OBJECT_NAME(Ind.OBJECT_ID) As [Table Name]
,ISNULL([rows],0)  as [Total Records]
FROM sys.hash_indexes AS Ind
CROSS APPLY sys.dm_db_stats_properties(Ind.object_id,Ind.index_id)
WHERE Ind.index_id =2
ORDER BY OBJECT_NAME(OBJECT_ID)
--OUTPUT

Note : Before executing the above query make sure that all your tables’ stats are up to date.

Let me know if you came across this issue and its solution.

Read Full Post »