SQL Server 2022: How Auto-Drop Statistics Work

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

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 

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:

SELECT object_id,
       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.

 

The post SQL Server 2022: How Auto-Drop Statistics Work appeared first on Simple Talk.

from Simple Talk https://ift.tt/YRh26EM
via