Search code examples
sql-serverindexingalter-tablealter

Unanticipated Index Creation in MSSQL and its Impact on Column Alteration - INDEX_VV_<3to4DigitNumber>_<3to4DigitNumber>

SQL Version - 12.00.6118(SQL Server 2014)

In MSSQL, indexes are created in a few tables and certain columns apart from the ones defined in the schema with the index name format 'INDEX_VV_<3to4DigitNumber>_<3to4DigitNumber>' (e.g., INDEX_VV_40627_40626).

Due to this, when attempting to ALTER the column, we face an issue as there is an unexpected index that is dependent on the column.

We have checked and confirmed whether there were any index rebuilding activities, SQL tuning performed, SQL jobs configured, or any custom modifications made at the SQL level in relation to indexes.

Is there any scenario in which an index with such a format will be created?

Solution

  • Is there any scenario in which an index with such a format will be created?

    No. SQL Server on prem doesn't do "Automatic Index Management". Some user or third-party tool created those indexes.