SQL Server Magazine UPDATE, July 22, 2004--Nested Views

To ensure that future email messages you receive from SQL Server Magazine UPDATE aren't mistakenly blocked by antispam software, be sure to add [email protected] to your list of allowed senders and contacts.

This Issue Sponsored By

Get Two Free Sample Issues of SQL Server Magazine!
        http://lists.sqlmag.com/cgi-bin3/DM/y/egof0FgQMn0BRZ0BJ5P0AZ

1. SQL Server Perspectives

2. News and Views

3. Announcements

4. Resources

5. Events Central

6. New and Improved

Sponsor: Get Two Free Sample Issues of SQL Server Magazine!
If you are a SQL Server developer or administrator, SQL Server Magazine is a must-read. Each issue is loaded with information covering Reporting Services, ADO.NET, performance tuning, SQL Server 2005, security and much more. By subscribing today, you'll gain access to a treasury of online article archives, savvy tips, endless code listings, and expert advice that will give you the answers you're looking for. Try two sample issues today, and discover the value and timesaving qualities SQL Server Magazine has to offer. Order now:
     http://lists.sqlmag.com/cgi-bin3/DM/y/egof0FgQMn0BRZ0BJ5P0AZ

1. SQL Server Perspectives

     by Brian Moran, [email protected]

Nested views--views that are called by another view--invariably start off with the best of intentions. Theoretically, there's nothing wrong with using a nested view as long as you're aware of the performance implications. Theoretically, the optimizer eliminates the intermediate abstraction of a nested view, ending up with the same query execution plan it would have devised if nested views hadn't been involved. But in practice, nested views create more problems than they solve.

Let's look at example of how a nested view is born. Joe, a well-meaning developer, creates a perfectly good view that encapsulates a complex business rule--CustomerRule123. Joe's view, ShowCustomerInformation, joins three tables. A few weeks later, Sue sees the view and thinks, "Perfect. That's almost the exact data set I need for a report. I'll use ShowCustomerInformation as my base schema and simply add the other two joins I need." Sue thinks she can recreate the entire join in a new, top-level view. And she assumes that encapsulating the first view is beneficial in that it lets her report automatically pick up any changes to the logic in CustomerRule123. No problems, so far.

Alas, a few months later, someone else creates a new view, BeatsTheHeckOutOfMeData, that's now the first-level view, and Joe's original view, ShowCustomerInformation, is now buried five views down. This new view returns a result set that answers a simple query. However, if you print all the code that each nested view contains, you'll get 8 pages of SQL, and you'll see that the tangle of nested views joins multiple instances of tables together in ways that yield a horrible execution plan. This monster view takes 8 seconds to run.

Careful analysis might show that you could write the query based on BeatsTheHeckOutOfMeData as a clean four-way join that eliminates several messy and expensive rules--and returns in 250ms. But because of developer turnover during the past few months, nobody knows what BeatsTheHeckOutOfMeData is doing. The developers know only that the view returns the data users need. No one has time to detangle the messy web of nested views, and no one wants to change the inner views and risk breaking something somewhere else in the application. So, the monolithic monstrosity called BeatsTheHeckOutOfMeData lives on.

This example might seem extreme, but I often see similar situations at customer sites that regularly use nested views. When I'm called into help solve performance problems at such sites, I dig into my bag of tricks to find the queries that are consuming the most resources. I then run across an innocuous-looking query such as

   SELECT CustomerId, PlusABunchOfOtherColumns   FROM BeatsTheHeckOutOfMeData   WHERE Id  @TargetId   

and I think to myself, "That looks like an easy enough query to tune." Imagine my disappointment when I understand the mess I've gotten myself into. My heart sinks even further when I ask what the nested view does and the developer I'm working with says, "Beats the heck out of me."

Views are an incredibly powerful tool in the database world when they're in the right hands. And nested views aren't inherently evil provided you consider the implications of the scenario I've outlined for you here. I'd rather avoid the problems caused by granting developers the right to directly create nested views. Trained DBAs and architects who can weigh the pros and cons of using a deeply nested view in a controlled manner might use them wisely. But I prefer to make nested views off-limits to the core development team for projects I have architectural responsibility over.

2. News and Views

The voting has closed in SQL Server Magazine's Instant Poll for the question, "Do you use SQL Server Profiler during application development?" Here are the results (+/ 1 percent) from the 160 votes:

The next Instant Poll question is "Do you use nested views?" Go to the SQL Server Magazine Web site and vote for 1) yes, 2) yes, under controlled circumstances, 3) yes, but I plan to be more cautios, or 4) no, they're too problematic.
     http://www.sqlmag.com

3. Announcements

Visit the SQL Server Magazine Web site and experience a helpful resource offering the easy-to-find SQL Server solutions, news, guidance, and how-to information you're looking for. Reference lists of active forums, hot-topic discussions, keyword searches, free Web seminars, FAQs, and much more. The site also features a Web-exclusive column by Itzik Ben-Gan. Check it out:
     http://lists.sqlmag.com/cgi-bin3/DM/y/egof0FgQMn0BRZ0BJ5Q0Aa

SqlJunkies is your online community resource for original tutorial and how-to articles for developing applications with SQL Server 2000 and Yukon; peer-to-peer help and networking through discussion forums and newsgroups; technology tips and pointers from expert bloggers; and the latest in SQL Server-related events and news.
     http://lists.sqlmag.com/cgi-bin3/DM/y/egof0FgQMn0BRZ0BFll0As

4. Resources

