SQL Server Portal

Today when I was working on Columnstore index and I came across this error,  CREATE INDEX statement failed. Column ‘%.*ls’has a data type that cannot participate in a columnstore index. Omit column ‘%.*ls’. This is one of the new error messages come in SQL Server 2012. This error message is related to Columnstore index a new database feature shipped with SQL Server 2012.

Let me explain this with a simple example.
Message Number: 35343

Severity : 15

Error Message: CREATE INDEX statement failed. Column ‘%.*ls’has a data type that cannot participate in a columnstore index. Omit column ‘%.*ls’.

Error Generation:

Given below is the script that I tried to execute but it resulted with the following error:

Use AdventureWorks2012
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesOrderDetail_ColumnStore]
ON [Sales].[SalesOrderDetail]
(
[ProductID],
[OrderQty],
[UnitPrice],
[Rowguid]
)WITH (DROP_EXISTING = OFF) ON [PRIMARY]
GO

Msg 35343, Level 16, State 1, Line 1
CREATE INDEX statement failed. Column ‘rowguid’has a data type that cannot participate in a columnstore index. Omit column ‘rowguid’.

Ooopps…… I am unable to create columnstore index.

Resolution:
To resolve this error, I need to find the  datatypes which are not supported by  Columnstore index . As per MSDN, given below are the datatypes .

• binary and varbinary
• ntext , text, and image
• varchar(max) and nvarchar(max)
• uniqueidentifier
• rowversion (and timestamp)
• sql_variant
• decimal (and numeric) with precision greater than 18 digits
• datetimeoffset with scale greater than 2
• CLR types (hierarchyid and spatial types)
• xml

Now, just change your script (to create the columnstore index) and remove all non supported datatype. In this example, I will remove [Rowguid] column because its datatype is uniqueidentifier.

CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_SalesOrderDetail_ColumnStore]
ON [Sales].[SalesOrderDetail]
(
[ProductID],
[OrderQty],
[UnitPrice]
)WITH (DROP_EXISTING = OFF) ON [PRIMARY]
GO
--OUTPUT

Command(s) completed successfully.

Conclusion :

Remember, whenever you create the columnstore index, you must not include any column which has datatype that is not supported by columnstore index.