Using adventureworks lets take a look<\/p>\n\n\n\n
Our example will use the table Production.Product<\/em> and focus on the field ListPrice<\/em>.<\/p>\n\n\n\n 1) Drop the constraint CK_product_listprice<\/em>, otherwise it will block the demonstration<\/p>\n\n\n\n ALTER\u00a0TABLE\u00a0production.product 2) Check the existing statistics. You will not find anyone related to ListPrice field.<\/p>\n\n\n\n 3) Execute the following query:<\/p>\n\n\n\n SELECT\u00a0* 3) Check the statistics again. A new statistic was automatically created for the ListPrice field.<\/p>\n\n\n\n 4) Alter the column ListPrice<\/em>.<\/p>\n\n\n\n ALTER\u00a0TABLE\u00a0production.product 5) Check the statistics again. SQL Server will drop the auto-created statistic automatically.<\/p>\n\n\n\n 6) Create a new statistics on the field ListPrice<\/em>. It\u2019s a user created statistics.<\/p>\n\n\n\n CREATE STATISTICS [mystats] ON production.product(listprice) <\/p>\n\n\n\n 7) Try to change the schema again. An error will happen.<\/p>\n\n\n\n ALTER\u00a0TABLE\u00a0production.product 8) Drop the user created statistic<\/p>\n\n\n\n DROP statistic production.product.mystats <\/p>\n\n\n\n 9) Create the statistics again, this time using the auto-drop option.<\/p>\n\n\n\n CREATE statistics [mystats] ON production.product(listprice) WITH auto_drop=ON <\/p>\n\n\n\n 10) Try to change the schema again. This time it will work and the statistics mystats will be dropped<\/p>\n\n\n\n ALTER\u00a0TABLE\u00a0production.product You can check which stats have auto drop<\/strong><\/p>\n\n\n\n A simple query can help identify which statistics have the auto-drop option and which ones doesn\u2019t:<\/p>\n\n\n\n SELECT\u00a0object_id, NAME, auto_drop —check the auto_drop property<\/p>\n\n\n\n select * from sys.stats where object_id=object_id(‘table1’)<\/p>\n\n\n\n Through the example using the AdventureWorks database and the Furthermore, the option to easily identify which statistics have the auto-drop setting enabled, using a simple query on the 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.<\/p>\n","protected":false},"excerpt":{"rendered":" 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\u00a0SQL Server 2022\u00a0feature to change this behavior.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[5,40,44,21],"tags":[46,45],"class_list":["post-130","post","type-post","status-publish","format-standard","hentry","category-performance","category-sql-server-2022","category-statistics","category-tutorial","tag-auto-drop","tag-statistics"],"yoast_head":"\n
DROP\u00a0CONSTRAINT\u00a0ck_product_listprice\u00a0<\/p>\n\n\n\n![\"\"](\"https:\/\/www.sqltabletalk.com\/wp-content\/uploads\/2023\/11\/image-2.png\")
<\/figure>\n\n\n\n
FROM\u00a0\u00a0\u00a0production.product
WHERE\u00a0\u00a0listprice\u00a0=\u00a010\u00a0<\/p>\n\n\n\n![\"\"](\"https:\/\/www.sqltabletalk.com\/wp-content\/uploads\/2023\/11\/image-3-1024x969.png\")
<\/figure>\n\n\n\n
ALTER\u00a0COLUMN\u00a0listprice\u00a0NUMERIC<\/em>(18,\u00a02)\u00a0<\/p>\n\n\n\n![\"\"](\"https:\/\/www.sqltabletalk.com\/wp-content\/uploads\/2023\/11\/image-4.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.sqltabletalk.com\/wp-content\/uploads\/2023\/11\/image-5.png\")
<\/figure>\n\n\n\n
ALTER\u00a0COLUMN\u00a0listprice\u00a0MONEY<\/em>\u00a0<\/p>\n\n\n\n![\"\"](\"https:\/\/www.sqltabletalk.com\/wp-content\/uploads\/2023\/11\/image-6-1024x299.png\")
<\/figure>\n\n\n\n
ALTER\u00a0COLUMN\u00a0listprice\u00a0MONEY<\/em>\u00a0<\/p>\n\n\n\n
FROM\u00a0\u00a0\u00a0sys.stats\u00a0<\/p>\n\n\n\n![\"\"](\"https:\/\/www.sqltabletalk.com\/wp-content\/uploads\/2023\/11\/image-7-1024x72.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.sqltabletalk.com\/wp-content\/uploads\/2023\/11\/image-8-1024x73.png\")
<\/figure>\n\n\n\n
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.<\/p>\n\n\n\nProduction.Product<\/code> table, we clearly see the practical implications of this feature. The ability to automatically drop user-created statistics when schema changes occur \u2013 akin to the behavior of auto-created statistics \u2013 simplifies database maintenance and reduces the potential for errors. This is especially useful in dynamic environments where schema modifications are frequent.<\/p>\n\n\n\nsys.stats<\/code> 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.<\/p>\n\n\n\n