Profiler, SQL Server's built-in tracing feature, is an invaluable tool for performance tuning and debugging SQL Server applications. With it, you can trace every request a database application makes to SQL Server and use that data to improve performance. Profiler traces can give you great information to help you improve performance, but you probably don't have the time to run them often. In his August article, "9 Steps to an Automated Trace," Steven Berringer offers you the answer, showing you how to use SQL Server jobs to automate your traces. Read this article today at
    http://www.sqlmag.com/articles/index.cfm?articleid43014

ISI has inherited an application that uses the sa account to access a SQL Server 2000 database. ODBC is also set up using the sa account. What steps does ISI need to follow to get the application and ODBC to use a Windows 2000 domain user account? Offer your advice and see what other people have said on SQL Server Magazine's Security forum at    http://www.winnetmag.com/sqlserver/forums/messageview.cfm?catid1667&threadid123721

     by Brian Moran, [email protected]

Q. I often send query results to users through email or copy the results to Microsoft Word documents. Query Analyzer in text mode provides headings for the query results, but the headings don't copy and display well in Microsoft Excel or email. I can use Query Analyzer's output-file option, but that requires more mouse clicks than I want. Is there an easy way to copy a query result's column headings and values from Query Analyzer to Excel?

A. I regularly copy query results into Word documents by selecting Tab Delimited from the Query Analyzer Tools, Options, Results, Results Output Format menu. This option copies results into Word or Excel nicely. Excel formats the result set into a spreadsheet grid with no extra work. But if you copy into Word, you'll probably also want to use the "convert text to table" option, which formats the data into a table that's easy to read and to send in email.

5. Events Central

For a complete guide to Web and live events, see
    http://www.winnetmag.com/events

Discover solutions that minimize the likelihood of downtime in your SQL Server implementation and help to ensure continuous SQL Server application availability. SQL Server Magazine invites you to attend a free, interactive Web seminar designed specifically for SQL Server professionals. This live, online event will be presented August 19. Register today--it's free!
     http://lists.sqlmag.com/cgi-bin3/DM/y/egof0FgQMn0BRZ0BJ5R0Ab

6. New and Improved

     by Dawn Cyr, [email protected]

Advance Info Systems announced Query by Example for Excel (QBEX), software that lets business users query and manipulate data in SQL Server 2000 Analysis Services cubes through a Microsoft Excel interface. Users who already understand the database cube they're querying can select an appropriate Excel report layout; users who aren't familiar with cube structure can use the Item Selector to select and place dimensions and members in the spreadsheet. Alternatively, users can use the Layout Manager, a wizard-like designer, to create basic or complex reports. The product supports analytical activities such as expanding views to reveal more detailed report data, keeping or eliminating members or sections, pivoting dimensions across or down, and reordering dimension groupings. The product lets you write data to a temporary cube so that you can perform "what if" analyses, and it lets you write back to the database cube for planning or budgeting applications. Advanced users can employ Visual Basic for Applications (VBA) to further automate data updates and create custom reporting packages. QBEX costs $449 per license for up to four licenses. Five licenses cost $1995, and purchases of five or more licenses include support and all version upgrades for the first year. For further information or other pricing options, contact Advance Info Systems at 416-226-7941, [email protected], or [email protected].
     http://lists.sqlmag.com/cgi-bin3/DM/y/egof0FgQMn0BRZ0BJ5S0Ac

Fatesoft announced Sourcecode to Flowchart 2.5, a flowchart generator that lets developers reverse-engineer program code into flowcharts. The resulting flow diagrams help developers document, visualize, and understand source code in multiple languages, including C, C++, Visual C++ .NET, Visual Basic, VBScript, ASP.NET, and T-SQL. Developers can export the flow diagrams as .bmp images or to Microsoft Visio, Microsoft Word, Microsoft Excel, or Microsoft PowerPoint. Enhancements to the latest release include a new Visio stencil and options that let you output source code into flow chart shapes. Sourcecode to Flowchart 2.5 costs $89.99 for a single-user license, and a free evaluation copy is available for download. For more information, contact Fatesoft at [email protected], or for phone orders only, call 866-500-6750 or 44-1297-552862.
    http://lists.sqlmag.com/cgi-bin3/DM/y/egof0FgQMn0BRZ0BJ5T0Ad

Silico-Magnetic Intelligence (SMI) announced its Crash Course in Microsoft SQL Server 2000, a training course on three CD-ROMs that helps IT professionals learn how to use and program SQL Server 2000. The course prepares beginning and intermediate students for successful database access and application development. The CD-ROMs, which run on Macromedia Flash, contain full-motion screen image video with audio narration. Each chapter contains 10-minute units that students can replay multiple times to master the skills they need to work with such essential SQL Server components as Enterprise Manager, Query Analyzer, and Data Transformation Services (DTS). The complete training package costs $245. For more information or a test-drive, contact SMI through SQL USA at [email protected].
      http://lists.sqlmag.com/cgi-bin3/DM/y/egof0FgQMn0BRZ0BJ5U0Ae

SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
     http://www.sqlmag.com/sub.cfm?codessei211x1y

Contact Us:

Manage Your Account

You are subscribed as #EmailAddr#.

To unsubscribe from this email newsletter, send an email message to mailto:#Mailing:UnsubEmail#.

To make other changes to your email account such as changing your email address, updating your profile, and subscribing or unsubscribing to any of our email newsletters, simply log on to our Email Preference Center.
http://www.winnetmag.com/email

Copyright 2004, Penton Media, Inc.