Thursday, February 17, 2011

Why does SQL Server's GUI mangle my views?

When I create a view in SQL Server 2005 and someone later opens it in the GUI modify mode it sometimes completely rearranges my joins. Often to the point it's virtually no longer readable. If I look at it in the GUI it's changed as well from what I originally wrote. Sometimes the join lines no longer even point to a field and the join symbol has "fx". I assume it has "optimized" it for me. Is there a way to prevent this from happening? And what does "fx" mean?

Originally it was something like this:

FROM dbo.Stop LEFT OUTER JOIN dbo.StopType ON dbo.Stop.StopTypeID = dbo.StopType.StopTypeID LEFT OUTER JOIN dbo.CityState ON dbo.Stop.City = dbo.CityState.City AND dbo.Stop.State = dbo.CityState.State LEFT OUTER JOIN dbo.vwDrivers ON dbo.Stop.DriverID = dbo.vwDrivers.DriverID LEFT OUTER JOIN dbo.truck ON dbo.Truck.TruckID = dbo.Stop.TruckID INNER JOIN dbo.vwTicketIDFirstStopLastStop ON dbo.vwTicketIDFirstStopLastStop.TicketID = dbo.stop.ticketid LEFT OUTER JOIN dbo.Company ON dbo.Company.CompanyID = dbo.vwTicketIDFirstStopLastStop.BillToCompanyID

Now it's this.

FROM dbo.Truck RIGHT OUTER JOIN dbo.Stop INNER JOIN dbo.StopType ON dbo.Stop.StopTypeID = dbo.StopType.StopTypeID LEFT OUTER JOIN dbo.CityState ON dbo.Stop.City = dbo.CityState.City AND dbo.Stop.State = dbo.CityState.State LEFT OUTER JOIN dbo.vwDrivers ON dbo.Stop.DriverID = dbo.vwDrivers.DriverID ON dbo.Truck.TruckID = dbo.Stop.TruckID LEFT OUTER JOIN dbo.vwTicketIDFirstStopLastStop LEFT OUTER JOIN dbo.Company ON dbo.Company.CompanyID = dbo.vwTicketIDFirstStopLastStop.BillToCompanyID ON dbo.vwTicketIDFirstStopLastStop.TicketID = dbostop.ticketid

From stackoverflow
  • No you cannot. That's why you should never, ever use it.

    "Fx" means that the join is not a simple column-to-column link, but involves a function (that's also why it can't point to a field, of course). It should NOT do that by itself, though,

  • It's the result of the view GUI parsing the SQL into it's own internal DOM style format and then writing it back out - much in the same way that HTML editors take in XHTML and emit stuff differently to how you wanted it :(

  • I hate the view GUI

    I use RightClick->ScriptViewAs->Alter To->New Query Editor Window

    So much nicer :)

0 comments:

Post a Comment