CODEWITHSUNDEEP
pythonazuredatabricks
VinaySavanth
VinaySavanth

Reputation: 21

Azure- Column has a data type that cannot participate in a columnstore index

I'm trying to load data to a table in Azure database using databricks. I get the following error.

com.microsoft.sqlserver.jdbc.SQLServerException: The statement failed. Column 'MemberNumber' has a data type that cannot participate in a columnstore index.

I created the dataframe from a view and here's the logic for view creation

CREATE OR REPLACE TEMPORARY VIEW MemberDetails_Final AS
SELECT CAST(MemberNumber AS VARCHAR(20)) AS MemberNumber

I've seen that columnstore index doesn't work for varchar(max) but this column is defined as varchar(20).

Upvotes: 2

Views: 7721

Answers (1)

CHEEKATLAPRADEEP
CHEEKATLAPRADEEP

Reputation: 12768

Welcome to Stackoverflow!

If the underlying table has a column of a data type that is not supported for columnstore indexes, you must omit that column from the nonclustered columnstore index.

Columns that use any of the following data types cannot be included in a columnstore index:

  • ntext, text, and image
  • nvarchar(max), varchar(max), and varbinary(max) (Applies to SQL Server 2016 (13.x) and prior versions, and nonclustered columnstore indexes)
  • rowversion (and timestamp)
  • sql_variant
  • CLR types (hierarchyid and spatial types)
  • xml
  • uniqueidentifier (Applies to SQL Server 2012 (11.x))

For more details, refer Azure SQL Database - limitations and Restrictions.

Hope this helps.

Upvotes: 1

Related Questions