How to change a statistic's collation or remove its dependency from database collation in SQL Server?

user_6059951 0
Viewed 4373

I have decided to change my SQL Server database collation; however it gives me the common schema-bound error.

The database collation cannot be changed if a schema-bound object depends on it

There are several objects listed on the error list above, including some constraints and statistics. I have no problem with removing the constraints' dependencies. My only problem is removing the statistics' dependencies.

The exact error is:

The statistics 'SampleStatistics' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

Here are some notes:

  1. I cannot delete the statistic, because the Delete option is disabled.
  2. I cannot generate the statistic's script, because the Script Statistic as option is disabled.
  3. I have found the statistic in sys.stats, but I cannot delete the record and I get this error:

Ad hoc updates to system catalogs are not allowed. 4) There isn't any way to change the collation or remove the dependency in the statistic's Properties option.

Is there anyway to solve the problem?

sql-server
1 Answers

Statistics are database objects which involve the detailed statistical distribution of the column values for the tables or indexed views. The query optimizer uses this information to estimate how many rows will be returned from a query.

When we create an index the statistics are created automatically which are related to the index. If we drop the index the statistics which are related to the index will be dropped.

On the other hand, the queries can create statistics in their first execution and we can drop these statistics as like the below query :

DROP STATISTICS table_name.statistics_name | view.statistics_name [ ,...n ]
user_121189790
Related