2

I had always understood that SQL Server auto-created statistics only include one column. In other words, they are not multi-column statistics.

However, today I came across some auto-created statistics that have two columns. The second column is always the primary key of the table. There are other auto-created statistics on the same table that don't have a second column.

enter image description here

auto_created = 1 on the sys.stats table.

Also, some statistics have a different naming convention on the exact same tables. Some of them have a column name in the statistic name. What makes SQL use a different naming convention? For example,

  • _WA_Sys_BegEffDate_7363C5E4
  • _WA_Sys_0000000B_7363C5E4

These are not causing problems, I am just curious about it.

2
  • 2
    What version of SQL Server? And was the database upgraded/migrated from an earlier version or compatibility level? – LowlyDBA - John M Feb 10 at 16:57
  • This particular database is on SQL Server 2014. Yes, the database has been upgraded from previous versions, I know it was on 2008 R2, and could have originated from an even earlier version. – PianoDan Feb 10 at 17:49
1

I have spent a decent amount of time searching for an explanation of the different naming conventions on some of my own databases, and haven't found anything definitive.

That being said, in both our cases we are dealing with very old databases that have undergone many upgrades over the years. My best guess is that at some point Microsoft switched the auto-created statistics to use an incremental alphanumeric pattern instead of using partial column names (somewhere around SQL Server 2000 or 2005?). This may have been done to reduce the potential for naming conflicts or to better handle the renaming of a column.

I haven't seen the Primary Key be added into auto-created statistics, but that might also be an artifact of a very old version.

2
  • This is most likely the reason. Thank you for answering this question. – PianoDan Feb 10 at 22:46
  • Does the behaviour change upgrading the compatibility level? – Francesco Mantovani Feb 11 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.