Source? I’ve been working with SQL Server for a couple of decades and I don’t believe it will automatically create or drop indexes under any circumstances. You might be thinking of Azure SQL DB.
I have used this in Azure SQL too, but according to that it should be in SQL Server.
https://learn.microsoft.com/en-us/sql/relational-databases/a...
> Automatic index management identifies indexes that should be added in your database, and indexes that should be removed. Applies to: Azure SQL Database
Just a few sentences later "Automatic tuning in Azure SQL Database also creates necessary indexes and drops unused indexes" - that's not in on-prem SQL Server.