Warning on editing complex views in SQL Server Enterprise Manager

http://Read this tip to learn what limitations exist while editing 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 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

Dig deeper on Data centers and virtualization for Small Business

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchCompliance

SearchHealthIT

SearchCloudComputing

SearchMobileComputing

SearchDataCenter

Close