In-Memory OLTP: Compatibility Check for Datatypes in SQL Server

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'
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s