Someone will have to explain me a very weird situation I cannot simulate, thus cannot understand, but someone else has a problem with an operation against SQL Server where I have not.
A table has 100 fields. About 50 fields have an index. We are talking single index. Thus, the field is created and marked for the index.
So, in SSMS, in the Tables\Indexes treeview, we can see the index.
If we right click on the table and go in the designer, I can remove the field. I click Save and it's done. Even on a table that has 3 to 10 million records, this works instantly.
Recently, I ran a simulation before going in production. I ran the exact same set of procedures on two servers. This approach worked very well. Nothing to report was observed.
However, once we deploy, the DBA notes that this is impossible to do because we cannot delete a field if it has an index as a drop failed message will appear indicating the index is interfering. So, why it this possible that I never have this problem and he already has it?