All, I had a very strange thing happen last Friday. We have a table that is updated on a daily basis using a job that fires a proc that utilizes a view. The columns were being transposed from the view to the table being updated i.e. column A from the view was updating column D in the table instead of column A as it should have.

When I right clicked the view and selected "Design" from the menu I could see the problem was indeed that the sql statement generated was being written to update column D in the table with information from column A in the view.

I suspected a change to the view definition so I right clicked the view and scripted it out to see when it was last updated and when I did this the script generated was correct and completely different from the sql generated when I chose the "design" option. At this point I dropped the view and recreated it from the script. Now all is well.

Has anybody ever seen this? I've been working with SQL server since 2000 and have never come across this. We are using SQL2005 SP4 on Win2K3 (upgrading to 12 soon). Any insight would be appreciated. -Paul


Yes, the view designer is a horribly crippled, buggy piece of garbage you shouldn't be using to "design" your views. It should be called the view destroyer, because it can do exactly what you've observed: re-write any non-standard T-SQL into what it thinks you meant. And by "any non-standard T-SQL" I mean anything not predicted by the two developers who likely spent about a day testing this "feature."

Always, always, always modify your views using Script View As > Alter to > New Query Editor Window.

See these related Connect items:


Where I mention in the workarounds:

Obviously a better approach is to right-click the view, script as alter to new window. I wonder when they'll admit defeat and ditch the visual designers altogether. They cause more problems than they solve, IMHO.


Where I do something similar, and


Where I ask:

Didn't Edit bring up the horrible graphic designer for views, which was riddled with bugs and lacked support for several basic T-SQL constructs? Isn't that what Design does now?

  • Aaron and Jon, Thanks for the input, I usually do create all objects by writing the script myself, however this time the "view" corrupted itself. It was returning these errant values before I examined the structure of it using the designer. I should have included the fact the view was dropped and replaced via script in a .NET deployment of an application upgrade. The developers dropped and recreated the view via script but it somehow got corrupted. I really appreciate the help and now know I'm not going crazy. I'm glad other people were there because I would not have believed it! THX AGN
    – Paul_J
    Feb 23 '14 at 23:14
  • can't believe in SSMS2016 the problem still persists
    – shanyangqu
    Dec 11 '16 at 22:13
  • @shanyangqu It's existed since 2005, so I wouldn't hold my breath for it to ever be fixed. This view designer is horrible baggage that I'm surprised still exists, and I recommend all of my customers to stay away from it completely - in most cases it is a crutch and not actually necessary anyway. Dec 12 '16 at 2:49

One thing that View Designer handles very badly is mixing of AND and OR in the WHERE clause, even when you have correctly bracketed everything to clearly show what you intended.

If you need a workaround for that situation to allow others to edit your view using View Destroyer, then

  • Make an extra copy of the entire SELECT statement with the ANDs and ORs for each permutation of the ORs
  • Edit each one to cover a single set of terms, i.e. the WHERE has only ANDs in it
  • Join each of these individual SELECTs together with UNIONs

Congratulations, your View is now miles/kilometers longer than it needs to be, but View Designer won't defecate all over it.

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.