Auto-Create statistics is a well-known feature critical for the database performance. When one statistic is auto created, changing the table structure is not blocked by the presence of the statistic. An auto-created statistics is also dropped automatically when a schema change happens.
On the other hand, if the statistic is created by the user, any schema change will be blocked by the presence of the statistic.
The Auto-Drop setting on a statistic is a new SQL Server 2022 feature to change this behaviour. If a user created statistic is set with the auto-drop option, it will behave as an auto-created statistics: It will be automatically dropped if a schema change happens.
Let’s make an example using AdventureWorks2019. You can download it on https://learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&tabs=ssms . Restore the backup in a SQL Server 2022.
Demonstration
Our example will use the table Production.Product and focus on the field ListPrice. Let’s see a sequence of steps for this demonstration.
1) Drop the constraint CK_product_listprice, otherwise it will block the demonstration
DROP CONSTRAINT ck_product_listprice
2) Check the existing statistics. You will not find anyone related to ListPrice field.
3) Execute the following query:
FROM production.product
WHERE listprice = 10
3) Check the statistics again. A new statistic was automatically created for the ListPrice field.
4) Alter the column ListPrice.
ALTER COLUMN listprice NUMERIC(18, 2)
5) Check the statistics again. SQL Server will drop the auto-created statistic automatically.
6) Create a new statistics on the field ListPrice. It’s a user created statistics.
7) Try to change the schema again. An error will happen.
ALTER COLUMN listprice MONEY
8) Drop the user created statistic
9) Create the statistics again, this time using the auto-drop option.
10) Try to change the schema again. This time it will work and the statistics mystats will be dropped
ALTER COLUMN listprice MONEY
Checking which statistics have the auto-drop option
A simple query can help identify which statistics have the auto-drop option and which ones doesn’t:
NAME,
auto_drop
FROM sys.stats
Conclusion
This is a simple new feature, but it can help in some scenarios of version control and automated deployment for database schemas.
Load comments