Hope you enjoyed my earlier posts on
In-Memory OLTP and Memory Optimized data filegroup in SQL Server 2014
In-Memory OLTP: Compatibility Check for SQL Server and Database

Here, as continuation of compatibility check, we are moving further one more step on Datatype compatibility.

The below script is primarily focusing on major limitations like unsupported datatypes,computed columns,sparse columns, unavailability of primary keys and presence of clustered index. The script has been developed for SQL Server 2014 version(if there are any of the above limitations removed in later versions, you may need to change the script accordingly.).

Note: Please change the result view as “Results To Text(Ctrl + T)” for better readability of the result.

PRINT '*******************************************************'
PRINT 'DATATYPE COMPATIBILITY CHECK'
PRINT '*******************************************************'

PRINT 'THE BELOW TABLE(S) ARE NOT COMPATIBLE TO IN-MEMORY AS IS DUE TO UNSUPPORTED DATATYPES. '
SELECT NAME FROM SYS.TABLES WHERE NAME IN
	(SELECT OBJECT_NAME(OBJECT_ID) FROM SYS.COLUMNS WHERE 
	(TYPE_NAME(USER_TYPE_ID) IN 
            ('IMAGE','TEXT','NTEXT','DATETIMEOFFSET','XML','GEOMETRY',
             'DATETIMEOFFSET','GEOGRAPHY','TIMESTAMP','SQL_VARIANT'))
	OR
	(TYPE_NAME(user_type_id) IN 
            ('VARCHAR','CHAR','NVARCHAR','NCHAR','VARBINARY') AND MAX_LENGTH = -1)
	OR
	/*FILESTREAM can only be enabled with VARBINARY(MAX), however, the defined form of datatype is
		not supported in In-Memory Tables, provided the below for FILESTREAM/FILETABLE for completeness,*/
	(TYPE_NAME(USER_TYPE_ID) IN ('VARBINARY') AND MAX_LENGTH = -1 AND (IS_FILESTREAM =1 OR IS_FILETABLE = 1))
	)

--COMPUTED COLUMNS
PRINT 'THE BELOW TABLE(S) ARE NOT COMPATIBLE TO IN-MEMORY AS IS DUE TO UNSUPPORTED COMPUTED COLUMNS'
SELECT NAME FROM SYS.TABLES WHERE NAME IN
	(SELECT OBJECT_NAME(OBJECT_ID) FROM SYS.COLUMNS WHERE IS_COMPUTED =1)

--SPARSE COLUMNS
PRINT 'THE BELOW TABLE(S) ARE NOT COMPATIBLE TO IN-MEMORY AS THEY HAVE SPARSE COLUMNS'
SELECT NAME FROM SYS.TABLES WHERE NAME IN
	(SELECT OBJECT_NAME(OBJECT_ID) FROM SYS.COLUMNS WHERE is_sparse =1) 

-- ABSENCE OF PRIMARY KEY
PRINT 'THE BELOW TABLE(S) ARE NOT COMPATIBLE TO IN-MEMORY AS THEY DO NOT HAVE PRIMARY KEYS'
SELECT NAME FROM SYS.TABLES WHERE NAME NOT IN 
	(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS  WHERE CONSTRAINT_TYPE ='PRIMARY KEY')

--PRESENCE OF CLUSTERED INDEX
PRINT 'THE BELOW TABLE(S) ARE NOT COMPATIBLE TO IN-MEMORY AS THEY HAVE CLUSTERED INDEX'
SELECT B.NAME FROM SYS.INDEXES A
INNER JOIN SYS.TABLES B ON A.OBJECT_ID =B.OBJECT_ID WHERE A.TYPE_DESC = 'CLUSTERED'