Azure DW, The Peril of AUTO_CREATE_STATISTICS

Last year the Azure DW team announced the availability of added another hotly requested feature, auto creation of statistics.

Auto creating statistics has many known benefits which impacts DevOps, as database deployment pipelines don’t have to include the statistics creating queried, DBA’s who don’t have to worry about the lack of statistics on objects and business users who normally suffer from poorly perming reports and queries.

The Azure DW blogpost describes the impact well.

The Peril, yes there is one – is that auto created stats objects will sometimes prevent columns from being dropped from tables. The following error can sometimes be observed:

SQL exception has occured in script: '0000001-dropColum.sql'
Script block number: 0; Block line 1; Message:
System.Data.SqlClient.SqlException (0x80131904): The statistics 'Stat_b71bb60085b5420bbd7b95c46e578bb6' is dependent on column 'CostValue'.

Should this error occur, then the best option would be to disable auto create stats, and after the db deploy / code has been run we can enable it:

Disable
ALTER DATABASE SalesDW SET AUTO_CREATE_STATISTICS OFF;

Enable
ALTER DATABASE SalesDW SET AUTO_CREATE_STATISTICS ON;

Top tip: disable at the start of your database deployment and then enable at the end. MS Docs