4

When I create or edit queries using design view, SSMS keeps creating aliases (TableName_1) for some tables for no apparent reason. More annoying is it will also do this if I open existing queries in design view. In other words, it changes my SQL code! Even simple queries have this issue, for example:

SELECT  
  dbo.tblCalendar.id,  
  dbo.tblCalendar.title,  
  dbo.luCalendarType.typeName  
FROM  
  dbo.luCalendarType RIGHT OUTER JOIN  
  dbo.tblCalendar ON dbo.luCalendarType.id = dbo.tblCalendar.type

will be become:

SELECT  
  dbo.tblCalendar.id,  
  dbo.tblCalendar.title,  
  luCalendarType_1.typeName  
FROM  
  dbo.luCalendarType AS luCalendarType_1 RIGHT OUTER JOIN  
  dbo.tblCalendar ON luCalendarType_1.id = dbo.tblCalendar.type

Is there any way to stop SSMS altering my SQL code?

3
  • This behaviour is by design, to avoid name conflicts. If you want to stop this from happening, you will have to modify views in T-SQL, by right-clicking on the view name in the Object Explorer, and scripting the view as ALTER. Jan 19, 2017 at 6:47
  • 1
    Does it do this (change your code) if you open existing queries where you have defined your own aliases? Jan 19, 2017 at 13:06
  • Randolph West: I understand why it would do it to avoid name conflicts but there are no conflicts. As I said, it seems to do it for no apparent reason (other than to annoy me). I have been opening in Script View to remove the aliases and the view works fine. ypercube: It won't change aliases I've created but if I remove the aliases it creates the view will save and execute fine. It will, however, put them back if I re-open the view in Design View.
    – Tony
    Jan 27, 2017 at 6:53

1 Answer 1

5

The best option is to stop using the SSMS View Designer.

By sticking to using a plain old query window to create or edit views, you will have full control over the queries as well as regular practice in Transact-SQL.

Besides, the SSMS View Designer is known to be buggy (see view corrupted when viewed using design feature) or produce peculiar syntax1 (see View designer strange join syntax) or encourage syntax that has become obsolete and is now misleading (see After upgrade of SQL Server 2000 database to SQL Server 2008 the view is not sorted on the group by column).

So, in short, you will just be better off, in the long run, without the View Designer.


1Not necessarily an issue in itself, but syntax that is not very familiar to you does not lend itself well to maintainability of the query.

4
  • Andriy M: So that would be a "No" to my question then. :-) Usually I would only use Design View to create the view. I find it easier to set up the joins in Design View. After that I would swap to Script View to format and develop the view. Currently, this involves removing unnecessary aliases.
    – Tony
    Jan 27, 2017 at 7:03
  • @Tony: That does seem to make sense, especially if you could assign aliases directly in the designer (which would probably eliminate the issue you are asking about). Sadly, it appears you no longer can. Too bad.
    – Andriy M
    Jan 28, 2017 at 9:22
  • Thanks Andriy. I'm using SSMS 2014 and I can assign aliases directly in Design View via the Properties panel no problem. The problem with Design View is that insists on assigning unnecessary aliases which cannot be removed in Design View (if you delete the alias, it puts them back). Design View is happy if I change the alias to something else (provided there's no clash) but why it creates them to begin with make no sense. Especially when the aliases it creates is just the original name with "_1" at the end. Sounds like SSMS is, shall we say, still not as good as it could be. :-)
    – Tony
    Jan 30, 2017 at 7:05
  • I find SSMS to be fine, but then I don't use any designer features in it :) If I were to use Design View the way you described it in the other comment, to just create the bulk of the view with the necessary joins and stuff and then edit it further in a plain query window, I'd probably use it for more or less complex queries where table aliases are needed. Simpler ones could just be typed in their entirety in a query window as I see fit, with or without aliases. Still, I might not know what I'm talking about, as I'm used to writing all my views manually.
    – Andriy M
    Jan 30, 2017 at 8:05

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.