Why 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

    Requires Free Membership to View

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!

This was first published in November 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.