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 behavior. 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.
Using adventureworks lets take a look
Our example will use the table Production.Product and focus on the field ListPrice.
1) Drop the constraint CK_product_listprice, otherwise it will block the demonstration
ALTER TABLE production.product
DROP CONSTRAINT ck_product_listprice
2) Check the existing statistics. You will not find anyone related to ListPrice field.
3) Execute the following query:
SELECT *
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 TABLE production.product
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.
CREATE STATISTICS [mystats] ON production.product(listprice)
7) Try to change the schema again. An error will happen.
ALTER TABLE production.product
ALTER COLUMN listprice MONEY
8) Drop the user created statistic
DROP statistic production.product.mystats
9) Create the statistics again, this time using the auto-drop option.
CREATE statistics [mystats] ON production.product(listprice) WITH auto_drop=ON
10) Try to change the schema again. This time it will work and the statistics mystats will be dropped
ALTER TABLE production.product
ALTER COLUMN listprice MONEY
You can check which stats have auto drop
A simple query can help identify which statistics have the auto-drop option and which ones doesn’t:
SELECT object_id, NAME, auto_drop
FROM sys.stats
—check the auto_drop property
select * from sys.stats where object_id=object_id(‘table1’)
In conclusion, the introduction of the Auto-Drop feature for user-created statistics in SQL Server 2022 marks a significant improvement in database management and flexibility. This feature harmonizes the behavior of both auto-created and user-created statistics, offering a more streamlined and efficient approach to handling schema changes.
Through the example using the AdventureWorks database and the Production.Product table, we clearly see the practical implications of this feature. The ability to automatically drop user-created statistics when schema changes occur – akin to the behavior of auto-created statistics – simplifies database maintenance and reduces the potential for errors. This is especially useful in dynamic environments where schema modifications are frequent.
Furthermore, the option to easily identify which statistics have the auto-drop setting enabled, using a simple query on the sys.stats system view, adds to the usability of this feature. Database administrators now have greater control and can make more informed decisions about their statistics management strategies.
Overall, SQL Server 2022’s Auto-Drop feature for statistics is a thoughtful enhancement that addresses a longstanding limitation, thereby improving the database’s adaptability and performance. It’s a clear reflection of SQL Server’s commitment to evolving in line with the needs of modern database environments.