If DBCC printed error messages, contact your system administrator: operand type clash on index rebuild

Question

Question: I'm using SQL Server 2016 and have a table with encrypted columns using Always Encrypt. I had to make some changes to the table. So, I had to drop and re-add the table. The table has one index, the clustered index.

When I rebuild my indexes on the table specifying the name of the index it is fine:

DBCC DBREINDEX ('[case]', PK_Case_1,90); The result is..

DBCC execution completed. If DBCC printed error messages, contact your system administrator. Completion time: 2021-07-30T17:52:21.6183694-04:00

But, if I run it for all indexes on the table, I get...

DBCC DBREINDEX ('[case]', ' ',90); Msg 206, Level 16, State 2, Line 3 Operand type clash: nvarchar(max) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'DBNAME') is incompatible with varchar DBCC execution completed. If DBCC printed error messages, contact your system administrator. Completion time: 2021-07-30T17:53:06.1301441-04:00

So, I looked to see what other indexes there may be and it only shows the one...

USE DBNAME EXEC sp_helpindex '[Case]'

index_name index_description, index_keys

PK_Case_1 clustered, unique, primary key located on PRIMARY CaseID

I ran dbcc checkdb(DBNAME,repair_allow_data_loss) - and there are no errors.

Re-ran DBREINDEX and it the result are the same. Any ideas as to where these mixed results are coming from?

--UPDATE SOLVED-- The issue was not a hidden index, it was the statistics. I dropped the statistics and rebuilt the index the error no longer appears. I'm guessing that one of the statistics was referencing an old datatype.

Answer 1

You can rebuild a clustered index online or offline. Here, we will discuss the steps to rebuild an index online using the SQL Server Management Studio. The detailed steps are as follows:

• In SSMS, from the Object Explorer box, click the plus sign to expand the SQL database containing the table on which you need to rebuild an index online.
• Expand the database table on which you want to rebuild the index online.
• Expand the Indexes folder, right-click the index you wish to rebuild online, and select Properties.
• Select Options under Select a page.
• Select Allow online DML processing, and choose True from the list, and then click OK.
• Right-click the index to be rebuilt online, and then select Rebuild.
• When the ‘Rebuild Indexes’ dialog box opens, make sure that the correct index is listed in the Indexes to rebuild the grid. Click OK.

Answer 2

Unless NO_INFOMSGS is specified (the table name must be specified), DBCC DBREINDEX always returns:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Link reference: https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-dbreindex-transact-sql?view=sql-server-ver16