Why Does Microsoft SQL Server Management Studio Keep Creating Table Aliases?

Question

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?

Answer 1

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 syntax¹ (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.

---

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