Tip

Warning on editing complex views in SQL Server Enterprise Manager

This tip originally appeared on SearchWin2000.com, a sister site of SearchSmallBizIT.com.

SQL Server Enterprise Manager has a built-in visual editor for prototyping and creating views. For programmers who are trying to get a handle

    Requires Free Membership to View

    Login

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!

Do you have comments on this tip? Let us know.


This was first published in December 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    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.

    Back to top