MSSQL: tsql,normalization_strategy=case_sensitive doesn't handle case changes properly on case insensitive collations

[martinburch]

When switching on case sensitivity, the database's case-insensitive comparisons allow the DROP VIEW IF EXISTS to succeed in dropping the newly-created view.

The order of the commands needs to be reversed: first, drop the view, then re-create it.
https://learn.microsoft.com/en-us/sql/relational-databases/views/rename-views?view=sql-server-ver16#TsqlProcedure

As a workaround, I can replay the CREATE OR ALTER VIEW commands from the SQLMesh log.

-  dialect tsql,                                                                                                                                                  
+  dialect [tsql,normalization_strategy=case_sensitive],   

SQLMesh correctly identifes

Models:
├── Added:
│   ├── a_o.O
├── Removed:
│   ├── a_o.o

However, the commands are in the wrong order:

2025-02-27 16:42:18,836 - ThreadPoolExecutor-4_1 - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: CREATE OR ALTER VIEW [a_o].[O] AS SELECT * FROM [sql-prod].[a_o].[a_o__O__641323945]; (base.py:2079)

2025-02-27 16:42:19,635 - ThreadPoolExecutor-5_1 - sqlmesh.core.engine_adapter.base - INFO - Executing SQL: DROP VIEW IF EXISTS [a_o].[o]; (base.py:2079)

[lafirm]

The order of the commands needs to be reversed: first, drop the view, then re-create it.

If we reverse the order like @martinburch said, that should solve this #3649 as well. just curious, is there any specific reason why we do create -> drop not drop -> create.

[izeigerman]

@georgesittas can you please have a look

[georgesittas]

@martinburch can you provide a complete example that I can use to reproduce the problem you're seeing? I'm not sure I understand what you're trying to achieve. I get that you changed the dialect setting to activate case-sensitivity for the T-SQL dialect, but is your database or your MSSQL server also case-sensitive?