Errors With Column Store Index in SQL Server



Today I was trying my hands on Column store indexes and luckily for 10 mins i was not able to create Clustered column store indexes, Off course I didn't read column store clustered indexes carefully and then luckily I got shower of errors and I also found denis blog from where i also added lot of reasons for column store index operation failure in this post. And If time permits down the line i will try to add post for all those errors implementation.

 It starts from here

  • CREATE INDEX statement failed because specifying a key list is not allowed when creating a clustered column store index. Create the clustered column store index without specifying a key list.: SQl server doesn't allow to specify any column name while creating clustered index

  • CREATE INDEX statement failed. Column 'name' has a data type that cannot participate in a column store index. Omit column : I have one column with varchar (max) which is not allowed in sql server

  • CREATE INDEX statement failed because a non clustered index cannot be created on a table that has a clustered column store index. Consider replacing the clustered column store index with a non clustered column store index.
  • SQL Server cannot load database ‘%.*ls’ because it contains a column store index. The currently installed edition of SQL Server does not support column store indexes. Either disable the column store index in the database by using a supported edition of SQL Server
  • The Cross Row set check on column store index object ID %d, index ID %d, partition ID %I64d. Drop and recreate the column store index.
  • CREATE INDEX statement failed because a column store index cannot be unique. Create the column store index without the UNIQUE keyword or create a unique index without the COLUMN STORE keyword.
  • CREATE INDEX statement failed because specifying sort order (ASC or DESC) is not allowed when creating a column store index. Create the column store index without specifying a sort order.
  • CREATE INDEX statement failed because a column store index cannot be created on a view. Consider creating a column store index on the base table or creating an index without the COLUMN STORE keyword on the view.
  • CREATE INDEX statement failed because column ‘%.*ls’ on table ‘%.*ls’ is a computed column and a column store index cannot be created on a computed column. Consider creating a non clustered column store index on a subset of columns that does not include the 
  • computed column
  • CREATE INDEX statement failed because a column store index cannot be a filtered index. Consider creating a column store index without the predicate filter.
  • CREATE INDEX statement failed because a column store index cannot be created on a sparse column. Consider creating a non clustered column store index on a subset of columns that does not include any sparse columns.
  • CREATE INDEX statement failed because a column store index cannot have included columns. Create the column store index on the desired columns without specifying any included columns.
  • CREATE INDEX statement failed because a column store index cannot be created on a column with file stream data. Consider creating a non clustered column store index on a subset of columns that does not include any columns with file stream data.
  • CREATE INDEX statement failed because specifying FILE STREAM_ON is not allowed when creating a column store index. Consider creating a column store index on columns without file stream data and omit the FILE STREAM_ON specification.
  • CREATE INDEX statement failed because a column store index cannot be created on a column set. Consider creating a non clustered column store index on a subset of columns in the table that does not contain a column set or any sparse columns.
  • CREATE INDEX statement failed because a column store index cannot be created in this edition of SQL Server. See Books Online for more details on feature support in different SQL Server editions.
  • CREATE INDEX statement failed because a column store index must be partition-aligned with the base table. Create the column store index using the same partition function and same (or equivalent) partition scheme as the base table. If the base table is not
  • CREATE INDEX statement failed because specifying %S_MSG is not allowed when creating a column store index. Consider creating a column store index without specifying %S_MSG.
  • CREATE INDEX statement failed because the %S_MSG option is not allowed when creating a column store index. Create the column store index without specifying the %S_MSG option.
  • CREATE INDEX statement failed because specifying DATA_COMPRESSION is not allowed when creating a column store index. Consider creating a column store index without specifying DATA_COMPRESSION. Column store indexes are always compressed automatically.
  • ALTER TABLE statement failed because the definition of a column cannot be changed if the column is part of a column store index. Consider dropping the column store index, altering the column, then creating a new column store index.
  • ALTER INDEX statement failed because a column store index cannot be reorganized. Reorganization of a column store index is not necessary.
  • ALTER INDEX REBUILD statement failed because specifying %S_MSG is not allowed when rebuilding a column store index. Rebuild the column store index without specifying %S_MSG.
  • ALTER INDEX REBUILD statement failed because the %S_MSG option is not allowed when rebuilding a column store index. Rebuild the column store index without specifying the %S_MSG option.
  • ALTER INDEX REBUILD statement failed because specifying DATA_COMPRESSION is not allowed when rebuilding a column store index. Rebuild the column store index without specifying DATA_COMPRESSION. Column store indexes are always compressed automatically.
  • %S_MSG statement failed because data cannot be updated in a table with a column store index. Consider disabling the column store index before issuing the %S_MSG statement, then rebuilding the column store index after %S_MSG is complete.
  • DBCC DBREINDEX failed because specifying FILL FACTOR is not allowed when creating or rebuilding a column store index. Rebuild the column store index without specifying FILL FACTOR.
  • CREATE INDEX statement failed because specifying key list is not allowed when creating a clustered column store index. Create the clustered column store index without specifying key list.
  • UPDATE STATISTICS failed because statistics cannot be updated on a column store index. UPDATE STATISTICS is valid only when used with the STATS_STREAM option.
  • Clustered column store index is not supported.
  • Multiple non clustered column store indexes are not supported.
  • Conversion between column store index and relational index is not supported.
  • CREATE INDEX statement failed because a column store index cannot be created on a column with datatype decimal or numeric that has a precision that requires more than 8 bytes of storage. Consider either reducing the precision of column ‘%.*ls’ to 18.
  • CREATE INDEX statement failed because a column store index cannot be created on a date time offset type with precision that requires more than 8 bytes of storage. Consider either reducing the precision of column ‘%.*ls’ to date time offset(n) where n = 0, 1, o
  • Cannot include column ‘%.*ls’ in a column store index because the data type of the column is not supported in a column store index. The column may have been included explicitly (in the CREATE INDEX statement) or implicitly. Implicit inclusion occurs when c.
  • MERGE clause of ALTER PARTITION statement failed because two nonempty partitions containing a columnstore index cannot be merged. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index.
  • MERGE clause of ALTER PARTITION statement failed because two partitions on different filegroups cannot be merged if either partition contains columnstore index data. Consider disabling the columnstore index before issuing the ALTER PARTITION statement.
  • SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement.
  • The stored procedure, sp_tableoption failed because a table with a nonclustered columnstore index cannot be altered to use vardecimal storage format. Consider dropping the columnstore index.
  • CREATE INDEX statement failed because table ‘%.*ls’ uses vardecimal storage format. A columnstore index cannot be created on a table using vardecimal storage. Consider rebuilding the table without vardecimal storage.
  • TRUNCATE TABLE statement failed because table ‘%.*ls’ has a columnstore index on it. A table with a columnstore index cannot be truncated. Consider dropping the columnstore index then truncating the table.
  • CREATE INDEX statement failed because a columnstore index on a partitioned table must be partition-aligned with the base table. Consider dropping the columnstore index before creating a new clustered index.
  • DROP INDEX statement failed because a columnstore index on a partitioned table must be partition-aligned with the base table (heap). Consider dropping the columnstore index before dropping a clustered index.
  • %S_MSG statement failed because the operation cannot be performed online on a table with a columnstore index. Perform the operation without specifying the ONLINE option or drop (or disable) the columnstore index before performing the operation using the O
  • %s cannot be enabled on a table with a columnstore index. Consider dropping columnstore index ‘%s’ on table ‘%s’.


    • CREATE INDEX statement failed because a columnstore index cannot be created on a table enabled for %S_MSG. Consider disabling %S_MSG and then creating the columnstore index.
      • Clustered columnstore index is not supported.if You are using SQL server version less than 2014


      References:


      No comments:

      Post a Comment

      Subscribe to: Post Comments (Atom)