Changing Your Application to Eliminate Unsupported Data Types
There are limits to the data types for columns that are included in columnstore indexes, but fortunately, all the basic types (numbers, strings, dates) used in the large majority of data warehouses and data marts are supported. The following data types cannot be used in a columnstore index: decimal or numeric with precision > 18, datetimeoffset with precision > 2, binary, varbinary, image, text, ntext, varchar(max), nvarchar(max), cursor, hierarchyid, timestamp, uniqueidentifier, sqlvariant, and xml.
If you want to build a columnstore index on a table, and that table includes columns with unsupported data types, consider the following options.
Omit the column from the index
If the column that is not supported is not necessary for use in decision-support or data warehouses applications, you can leave that column out of the index. For example, if your table includes large (n)varchar(max) columns or XML columns, you will not be using those columns in typical data warehouse reporting queries. So including them in the columnstore index would provide little benefit even if it could be done. If your table contains lots of columns of these types, it may not be a good candidate for a columnstore index. You could just continue to use a heap or a B-tree to store the data.
Modify the data type to one of the included types
If it is useful and necessary to include a column with an unsupported type in the columnstore index, consider modifying the type to one of the supported types. Considerations for the different unsupported types are discussed below.
Numeric/Decimal with precision > 18
The most common unsupported type we've seen used by customers is a numeric or decimal with more than 18 digits (i.e. a precision higher than 18). In most of the applications we've seen that declare a column to have more than 18 digits, the actual data did not have more than 18 digits. So, for example, if you have a column SaleAmount of type numeric(38,2), consider change its type to numeric(18,2), provided that all the data in the column is small enough to fit in 18 digits. If you have a column like numeric(25,10) that really needs 25 digits then you could consider using two columns, one a bigint to represent the part of the data to the left of the decimal, and one a bigint to represent the part of the data to the right of the decimal. Queries could combine the two if needed. E.g. you could sum the two columns separately, then combine them after the aggregation.
Uniqueidentifier (guid)
Consider modifying a column of type uniqueidentifier to be an int or bigint. When you load the data into your data warehouse, you may need to use a translation table to map from the "business key" (uniqueidentifer) to a surrogate key (int or bigint). Using an integer column type instead of uniqueidentifer will also make your application perform better if you join on the column. Consider converting the uniqueidentifier to a string if you need to maintain all the information in it as part of the table with the columnstore index. The string column could still compress well if it has a low number of distinct values.
(n)varchar(max), text
If you have a column of type (n)varchar(max) or text that doesn't contain large strings, consider changing it to type (n)varchar(length) for a specified maximum length. It's not unusual for devlopers to use (n)varchar(max) or text just to avoid any length limit on strings, when in fact the application never uses strings longer than 8,000 bytes.
binary/varbinary, varbinary(max), image
If you must include data from a binary or varbinary field in a columnstore index, and the data is small enough to fit in an int or bigint, you can typecast the binary data to int or bigint without losing information. If the data is too bing for a bigint, consider converting the data to a character string format, such as a hexidcimal value encoded in a string. If you are using varbinary(max) or image fields that are all 4,000 characters or less, you may be able to encode these as strings as well if you really need them in the columnstore.
datetimeoffset with precision > 2
For datetimeoffset with precision > 2, consider truncating it to have 2 digits of precision if your application can tolerate this loss of information. Althernatively, you could include the portion after the decimal point in a separate column.
hierarchyid
Consider converting a hierarchyid to a bigint or a string if you need to keep it in a columnstore index. You may lose the ability to peform hierarchy operations but you will still be able to preservie its original value and its uniqueness property.
timestamp
Timestamp columns are not really necessary in a columnstore because you can't update the data once it has a columnstore on it. You should be able to omit the column. If you need the data, convert it to a bigint.
sqlvariant
Consider splitting a sqlvariant into several different columns, one for each type that actually appears in the sqlvariant column in the data. You can use an additional integer "type code" column to identify the data type of the variant for a particular row. Or, if all the data in the variant is numeric, you may be able to convert it all to a bigint or numeric(18,X) type.
xml
Consider placing small XML values into an (n)varchar field. Otherwise, omit these from the columnstore index.