{"id":95204,"date":"2022-12-19T17:00:57","date_gmt":"2022-12-19T17:00:57","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=95204"},"modified":"2022-12-27T19:28:19","modified_gmt":"2022-12-27T19:28:19","slug":"sql-server-2022-how-auto-drop-statistics-work","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/sql-server-2022-how-auto-drop-statistics-work\/","title":{"rendered":"SQL Server 2022: How Auto-Drop Statistics Work"},"content":{"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.<\/p>\n

On the other hand, if the statistic is created by the user, any schema change will be blocked by the presence of the statistic.<\/p>\n

The Auto-Drop setting on a statistic is a new SQL Server 2022<\/strong> 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.<\/p>\n

Let’s make an example using AdventureWorks2019<\/em>. You can download it on https:\/\/learn.microsoft.com\/en-us\/sql\/samples\/adventureworks-install-configure?view=sql-server-ver16&tabs=ssms<\/a> . Restore the backup in a SQL Server 2022<\/strong>.<\/p>\n

Demonstration<\/h2>\n

Our example will use the table Production.Product<\/em> and focus on the field ListPrice<\/em>. Let’s see a sequence of steps for this demonstration.<\/p>\n

1) Drop the constraint CK_product_listprice<\/em>, otherwise it will block the demonstration<\/p>\n

ALTER<\/span>\u00a0TABLE<\/span>\u00a0production<\/span>.<\/span>product<\/span>
\n\u00a0\u00a0DROP<\/span>\u00a0CONSTRAINT<\/span>\u00a0ck_product_listprice<\/span>\u00a0 <\/span><\/div>\n

2) Check the existing statistics. You will not find anyone related to ListPrice field.<\/p>\n

3) Execute the following query:<\/p>\n

SELECT<\/span>\u00a0*<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0production<\/span>.<\/span>product<\/span>
\nWHERE<\/span>\u00a0\u00a0listprice<\/span>\u00a0=<\/span>\u00a010<\/span>\u00a0 <\/span><\/div>\n

3) Check the statistics again. A new statistic was automatically created for the ListPrice field.<\/p>\n

\"Interface<\/p>\n

4) Alter the column ListPrice<\/em>.<\/p>\n

ALTER<\/span>\u00a0TABLE<\/span>\u00a0production<\/span>.<\/span>product<\/span>
\n\u00a0\u00a0ALTER<\/span>\u00a0COLUMN<\/span>\u00a0listprice<\/span>\u00a0NUMERIC<\/span>(<\/span>18<\/span>,<\/span>\u00a02<\/span>)<\/span>\u00a0 <\/span><\/div>\n

5) Check the statistics again. SQL Server will drop the auto-created statistic automatically.<\/p>\n

\"Texto\n\nDescri\u00e7\u00e3o<\/p>\n

6) Create a new statistics on the field ListPrice<\/em>. It’s a user created statistics.<\/p>\n

CREATE<\/span>\u00a0STATISTICS<\/span>\u00a0[mystats]<\/span>\u00a0ON<\/span>\u00a0production<\/span>.<\/span>product<\/span>(<\/span>listprice<\/span>)<\/span>\u00a0 <\/span><\/div>\n

\"Texto\n\nDescri\u00e7\u00e3o<\/p>\n

7) Try to change the schema again. An error will happen.<\/p>\n

ALTER<\/span>\u00a0TABLE<\/span>\u00a0production<\/span>.<\/span>product<\/span>
\n\u00a0\u00a0ALTER<\/span>\u00a0COLUMN<\/span>\u00a0listprice<\/span>\u00a0MONEY<\/span>\u00a0 <\/span><\/div>\n

\"Interface<\/p>\n

8) Drop the user created statistic<\/p>\n

DROP<\/span>\u00a0statistic<\/span>\u00a0production<\/span>.<\/span>product<\/span>.<\/span>mystats<\/span>\u00a0 <\/span><\/div>\n

9) Create the statistics again, this time using the auto-drop option.<\/p>\n

CREATE<\/span>\u00a0statistics<\/span>\u00a0[mystats]<\/span>\u00a0ON<\/span>\u00a0production<\/span>.<\/span>product<\/span>(<\/span>listprice<\/span>)<\/span>\u00a0WITH<\/span>\u00a0auto_drop<\/span>=<\/span>ON<\/span>\u00a0 <\/span><\/div>\n

10) Try to change the schema again. This time it will work and the statistics mystats will be dropped<\/p>\n

ALTER<\/span>\u00a0TABLE<\/span>\u00a0production<\/span>.<\/span>product<\/span>
\n\u00a0\u00a0ALTER<\/span>\u00a0COLUMN<\/span>\u00a0listprice<\/span>\u00a0MONEY<\/span>\u00a0 <\/span><\/div>\n

Checking which statistics have the auto-drop option<\/h2>\n

A simple query can help identify which statistics have the auto-drop option and which ones doesn’t:<\/p>\n

SELECT<\/span>\u00a0object_id<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NAME<\/span>,<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0auto_drop<\/span>
\nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>stats<\/span>\u00a0 <\/span><\/div>\n

Conclusion<\/h2>\n

This is a simple new feature, but it can help in some scenarios of version control and automated deployment for database schemas.<\/p>\n

 <\/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……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[147170,5296],"coauthors":[6810],"class_list":["post-95204","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-sql-server-2022","tag-statistics"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95204","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=95204"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95204\/revisions"}],"predecessor-version":[{"id":95223,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95204\/revisions\/95223"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=95204"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=95204"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=95204"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=95204"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}