Stop SQL Server Management studio from breaking my view

Question

I have a view that is working completely fine, and looks roughly as follows:

 WITH xxx AS ( ... ),
      yyy AS ( SELECT ... FROM xxx )

 SELECT ... FROM yyy

Pretty straightforward, and it has been working great. However, a situation has arisen and I need to make a very small change to xxx . I go into the view editor, make the small change to xxx, but when I save/execute, suddenly Server Management Studio's auto format takes over and decides to change my view to this:

 WITH yyy AS ( SELECT ... FROM xxx),
      xxx AS ( ... )

 SELECT ... FROM yyy

Basically it switches the first and second parts of my WITH statement, for no apparent reason! I've tried scripting the view to a new query, making the change there and recreating it, and it does the same thing.

Is there a way to turn off the auto formatting!? Any other suggestions!?

Answer 1

The only way to win is to stop using the view designer entirely.

You have to make a choice

• either you do care about the layout of the code within the view - in which case you will write it yourself in the query editor window, or,
• you treat the view as entirely generated by the view designer, in which case you need to ignore what it ends up looking like.