Manage Learn to apply best practices and optimize your operations.

Why editing complex views in SQL Server Enterprise Manager can be dangerous

Editing complex views in SQL Server Enterprise Manager can be dangerous.

SQL Server Enterprise Manager has a built-in visual editor for prototyping and creating views. For programmers who are trying to get a handle on how to create a complex view that contains a great many JOIN statements (usually the types of statements that most demand a view in the first place), using Enterprise Manager's visual tools to prototype the view is extremely helpful.

The more complex the view, however, the more prone it may be to being re-rendered improperly in Enterprise Manager. When a view is created, the diagram for the view is not kept with it, but is reconstructed dynamically when the view is edited. For instance, if the same tables are referenced more than once in the view's code and are not aliased by the programmer, the view editor will attempt to do the aliasing itself. JOINs between such tables will usually be ruined because of this.

If you edit the Properties for the view, you can see the code outside the context of the visual editor. This way changes can be made directly to it without worrying about the visual editor itself making modifications. This is the best way to get around this problem in the short run. You can also use the Query Analyzer to perform prototyping of JOINs, and then paste the resulting code directly into a CREATE VIEW statement rather than use the visual designer.

There is also an associated problem with SQL Server 7; it has problems with CREATE VIEW statements longer than 3.5K (documented in Knowledge Base article 290536). Views that complex should probably be handled in a different fashion—either through an aggregate of multiple VIEWs, or through a stored procedure, if possible.

Serdar Yegulalp is the editor of the Windows 2000 Power Users Newsletter. Check out his Windows 2000 blog for his latest advice and musings on the world of Windows network administrators – please share your thoughts as well!

Dig Deeper on Windows Server storage management

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.