As I’ve discussed in some of my previous posts, there are quite a few data types that cannot be part of a Columstore index. While there are fewer restrictions in SQL Server 2014, they still exist. I find myself constantly looking back at Books Online trying to make sure data types in my tables don’t contain any of those restricted data types. It would be much easier to know from day one which tables I need to redesign, or at least which columns I need to exclude from a non-clustered Columnstore index. This is why I have created the following script.
-- Find columns in user tables that cannot be included in a columnstore index.
-- These restrictions apply to both clustered and non-clustered columnstore indexes.
-- SQL Server 2014: http://msdn.microsoft.com/en-us/library/gg492153(v=sql.120).aspx
-- SQL Server 2012: http://msdn.microsoft.com/en-us/library/gg492153(v=sql.110).aspx
-- Get the version number of SQL Server
DECLARE @ServerVersion TINYINT = CONVERT(INT,SUBSTRING(CONVERT(VARCHAR,SERVERPROPERTY('ProductVersion')),1,(CHARINDEX('.',(CONVERT(VARCHAR,SERVERPROPERTY('ProductVersion'))))-1)))
IF @ServerVersion = 11
-- This section is only for SQL Server 2012
BEGIN
SELECT
s.name AS 'SchemaName'
,o.name AS 'TableName'
,c.name AS 'ColumnName'
,'ColumnType' = CASE t.name
WHEN 'decimal' THEN t.name + '(' + CONVERT(VARCHAR,c.precision) + ',' + CONVERT(VARCHAR,c.scale) + ')'
WHEN 'numeric' THEN t.name + '(' + CONVERT(VARCHAR,c.precision) + ',' + CONVERT(VARCHAR,c.scale) + ')'
WHEN 'varchar' THEN
CASE c.max_length
WHEN -1 THEN 'varchar(max)'
ELSE 'varchar(' + CONVERT(VARCHAR,c.max_length) + ')'
END
WHEN 'nvarchar' THEN
CASE c.max_length
WHEN -1 THEN 'nvarchar(max)'
ELSE 'nvarchar(' + CONVERT(VARCHAR,c.max_length) + ')'
END
WHEN 'datetimeoffset' THEN t.name + '(' + CONVERT(VARCHAR,c.scale) + ')'
ELSE t.name
END
,'ColumnAttribute' = CASE
WHEN (c.is_filestream = 1) THEN 'Filestream'
WHEN (c.is_sparse = 1) THEN 'Sparse'
ELSE ''
END
FROM sys.columns c
JOIN sys.objects o ON c.object_id = o.object_id
JOIN sys.types t ON c.user_type_id = t.user_type_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.is_ms_shipped <> 1
-- These types cannot be part of a SQL Server 2012 columnstore index
AND (
t.name IN
('binary'
,'varbinary'
,'ntext'
,'text'
,'image'
,'uniqueidentifier'
,'rowversion'
,'timestamp'
,'sql_variant'
,'hierarchyid'
,'geography'
,'geometry'
,'xml')
OR (
-- Decimal & numeric cannot have a precision over 18
t.name IN ('decimal','numeric')
AND c.precision > 18)
OR (
-- Varchar(max) and nvarchar(max)
t.name = 'datetimeoffset'
AND c.scale > 2)
OR (
-- Varchar(max) and nvarchar(max)
t.name IN ('varchar','nvarchar')
AND c.max_length = -1)
OR (
-- Filestream
c.is_filestream = 1)
OR (
-- Sparse
c.is_sparse = 1)
)
ORDER BY s.name,o.name,c.column_id
END
ELSE IF @ServerVersion = 12
-- This section is only for SQL Server 2014
BEGIN
SELECT
s.name AS 'SchemaName'
,o.name AS 'TableName'
,c.name AS 'ColumnName'
,'ColumnType' = CASE t.name
WHEN 'varchar' THEN
CASE c.max_length
WHEN -1 THEN 'varchar(max)'
ELSE 'varchar(' + CONVERT(VARCHAR,c.max_length) + ')'
END
WHEN 'nvarchar' THEN
CASE c.max_length
WHEN -1 THEN 'nvarchar(max)'
ELSE 'nvarchar(' + CONVERT(VARCHAR,c.max_length) + ')'
END
ELSE t.name
END
,'ColumnAttribute' = CASE
WHEN (c.is_filestream = 1) THEN 'Filestream'
WHEN (c.is_sparse = 1) THEN 'Sparse'
ELSE ''
END
FROM sys.columns c
JOIN sys.objects o ON c.object_id = o.object_id
JOIN sys.types t ON c.user_type_id = t.user_type_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.is_ms_shipped <> 1
-- These types cannot be part of a SQL Server 2014 columnstore index
AND (
t.name IN
('ntext'
,'text'
,'image'
,'rowversion'
,'timestamp'
,'sql_variant'
,'hierarchyid'
,'geography'
,'geometry'
,'xml')
OR (
-- Varchar(max) and nvarchar(max)
t.name IN ('varchar','nvarchar')
AND c.max_length = -1)
OR (
-- Filestream
c.is_filestream = 1)
OR (
-- Sparse
c.is_sparse = 1)
)
ORDER BY s.name,o.name,c.column_id
END
ELSE
BEGIN
RAISERROR ('This script only works on SQL Server 2012 and SQL Server 2014.',16,1);
END
GO
Running this against your database will output an organized list of tables along with the column name and data type that cannot be used within a Columnstore index.
The script can be used if you plan to create a clustered or non-clustered index, since the data type restrictions would apply to both. The script can also be used to analyze databases in either SQL Server 2012 or 2014.
You can read more about the limitations and restrictions of Columnstore indexes in Books Online.
