AUTO_DROP Setting for Create Statistics in SQL Server

Problem

There is a new feature in SQL Server 2022 which is AUTO_DROP statistics. In this article, we will cover what this does and how to enable and disable it.

Solution

This tip will explain the new AUTO_DROP statistic feature and provide some examples. We will also show some common error messages and how to solve them.

Requirements

We will use the following for the examples in this article:

1. Install SQL Server 2022
2. Install the AdventureWorks database
3. Install SSMS

What are SQL Server statistics?

Statistics are objects that contain information about the distribution of data values and SQL Server uses this information to determine how to generate execution plans. Having statistics updated can help performance of SQL Server queries. On the other hand, having statistics out of date can degrade SQL Server performance.

If you run a query, statistics are used to find the best execution plan. That is why having statistics updated is very important.

In SSMS, when you expand a table, you can see the Statistics folder and statistics for the table inside this folder.

We can also see the statistics using T-SQL. The following command shows how to check the statistics:

select * from sys.stats

The result:

 There is a new column named AUTO_DROP in the sys.stats view if you use SQL Server 2022 or Azure SQL. AUTO_DROP is 0 (false) if the option is disabled and 1 (true) if the option is enabled.

What is the SQLL Server AUTO_DROP Setting?

The AUTO_DROP setting is new in SQL Server 2022 and also available in Azure SQL and Azure SQL Managed instances.

In earlier versions of SQL Server, statistics could block schema changes but this is not the case in SQL 2022. Let’s take a look at an example.

CREATE STATISTICS [myPasswordStats]
ON [Person].[Password] (BusinessEntityID, [PasswordHash], [PasswordSalt])
WITH AUTO_DROP = ON;

We create statistics named myPasswordStats on table Person.Password and include three columns: (BusinessEntityID, PasswordHash, PasswordSalt). Finally, we set the AUTO_DROP setting to ON.

We can now see the new statistic:

Right-click on the table, select the design option and delete the PasswordHash column.

Note, the statistics myPasswordStats was removed:

This happened because of the change we made to the structure with the column deletion and now the statistic is obsolete. That is why it is dropped automatically using the auto drop setting.

AUTO_DROP Setting Not Available Prior to SQL Server 2022

If we run the code in SQL Server 2019 or lower versions we will get an error:

CREATE STATISTICS [myPasswordStats]
ON [Person].[Password]
(BusinessEntityID, [PasswordHash], [PasswordSalt])
WITH AUTO_DROP = ON;

The AUTO_DROP setting is not recognized in earlier versions. To check your SQL Server version, refer to this link: How to tell what SQL Server versions you are running.

Deleting Statistics Without AUTO_DROP Enabled

We will run the code without the AUTO_DROP option. The following code will create statistics in a SQL Server 2019 database.

CREATE STATISTICS [myPasswordStats]
ON [Person].[Password]
(BusinessEntityID, [PasswordHash], [PasswordSalt])

Now, we will try to delete the column:

The error message we get is:

There is a dependence on the column for the statistics that does not allow altering of the table.

Enable or Disable AUTO_DROP Setting

Turn Off

This example shows how to turn off the AUTO_DROP setting.

UPDATE STATISTICS [Person].[Password] [myPasswordStats] WITH AUTO_DROP = OFF;

In this example, [Person].[Password] is the table name, and myPasswordStats is the name of the statistics.

Turn On

This example shows how to turn on the AUTO_DROP setting.

UPDATE STATISTICS [Person].[Password]  [myPasswordStats] WITH AUTO_DROP = ON;

Conclusion

SQL Server 2022 now has this new auto drop feature, so consider using this in future development.

Next Steps

To learn more about SQL Server 2022, refer to these links:

• New features in SQL Server 2022
• New functions in SQL Server 2022 – LEFT_SHIFT and RIGHT_SHIFT